VBA: Using a 'String' to reference an 'Array' with that name in UBOUND

Question

Why can't I use a string to reference an array in UBound?

For example, if I have an array called "Banks", UBound only works if I write the code this way

UBound(Banks)

It will not work if I have this:

String = "Banks"
UBound(String)

Is there any workaround this?

Perhaps it will help to be more specific with what I am trying to achieve.

I want to dynamically calculate the UBound of several arrays with as few lines of code as possible.

Given I have several banks to analyse, each of which contains several accounts, the idea would be to dynamically calc UBound for each by doing this:

'Banks
Dim Banks(1 To 5) As Variant
     Banks(1) = "GS"
     Banks(2) = "BAML"
     ...
     Banks(5) = "Citi"

'Bank Accounts
Dim GS(1 to 15) As Variant
    GS(1) = "Cash account"
    GS(2) = "Repo account"
     ...
    GS(15) = "Equities account"

Dim BAML(1 to 20) As Variant
    BAML(1) = "Prime account"
    BAML(2) = "Current account"
     ...
    BAML(20) = "FX account"

Dim Banks_Total, Banks_Count as Integer
Dim Bank As String
Dim Account_Total as Integer

Banks_Total = UBound(Banks) 

For Banks_Count = 1 to Banks_Total
Bank = Banks(Bank_Count)     
Account_Total = UBound(Bank)

The last line is where my bug is. But if I wrote Ubound(GS) it would be totally fine. Why!!!?

Because a variable and a string representation of a variable are two completely different things. You could use a [jagged array](https://stackoverflow.com/questions/9435608/how-do-i-set-up-a-jagged-array-in-vba) though. — BigBen, Dec 12 '20 at 17:42
Use a Scripting Dictionary with the bank names as the keys and the account arrays as the values. Your example is odd though since you only have the one `Banks` array you don't need to call it by name - you can reference it directly. — Tim Williams, Dec 12 '20 at 17:57
You could also create a one-line `Function` to return the upper bound of the array: `Function UpperBound(ByRef arr As Variant) As Long; UpperBound = UBound(arr,1); End Func` — PeterT, Dec 12 '20 at 18:01
Thank you for your comments. I still dont know how to solve this - tried the scripting dictionary but again, it only works inside Ubound if I define the Bank as a manually using its name (=GS) not as dynamlc variable Banks(Bank_Count) (=GS). Could you please help me further? — Cla Rosie, Dec 13 '20 at 06:51
Here is my code for the dictionary: While this works...: Dim Dictionary As Object Set Dictionary = CreateObject("Scripting.Dictionary") Dictionary.Add "Bank", GS Account_Total = UBound(Dictionary("Bank")) This doesn't :-( Dim Dictionary As Object Set Dictionary = CreateObject("Scripting.Dictionary") Dictionary.Add "Bank", Banks(Bank_Count) Account_Total = UBound(Dictionary("Bank")) — Cla Rosie, Dec 13 '20 at 07:38
For jagged array, it also doesn't work because I need to give it the UBound of the bank account in question, which agian requires manual adding of the name of the bank... please help me! — Cla Rosie, Dec 13 '20 at 07:49

score 0 · Answer 1 · answered Dec 13 '20 at 08:12

Here's a basic example:

Dim Banks As Object,k, num, tot
set Banks = createobject("scripting.dictionary")

Banks.Add "GS", Array("Cash", "Repo")
Banks.Add "BAML", Array("Equities","Prime","Current")

For Each k in Banks
    num = UBound(Banks(k)) + 1  'zero-based array
    Debug.Print k, num
    tot = tot + num
Next k

VBA: Using a 'String' to reference an 'Array' with that name in UBOUND

1 Answers1