I am trying to clean up some of my VBA code by removing redundancies and making more reusable functions. One place I am trying to do this is a function where I take an array of row numbers (e.g. [3, 20, 45]
) and a column integer (e.g. 5
meaning E
) and turn it into a string: (e.g. "E3, E20, E45"
).
Original Code
Here is my original VBA code that I am trying to transfer:
Dim customRange As String
customRange = ""
For cc = 0 To UBound(resultArr)
If cc = UBound(resultArr) Then
customRange = customRange & "E" & resultArr(cc)
Else
customRange = customRange & "E" & resultArr(cc) & ", "
End If
Next cc
(keep in mind, I wasn't trying to convert the column number to a letter here yet)
First Attempt
And this was working fine. Now that I am trying to clean up my code by creating functions for redundant code, I am getting some errors. I am fairly new to VBA in general so this could be fairly obvious. This was my first attempt:
The Call:
customRange = BuildRangeStr(resultArr, 5)
The Function:
Function BuildRangeStr(ByRef custRowArr As Variant, ByVal custCol As Integer) As String
Dim aa As Integer
'convert custCol to column letter
If custCol <= 26 Then
column_letter = Chr(64 + custCol)
Else
column_letter = Chr(Int((custCol - 1) / 26) + 64) & Chr(((custCol - 1) Mod 26) + 65)
End If
BuildRangeStr = ""
'build string
For aa = 0 To UBound(custRowArr)
If aa = UBound(custRowArr) Then
Set BuildRangeStr = BuildRangeStr & "E" & custRowArr(aa)
Else
Set BuildRangeStr = BuildRangeStr & "E" & custRowArr(aa) & ", "
End If
Next aa
End Function
Second Attempt
With this first attempt I had some errors with passing in the Array. So, I went to creating a Variant of the Array as passing that in for my second attempt:
The Call:
Dim MyVar As Variant
MyVar = resultArr()
customRange = BuildRangeStr(MyVar, 5)
The Function:
Function BuildRangeStr(ByVal custRowArr As Variant, ByVal custCol As Integer) As String
Dim aa As Integer
Dim tempStr As String
'convert custCol to column letter
If custCol <= 26 Then
column_letter = Chr(64 + custCol)
Else
column_letter = Chr(Int((custCol - 1) / 26) + 64) & Chr(((custCol - 1) Mod 26) + 65)
End If
tempStr = ""
'build string
For aa = 0 To UBound(custRowArr)
If aa = UBound(custRowArr) Then
tempStr = tempStr & "E" & custRowArr(aa)
Else
tempStr = tempStr & "E" & custRowArr(aa) & ", "
End If
Next aa
Set BuildRangeStr = tempStr
End Function
Current Error
I am now getting an error "Object Required".
Ideal Solution
Ideally, I would like to not have to create a variant for my Array to increase simplicity in my code, but I understand that may be required for best use-case.
Really, all I need is to have my function run the same way the code did before, but without cluttering my code with redundant snippets.
If anyone has any ideas I would really appreciate it!