Stringlists or String arrays

[deyken]

Hi There,

Another conundrum:

In Delphi I usually (virtually) create a TStringList object (at RunTime) to accept several sets of Strings, which are automatically indexed. I can then access and process these strings through standard for..loops. I can also pass this object to functions and procedures.

Can this be done in VBA?

While on this type of object: Is it possible to create a string array (dynamic or static) that could be passed as an object to VBA functions/procedures?

Looking forward to your input!

[MWE]

Hi There,

Another conundrum:

In Delphi I usually (virtually) create a TStringList object (at RunTime) to accept several sets of Strings, which are automatically indexed. I can then access and process these strings through standard for..loops. I can also pass this object to functions and procedures.

Can this be done in VBA?

While on this type of object: Is it possible to create a string array (dynamic or static) that could be passed as an object to VBA functions/procedures?

Looking forward to your input!

VBA has very powerful string processing capabilities and I am sure what you wish to do in your first para can be done. Can you provide more details and identify what application is relevant (Word? Excel? Outlook? PowerPoint? ...)

You can pass just about anything to/from VBA procedures including all data types, all default object types, user-defined object types, etc. You can also force objects passed to be of a particular type to preclude errors due to inconsistent object types.

Finally, why is this inquiry in the MSProject forum? It appears that the thread is about VBA generally (vs a specific application like Excel or Word), but you will likey have more success getting questions answered if you ask them in higher volume forums like Word or Excel.

[deyken]

Hi MWE,

Thank you very much for the update! I will be uding this functionality in Excel. Can you give me an indication of the correct syntax?

Looking forward to your reply!

[MWE]

Hi MWE,

Thank you very much for the update! I will be uding this functionality in Excel. Can you give me an indication of the correct syntax?

Looking forward to your reply!

It would be hard to provide exact syntax until you tell me more about what you want to do. A few questions and suggested syntax:

1) do you know how many strings you will eventually have or will the loop code determine some sort of "end". If you know how many strings will be necessary, then we can dimension the string variable directly, for example
[vba]Dim StringArray(10) as string[/vba] If you are not sure, we tell VBA that the variable is a string array and can incrementally increase its size in the loop, for example
[vba]Dim N as Long
Dim StringArray() as string

For N = 1 to Something

ReDim Preserve StringArray( 1 to N)
StringArray(N) = ...

Next N[/vba] The above statement redimensions the array to size N (assumes the index runs from 1 top N) The "Preserve" qualifier preserves all current values in the array. With "Preserve" the array is redimensioned, but all previous values are lost.

2) how will the text be read into the strings? manually entered via user?
[vba]StringArray(N) = InputBox(...)[/vba] read in from worksheet cells?
[vba]StringArray(N) = Cells(...)[/vba] read in from an external file? something else?

3) regarding your 2nd question about passing string objects. Regardless of how complicated the string object might be, it can be passed to a procedure by simply including it in the arguement list:
[vba]Dim N as Long
Dim StringArray() as string

For N = 1 to Something

ReDim Preserve StringArray( 1 to N)
StringArray(N) = ...

Next N

Call SomeProcedure(StringArray, ... )[/vba]

The second procedure can determine array size without that information being passed:
[vba]Sub SomeProcedure(StringObject, ...)
Dim I1 as Long
Dim I2 as Long

I1 = LBound(StringObject) ' determine lower bound of array
I2 = UBound(StringObject) ' determine upper bound of array

End Sub[/vba]

Does this help?

[lucas]

moved to Excel help forum.

[deyken]

Hi MWE,

Thank you very much! This helps a lot. What I also needed to know was this:

During some function/procedure I may need to create this String array at run time to store an unknown amount of strings. This latter function might have several for..loops from which I add (based on certain conditions) Messages or other strings throughout the process. A later algorithm inside of said function would then access this dynamic array and process the messages or string information. Once done, I would Free/Destroy this virtually created string array to clear the memory.

The String array / object that I refer to can be described as a virtual (non-visual) Listbox component. Does VBA have this functionality? If not, can it be created? Perhaps as a class object?

[Paul_Hossler]

deyken:

I also come to VBA from a Delphi background.

Read up on Redim Preserve for arrays, but you also might consider using a VBA Collection

You can pass an array or a collection to a Variant, since VBA isn't as strongly typed as Pascal derivitives.

Got an specific example of what you want to do?

Paul

[mdmackillop]

Rather than redim in each loop, depending upon size, you can set a size greater than you need then redim on completion.
[vba]
Dim i As Long
Dim StringArray() As String

ReDim StringArray(1000)
For Each cel In Range("Test")
StringArray(i) = cel
i = i + 1
Next
ReDim Preserve StringArray(i - 1)
[/vba]

Note that in 2D arrays, you can only redim the second dimension

[vba]
Redim arr(1,100)
'your code
Redim Preserve arr(1,i-1)
[/vba]