473,574 Members | 2,422 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA array basics - dynamic or static?

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
11 39439
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.c om> 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
> 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
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
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**********@b ellsouth.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.c om> 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
> 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
Yes - good thing I at least spelled "Preserved" correctly once :)

On Wed, 02 Mar 2005 18:08:53 GMT, "deko" <de**@hotmail.c om> 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
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
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(InArr ay() 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(InA rray() 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(InArr ay() 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(InArr ay() 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.c om> 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
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.T eacherID';
sql+='\nJOIN SchoolTeachers st ON t.TeacherID=st. TeacherID';
sql+='\nWHERE st.SchoolID='+v SchoolID;

recordSet=conne ction.Execute(s ql);

a=recordSet.Get String(adClipSt ring,recordSet. RecordCount,'\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.len gth;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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
51662
by: deko | last post by:
What I'd like to do is create an array of values and test for existence of those values. Here's the non-working code I'm having trouble with: Dim wcSearch(4 To 7) As Integer If Me.Value = wcSearch Then Do Something Else Do Something else
2
7105
by: deko | last post by:
Can I return the index number of an array if all I have is the element? For example, if I want to index the alphabet, I can put the letters in the array: Dim varLtr As Variant varLtr = Array("A", "B", "C", "D", "E", "F", "G", _ "H", "I", "J", "K", "L", "M", "N", _ "O", "P", "Q", "R", "S", "T", "U", _ "V", "W", "X", "Y", "Z")
7
2991
by: kaul | last post by:
i want to create a 2-d array containg r rows and c columns by dynamic memory allocation in a single statement so that i will be able to access the ith and jth index as say arr how is that possible?
2
5418
by: raxitsheth | last post by:
I am using array in my progrm... is there any tool /tips so that i can convert my program to Dynamic Array...so that I can Add more Element to Array.... Code is around 4000 line 'C' (not C++) program....
11
7684
by: C C++ C++ | last post by:
Hi all, got this interview question please respond. How can you quickly find the number of elements stored in a a) static array b) dynamic array ? Rgrds MA
7
3108
by: alternative451 | last post by:
Hi, I have just one question : how to know the size of an array, i have un little program, i use first static, ant i can use sizeof() to know the size, but when i put it as paremeter in the function, size of return "1". ex int tab; printf("%d",sizeof(tab)/sizeof(int)); // print 10 int length(int *tab)
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6481
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5635
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3756
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3770
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2254
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.