I know I shouldn't be doing this, but I have to.
I'm trying to manipulate multidimensional arrays in VBA, in this specific case, I have to add a string to a multidimensional array, with all but the last dimension having single elements like Arr(1 To 1, 1 To 1, 1 To 3)
As VBA does not allow accessing elements of an array of arbitrary rank, I write a sub at runtime as:
Public Sub AddItemToReducedArr(ByRef Arr() As String, Dimensions As Byte, _
Item As String
)
Dim VBComp As VBIDE.VBComponent
Dim i As Integer
Dim ArrElementS As String
Dim ArrElementR As String
Set VBComp = ThisWorkbook.VBProject.VBComponents("modCustomCode")
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
.InsertLines 1, _
"Public Sub AddItemToReducedArrCode(ByRef Arr() As String, " & _
"Dimensions As Byte, Item As String)"
ArrElementS = _
"Arr(" & Replace(String((Dimensions - 1), "*"), "*", "1, ") & _
"*(Arr, " & Dimensions & "))"
.InsertLines 2, "Debug.Print ""Enters Sub"""
.InsertLines 3, "If LBound(Arr, " & Dimensions & ") = UBound(Arr, " & _
Dimensions & ") And " & Replace(ArrElementS, "*", "UBound") & _
" = """" Then"
.InsertLines 4, Replace(ArrElementS, "*", "UBound") & " = Item"
.InsertLines 5, "Else"
ArrElementR = _
"Arr(" & Replace(String((Dimensions - 1), "*"), "*", "1 To 1, ") & _
"LBound(Arr, " & Dimensions & ") To UBound(Arr, " & Dimensions & ") + 1)"
.InsertLines 6, "Redim Preserve " & ArrElementR
.InsertLines 7, Replace(ArrElementS, "*", "UBound") & " = Item"
.InsertLines 8, "End If"
.InsertLines 9, "End Sub"
Debug.Print "creates sub"
'I also tried adding Sleep, many DoEvents here and saving, none worked
AddItemToReducedArrCode Arr, Dimensions, Item
Debug.Print "calls proper"
End With
Set VBComp = Nothing
ResetCode
End Sub
ResetCode Subroutine just clears the code inside the created sub and is not listed for simplicity.
At this stage, VBA does not allow stepping through the code, rarely executes as intended and mostly does not execute the created sub and sometimes chrashes.
What can I be doing wrong, apart from using VBA for this kind of task? Do you think I have to give up and wait until I have other development options (a long time that will be) or is there a point that I'm missing?
You can test this code by creating a module named modCustomCode and using the below test:
Public Sub testASDF()
Dim Arr() As String
ReDim Arr(1 To 1, 1 To 2)
Arr(1, 1) = "a"
Arr(1, 2) = "b"
AddItemToReducedArr Arr, 2, "c"
Debug.Print UBound(Arr, 2)
Debug.Print Arr(1, UBound(Arr, 2))
End Sub
An alternative approach is to use Variants. Consider:
The output from this code is:
I have declared vdaA as a Variant and then used
Redimto convert it to 1D array. You will get a syntax error if you typeReDim vdaA(1)(0 to 3). However, you can convert vdaA(1) and vdaA(2) into arrays of different sizes as I have shown. Alternatively you can pass vdaA(1) to a subroutine as a Variant andReDimit there.I have converted vdaA to a Jagged array. If you search for "Jagged array" you can get fuller descriptions of them but I have given you an adequate introduction for the purposes of this answer.
As I understand it, you do not need different rows to have different number of columns but I am sure you can see the flexibility that is available. You can pass
vdaA(1)down to a sub-routine that converts it to an array.vdaA(1)(1)can then be passed down for conversion. With recursion you can declare arrays with as many dimensions as you determine to be necessary at run time. Other recursive routines can locate particular entries and set or get values.Many years ago, I did get this technique to work although it hurt my brain. I no longer have that code and I would not recommend it unless nothing else could meet the requirement. However, it can be made to work if necessary.
The code below uses a much simplier technique. It only handles regular arrays and handles a maximum of five dimensions. "Five" is arbitrary and the code could easily be adjusted to a larger limit if necessary.
Before showing the code, I wish to discuss Param Arrays. I have been surprised in the past how many experienced VBA programmers seen unaware of Param Arrays or the flexibility they give you. Sorry if I am insulting your knowledge.
A possible declaration is:
Parameters A and B are of fixed type. I could have fixed type parameters C, D, E, and so on as is required. My last parameter is a Param Array which means I can follow the values for A and B with as many parameters as I require. The following are valid calls of this routine:
In these examples I have a pattern to these extra parameters. However, VarType allows me to check the type of each parameter so they do not have to follow a simple pattern.
One of my routines has a declaration of:
Valid calls include:
These are equivalent to:
Other calls are:
Which are equivalent to:
You only expressed an interest in Strings but with Variants you can have any type for no extra effort.
The code behind VdaGetValue, for example, is simple:
Not elegant but very simple and extendable up to 10 or 15 dimensions if necessary.
The code below does not include much validation of parameters and is not fully tested. However, I think it provides an adequate demonstration of this approach.
Edit New section explaining a "problem" with Param Arrays and giving a possible solution.
Suppose I have three routines Main, SubA and SubB with both SubA and SubB having Param Arrays named "Param" as their only parameters. Suppose further that SubA passes the Param Array it receives from Main to SubB.
Within Main I have a call of SubA:
For SubA, Param will have four entries:
If SubA then calls SubB:
then SubB's Param will have not four entries. Instead it will have a single entry:
I call this nesting. If SubB can only be called by SubA then SubB can be coded to handle a nested Param Array. However, if SubB can also be called by Main, it gets a little messy. It gets messer still if you have SubC and SubD with Param Arrays and they can be called from any of their parents.
I use the following routine to convert Param Arrays and Param Arrays nested to any depth to a consistent format: