Solved

# Use of Arrays for strings

Posted on 2003-12-01
198 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
Question by:Nitindhamane
9 Comments

LVL 19

Expert Comment

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

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

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

LVL 85

Expert Comment

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

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

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

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 85

Expert Comment

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

Idle_Mind
0

LVL 85

Accepted Solution

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

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!