2D array of strings problem

[Robert Crandal]

I have the following erroneous subroutine:

'=================================
Sub DoStuff (A as Integer, B as Integer)

Dim (A, B) as String ' This creates an error!

End Sub
'================================

Is it possible to create a 2D array whose size depends
on the values of A and B above? I'm basically
looking for a way to create a 2D array whose
dimensions are not known at runtime. The size
of this 2D array will stay constant for the duration
of the "DoStuff" procedure, so I only need to set
it's size once.

Any ideas?

- Robert

 

[Walter Briscoe]

In message <[email protected]> of Fri, 18
Feb 2011 23:18:12 in microsoft.public.excel.programming, Robert Crandal

I have the following erroneous subroutine:

'=================================
Sub DoStuff (A as Integer, B as Integer)

Dim (A, B) as String ' This creates an error!

You have not named that array
Dim X(A, B) as String ' Gets no error

 

[joeu2004]

Sub DoStuff (A as Integer, B as Integer)
Dim (A, B) as String   ' This creates an error!
End Sub [....]
Is it possible to create a 2D array whose size depends
on the values of A and B above?

Yes: use ReDim instead Dim. For example:

Sub doit1(a As Long, b As Long)
ReDim s(a, b) As String
MsgBox "a=" & a & " b=" & b & Chr(10) & _
LBound(s, 1) & ":" & UBound(s, 1) & _
" " & LBound(s, 2) & ":" & UBound(s, 2)
End Sub

Sub callit1()
doit1 12, 34
doit1 23, 45
End Sub

However, in that form, the lower bound depends on the Option Base. It
would be prudent to specify it explicitly, for example:

ReDim s(1 to a, 1 to b) As String

The size of this 2D array will stay constant for the duration
of the "DoStuff" procedure, so I only need to set it's size once.

So place the ReDim statement early in the procedure.

However, if you mean that you want to set the size only on the first
call and retain the original size on subsequent calls even if a and b
are different, you could do the following:

Sub doit2(a As Long, b As Long)
Static first As Long
Static s 'must be Variant type
If first = 0 Then
'first call only
first = 1: ReDim s(a, b) As String
s(1, 1) = "s(1,1)": s(1, 2) = "s(1,2)"
s(2, 1) = "s(2,1)": s(2, 2) = "s(2,2)"
End If
MsgBox "a=" & a & " b=" & b & Chr(10) & _
LBound(s, 1) & ":" & UBound(s, 1) & _
" " & LBound(s, 2) & ":" & UBound(s, 2) & _
Chr(10) & _
":" & s(1, 1) & ":" & s(1, 2) & ":" & _
s(2, 1) & ":" & s(2, 2) & ":"
End Sub

Sub callit2()
doit2 12, 34
doit2 23, 45
End Sub

 

[Robert Crandal]

Thanks, that worked perfectly.

BTW, is it possible to have a 2D array dimensioned
like this:

ReDim (0 to A, 0 to 0) ' Is this possible?

I'm thinking that the second element will usually contain
ONE element, but it will be at index "0".

Sub DoStuff (A as Integer, B as Integer)
Dim (A, B) as String ' This creates an error!
End Sub
[....]
Is it possible to create a 2D array whose size depends
on the values of A and B above?

Yes: use ReDim instead Dim. For example:

Sub doit1(a As Long, b As Long)
ReDim s(a, b) As String
MsgBox "a=" & a & " b=" & b & Chr(10) & _
LBound(s, 1) & ":" & UBound(s, 1) & _
" " & LBound(s, 2) & ":" & UBound(s, 2)
End Sub

 

[Robert Crandal]

You can actually disregard my 2nd question, cuz apparently it
works, but I might not use that after all.

Thank you for your help!

 

[joeu2004]

You have not named that array
Dim X(A, B) as String ' Gets no error

Until you try to call it. Then you get the runtime error "compile
error: constant expression required".