Solved

Use of Arrays for strings

Posted on 2003-12-01
9
210 Views
Last Modified: 2010-05-01
I had never used Arrays and wanted to know how to use Arrays for strings.
I am trying to read file by each line and then split using array.
The strings are like
n nn 'aaa' nnn
or
n nn 'aaa aaa aaa aaa' 'aaaa aaa aa' nnn 'aaa aaa aaa'
Where 'n' is numbers and 'a' is alphabets

Presently I am using Replace function to get

n,nn,'aaa',nnn
or
n,nn,'aaa,aaa,aaa,aaa','aaaa,aaa,aa',nnn,'aaa,aaa,aaa'

But I want the string to be

n,nn,'aaa',nnn......(NO CHANGE can use Replace function)
and
n,nn,'aaa aaa aaa aaa','aaaa aaa aa',nnn,'aaa aaa aaa'

Regards
Nitin
0
Comment
Question by:Nitindhamane
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 9849234
just do this...

dim varArray as variant
dim strData as string
dim intCount as integer

strData = "aaa NNN aaa NNN aaa NNN"
varArray = split(strData," ") '<---split each item by the space
for iCount = lbound(varArray) to ubound(varArray)
  msgbox varArray(iCount)
next iCount


hope that helps
-brian
0
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 9849241

This will do EXACTLY what you want.



dim varArray as variant
dim strData as string
dim intCount as integer
dim strFinal as string

strData = "aaa NNN aaa NNN aaa NNN"
varArray = split(strData," ") '<---split each item by the space
for iCount = lbound(varArray) to ubound(varArray)
 strFinal = strFinal & "," & varArray(iCount)
next iCount
strFinal = right(strFinal,len(strFinal) - 1)
msgbox strFinal



-brian

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 9849280
You could use  Replace several times

Dim s As String, t As String

s = "n nn 'aaa aaa aaa aaa' 'aaaa aaa aa' nnn 'aaa aaa aaa'"
t = Replace(s, " '", "|'")
t = Replace(t, "' ", "'|")
t = Replace(t, "||", ",")
t = Replace(t, "|", ",")
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 9851172
BrianGeff719,

    Your code will break the strings apart.

         'aaa aaa aaa aaa'

    should stay together as string.


GrahamSkan,

    Your code was close but produced:

        n nn,'aaa aaa aaa aaa','aaaa aaa aa',nnn,'aaa aaa aaa'

    when it should be:

        n,nn,'aaa aaa aaa aaa','aaaa aaa aa',nnn,'aaa aaa aaa'

Here is one way to do it, stripping off the single quotes at the same time:

    Dim s As String, t As String
    Dim curChar As String
    Dim a As Integer, str As Boolean
    Dim values As Variant
   
    str = False
    s = "n nn 'aaa aaa aaa aaa' 'aaaa aaa aa' nnn 'aaa aaa aaa'"
    For a = 1 To Len(s)
        curChar = Mid(s, a, 1)
        If curChar = "'" Then
            str = Not str
            curChar = "" ' take this line out to leave single quotes on your strings
        ElseIf curChar = " " Then
            If Not str Then
                curChar = ","
            End If
        End If
        t = t & curChar
    Next a
   
    Debug.Print s
    Debug.Print t
   
    values = Split(t, ",")
    For a = 0 To UBound(values)
        Debug.Print values(a)
    Next a

Regards,

Idle_Mind
0
 

Author Comment

by:Nitindhamane
ID: 9856875
Idle_mind,
Your code gives the desired result but is it possible to get the same result using array command.

Regards
Nitin
0
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 9857062
Just use my code and add this to the for loop

if isNumeric(varArray(iCount)) = true then
 msgbox "array item " & icount & " is numeric"
end if
0
 
LVL 19

Expert Comment

by:BrianGEFF719
ID: 9857070
Idle_Minds code is using arrays.....do you see he uses the Split() command, split takes a string, and it will create an array based on a Delimiter.

VARIANT = ARRAY
So here is an example
RUN THIS CODE

dim values as variant
dim t as string
dim i as integer
t = "hello,my,name,is,brian"
values = Split(t, ",")
for i = lbound(values) to ubound(values)
msgbox values(i)
next i


Values is now an array created from the string (t) and each item is added to the array by commas.


I hope that helps you understand arrays.
-Brian
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 9858436
The desired strings end up in an array called "values" in my code.

Idle_Mind
0
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 50 total points
ID: 9858469
Here is the snippet of code that actually produces the array:

values = Split(t, ",") ' <----- Array generated here
For a = 0 To UBound(values)
    Debug.Print values(a)
Next a

The For...Next loop is there just to demonstrate that the array was actually generated and to show its contents.

Idle_Mind
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month3 days, 23 hours left to enroll

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question