By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,608 Members | 3,801 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,608 IT Pros & Developers. It's quick & easy.

VBA array basics - dynamic or static?

P: n/a
I need to create a basic one-dimensional array of strings, but I don't know
how many strings I'm going to have until the code is finished looping.

pseudo code:

Dim astrMyArray()
Do While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
astrMyArray(i) = rst!Something
End if
Loop
ReDim astrMyArray(1 to i)

The rst is just for example, the actual loop I am using is different, but
the idea is the some.

How do I create this basic Array?

I've been looking for some good tutorials on VBA Arrays, but haven't found
any...

Thanks in advance.
Nov 13 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Dim astrMyArray()

While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
ReDim astrMyArray(1 to i)
astrMyArray(i) = rst!Something
End if
Wend

On Wed, 02 Mar 2005 17:13:11 GMT, "deko" <de**@hotmail.com> wrote:
I need to create a basic one-dimensional array of strings, but I don't know
how many strings I'm going to have until the code is finished looping.

pseudo code:

Dim astrMyArray()
Do While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
astrMyArray(i) = rst!Something
End if
Loop
ReDim astrMyArray(1 to i)

The rst is just for example, the actual loop I am using is different, but
the idea is the some.

How do I create this basic Array?

I've been looking for some good tutorials on VBA Arrays, but haven't found
any...

Thanks in advance.


Nov 13 '05 #2

P: n/a
> Dim astrMyArray()

While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
ReDim astrMyArray(1 to i)
astrMyArray(i) = rst!Something
End if
Wend


Thanks for the reply.

so I just redim it every iteration? fine with me, but then it seems the
array is not really dynamic - is this just the way vb does arrays?

Nov 13 '05 #3

P: n/a
Okay... but how do I return the array values?

Why does Debug.Print only return "string9" ?

Public Function test()
Dim i As Integer
Dim j As Integer
Dim s As String
Dim astr() As String
Do While i < 10
If i > 5 Then
ReDim astr(1 To i)
s = "string" & i
astr(i) = s
End If
i = i + 1
Loop
For j = LBound(astr) To UBound(astr)
Debug.Print astr(j) <<===== * * *
Next
End Function
Nov 13 '05 #4

P: n/a
Yuo forgot to use the Preserve keyword in the ReDim statement. Without
Presserve, the array gets erased every time it is ReDim'd.

On Wed, 02 Mar 2005 12:33:50 -0800, Tom Warren <wi**********@bellsouth.net>
wrote:
Dim astrMyArray()

While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
ReDim astrMyArray(1 to i)
astrMyArray(i) = rst!Something
End if
Wend

On Wed, 02 Mar 2005 17:13:11 GMT, "deko" <de**@hotmail.com> wrote:
I need to create a basic one-dimensional array of strings, but I don't know
how many strings I'm going to have until the code is finished looping.

pseudo code:

Dim astrMyArray()
Do While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
astrMyArray(i) = rst!Something
End if
Loop
ReDim astrMyArray(1 to i)

The rst is just for example, the actual loop I am using is different, but
the idea is the some.

How do I create this basic Array?

I've been looking for some good tutorials on VBA Arrays, but haven't found
any...

Thanks in advance.


Nov 13 '05 #5

P: n/a
> Yuo forgot to use the Preserve keyword in the ReDim statement. Without
Presserve, the array gets erased every time it is ReDim'd.


I see. Now this works properly:

Public Function test()
Dim i As Integer
Dim j As Integer
Dim s As String
Dim astr() As String
Do While i < 10
If i > 5 Then
ReDim Preserve astr(1 To i)
s = "string" & i
astr(i) = s
End If
i = i + 1
Loop
For j = LBound(astr) To UBound(astr)
Debug.Print astr(j)
Next
End Function

Thanks!
Nov 13 '05 #6

P: n/a
Yes - good thing I at least spelled "Preserved" correctly once :)

On Wed, 02 Mar 2005 18:08:53 GMT, "deko" <de**@hotmail.com> wrote:
Yuo forgot to use the Preserve keyword in the ReDim statement. Without
Presserve, the array gets erased every time it is ReDim'd.


I see. Now this works properly:

Public Function test()
Dim i As Integer
Dim j As Integer
Dim s As String
Dim astr() As String
Do While i < 10
If i > 5 Then
ReDim Preserve astr(1 To i)
s = "string" & i
astr(i) = s
End If
i = i + 1
Loop
For j = LBound(astr) To UBound(astr)
Debug.Print astr(j)
Next
End Function

Thanks!


Nov 13 '05 #7

P: n/a
deko wrote:
Dim astrMyArray()

While Not rst.EOF
i = i + 1
If rst!Something = [some condition] Then
ReDim astrMyArray(1 to i)
astrMyArray(i) = rst!Something
End if
Wend


Thanks for the reply.

so I just redim it every iteration? fine with me, but then it seems the
array is not really dynamic - is this just the way vb does arrays?


Befor you enter your While loop do a Dcount on rst testing your "some
condition".

Ron

--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com

Nov 13 '05 #8

P: n/a
Deko,

Here's an array library I wrote back day.

Option Compare Database
Option Explicit

Public Const VB_Binary = 0
Public Const VB_Text = 1

Public Function ArrayJoin(InArray() As String, Optional ByVal Sep As
String = " ") As String
' Build a string of words from "InArray" using "Sep" to separate each
word.
Dim i As Integer ' array index

For i = LBound(InArray) To UBound(InArray) ' loop through array
WordB and build string
If i = UBound(InArray) Then ' if last word in input
array
ArrayJoin = ArrayJoin & InArray(i) ' add word to output
string withOUT separator
Else ' if NOT last word in
input array
ArrayJoin = ArrayJoin & InArray(i) & Sep ' add word to output
string with separator
End If ' if last word in input
array
Next
End Function

Public Sub ArraySplit(Text As String, InArray() As String, Optional
ByVal Sep As String = " ", Optional ByVal Compare As Long = VB_Binary)
' Fill the "InArray" array with words from "Text", using "Sep" as a
separator for each word.
Dim WordB As Integer ' current word's
starting string position
Dim WordE As Integer ' current word's ending
string position
Dim i As Integer ' word array's current
element index

If Len(Text) = 0 Then Exit Sub ' if no text to split
i = 0 ' initialize word
array's current element index
WordB = 1 ' initialize current
word's starting string position
ReDim InArray(0) ' initialize word array

WordE = InStr(1, Text, Sep, Compare) ' search for first
separator
While WordE > 0 ' loop each word in text
string
InArray(i) = Mid$(Text, WordB, WordE - WordB) ' load word into array
i = i + 1 ' bump word array's
current element index
ReDim Preserve InArray(i) ' expand word array
WordB = WordE + Len(Sep) ' set start of next word
WordE = InStr(WordB, Text, Sep, Compare) ' search for next
separator
Wend ' next word

If WordB <= Len(Text) Then ' if text does NOT end
with separator
InArray(i) = Mid$(Text, WordB) ' load last word into
array
Else ' if text ends with
separator
ReDim Preserve InArray(i - 1) ' compress word array
End If ' if text does NOT end
with separator

End Sub

Public Function ArraySearch(InArray() As String, Word As String) As
Integer
' Search SORTED "InArray" for "Word", return array number (of first
occurrence) if found or
' -1 if not.
Dim Low As Integer ' low search boundary
Dim Bin As Integer ' binary split search
boundary
Dim High As Integer ' high boundary

ArraySearch = -1 ' return word NOT found
Low = LBound(InArray) ' set low search
boundary
If Word < InArray(Low) Then Exit Function ' if word too low to be
in array, return 0
High = UBound(InArray) ' set high search
boundary
If Word > InArray(High) Then Exit Function ' if word too high to be
in array, return 0
If Word = InArray(Low) Then ' if low match
ArraySearch = Low ' return array number of
FIRST word found
Exit Function ' return to caller
End If ' if low match
If Word = InArray(High) Then ' if high match
ArraySearch = High ' return array number of
FIRST word found
Exit Function ' return to caller
End If ' if high match

Do Until High - Low = 1 ' loop through array
Bin = ((High - Low) \ 2) + Low ' binary split array
If Word = InArray(Bin) Then ' if match
ArraySearch = Bin ' return array number of
FIRST word found
Exit Function ' return to caller
End If ' if match
If Word > InArray(Bin) Then ' if word higher then
binary split
Low = Bin ' reset low boundary for
next binary split
Else ' if word lowwer then
binary split
High = Bin ' reset high boundary
for next binary split
End If ' if word higher then
binary split
Loop ' next search
End Function

Public Sub ArraySort(InArray() As String)
Dim i As Integer ' array index
Dim First As Integer ' first array number
Dim Last As Integer ' last array number
Dim Split As Integer ' array binary-split
pointer
Dim Swap As String ' array element temporay
swap

First = LBound(InArray) ' get first array number
Last = UBound(InArray) ' get last array number
Split = (Last - First + 1) \ 2 ' calculate binary split
array pointer

Do While Split > 0 ' sort loop
For i = First To Last - Split ' sort low half loop
If InArray(i) > InArray(Split + i) Then ' if value in low half
greater then value in high half
Swap = InArray(Split + i) ' save high half
(lesser) value
InArray(Split + i) = InArray(i) ' move low half
(greater) value to high half
InArray(i) = Swap ' move saved (lesser)
high half value to low half
End If ' if value in low half
greater then value in high half
Next i ' next lower half sort

For i = Last - Split To First Step -1 ' sort high half loop
If InArray(i) > InArray(Split + i) Then ' if value in low half
greater then value in high half
Swap = InArray(Split + i) ' save high half
(lesser) value
InArray(Split + i) = InArray(i) ' move low half
(greater) value to high half
InArray(i) = Swap ' move saved (lesser)
high half value to low half
End If ' if value in low half
greater then value in high half
Next i ' next lower half sort
Split = Split \ 2 ' re-calculate binary
split array pointer for next loop
Loop

End Sub

Public Function ArrayScan(InArray() As String, Word As String) As
Integer
' Scan UNSORTED "InArray" for "Word", return array number (of first
occurrence) if found or
' -1 if not.
Dim i As Integer ' array index
For i = LBound(InArray) To LBound(InArray) ' scan through array
If InArray(i) = Word Then ' if word found
ArrayScan = i ' return array number of
FIRST word found
Exit Function ' return to caller
End If ' if value in low half
greater then value in high half
Next i ' next lower half sort
ArrayScan = -1 ' return word NOT found
End Function

On Wed, 02 Mar 2005 18:08:53 GMT, "deko" <de**@hotmail.com> wrote:
Yuo forgot to use the Preserve keyword in the ReDim statement. Without
Presserve, the array gets erased every time it is ReDim'd.


I see. Now this works properly:

Public Function test()
Dim i As Integer
Dim j As Integer
Dim s As String
Dim astr() As String
Do While i < 10
If i > 5 Then
ReDim Preserve astr(1 To i)
s = "string" & i
astr(i) = s
End If
i = i + 1
Loop
For j = LBound(astr) To UBound(astr)
Debug.Print astr(j)
Next
End Function

Thanks!


Nov 13 '05 #9

P: n/a
deko wrote:
The rst is just for example, the actual loop I am using is different, but
the idea is the some.

How do I create this basic Array?


VBA Arrays are very weak. If you are populating the array from a
recordset, GetString would allow you to load and dimension the array in
one line.

This is an example from JScript, but it could easily be modified for
VBA. The variables have been declared with "modular" wide scope. The
connection and recordset are ADO objects.

function getAssignments(){
sql='SELECT a.Periods, a.ClassID, a.SubjectID, a.TeacherID'
sql+='\nFROM Assignments a';
sql+='\nJOIN Teachers t ON a.TeacherID=t.TeacherID';
sql+='\nJOIN SchoolTeachers st ON t.TeacherID=st.TeacherID';
sql+='\nWHERE st.SchoolID='+vSchoolID;

recordSet=connection.Execute(sql);

a=recordSet.GetString(adClipString,recordSet.Recor dCount,'\t','\n',0).split(/\n/);
}

a is now an array of the records of the recordset, each element being a
string of the values of the record's fields.

We can translate each of these elements to an array with

function splitElements(){
for(z=0;z<a.length;z++){
a[z]=a[z].split(/\t/);
}
}

this too could easily be rewritten in VBA.

we now have an array of arrays, identical in values to those of
recordset. Manipulating the array is many times faster than manipulating
the recordset, and there are no rules to check ... if Updateable ... etc.

If you're not populating the array from a recordset you might want to
use a collection. Collections are slow. Are they slower than
redimensioning an array? I don't know.

Have you considered dimensioning the array to some adequately large
number and after you have populated those elements you want,
redimensioning it to the appropriate smaller size?
Nov 13 '05 #10

P: n/a
Thanks for the detailed reply.
If you're not populating the array from a recordset you might want to
use a collection. Collections are slow. Are they slower than
redimensioning an array? I don't know.
I thought about a collection, but I think an Array is more appropriate here.
Have you considered dimensioning the array to some adequately large
number and after you have populated those elements you want,
redimensioning it to the appropriate smaller size?


I have about 100 items to loop through where some will pass and other fail.
I want to put each in it's own array and do stuff with the strings later in
the same function. So it looks something like this:

Dim sn() As String

For Each ...
k = k + 1
ReDim Preserve sn(1 To k)
sn(k) = strValue

[another array]

Next

Later, when I need the values, I do this:

For p = LBound(sn) To UBound(sn)
[code]
Next p

Would it be more efficient to get a count of all items to test and dim both
arrays that number (e.g. 100) and then redim both arrays to their respective
count when done? I may only have a few elements in the failure array.
Nov 13 '05 #11

P: n/a
> Here's an array library I wrote back day.

Thanks, I'll take a look.
Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.