I am fairly new to VBA, so this may be a simple question but here goes.
I would like to initialize an entire array myArray
, say of integers, in VBA. I know that I can do this by a simple initialization like so:
Dim myArray
myArray = Array(1, 2, 4, 8)
But if the array is large this is cumbersome, and I'd like to initialize all of the elements to the same value. Ideally it would be something like this:
myArray(:) = 0
I tried that but the compiler complained. Then I tried myArray() = 0
and it complained about that, too.
Can anyone explain how to do this, without looping? I'd like to do it in one statement if possible.
Clarification:
I want to initialize every single element of the array to some initial value.
So if I have an array Dim myArray(300) As Integer
of 300 integers, for example, all 300 elements would hold the same initial value (say, the number 13).
More Clarification
I found this answer that states that you can do this with a variable like so:
Dim x As Double: x = 0
Perhaps there is a way to update the syntax slightly to make it applicable to arrays?
This question is related to
arrays
vba
excel
initialization
This function works with variables for size and initial value it combines tbur & Filipe responses.
Function ArrayIniValue(iSize As Integer, iValue As Integer)
Dim sIndex As String
sIndex = "INDEX(Row(1:" & iSize & "),)"
ArrayIniValue = Evaluate("=Transpose(" & sIndex & "-" & sIndex & "+" & iValue & ")")
End Function
Called this way:
myArray = ArrayIniValue(350, 13)
I want to initialize every single element of the array to some initial value. So if I have an array Dim myArray(300) As Integer of 300 integers, for example, all 300 elements would hold the same initial value (say, the number 13).
Can anyone explain how to do this, without looping? I'd like to do it in one statement if possible.
What do I win?
Sub SuperTest()
Dim myArray
myArray = Application.Transpose([index(Row(1:300),)-index(Row(1:300),)+13])
End Sub
You can initialize the array by specifying the dimensions. For example
Dim myArray(10) As Integer
Dim myArray(1 to 10) As Integer
If you are working with arrays and if this is your first time then I would recommend visiting Chip Pearson's WEBSITE.
What does this initialize to? For example, what if I want to initialize the entire array to 13?
When you want to initailize the array of 13 elements then you can do it in two ways
Dim myArray(12) As Integer
Dim myArray(1 to 13) As Integer
In the first the lower bound of the array would start with 0
so you can store 13 elements in array. For example
myArray(0) = 1
myArray(1) = 2
'
'
'
myArray(12) = 13
In the second example you have specified the lower bounds as 1
so your array starts with 1
and can again store 13 values
myArray(1) = 1
myArray(2) = 2
'
'
'
myArray(13) = 13
Wnen you initialize an array using any of the above methods, the value of each element in the array is equal to 0
. To check that try this code.
Sub Sample()
Dim myArray(12) As Integer
Dim i As Integer
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub
or
Sub Sample()
Dim myArray(1 to 13) As Integer
Dim i As Integer
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub
FOLLOWUP FROM COMMENTS
So, in this example every value would be 13. So if I had an array Dim myArray(300) As Integer, all 300 elements would hold the value 13
Like I mentioned, AFAIK, there is no direct way of achieving what you want. Having said that here is one way which uses worksheet function Rept
to create a repetitive string of 13's. Once we have that string, we can use SPLIT
using ","
as a delimiter. But note this creates a variant array but can be used in calculations.
Note also, that in the following examples myArray
will actually hold 301 values of which the last one is empty - you would have to account for that by additionally initializing this value or removing the last "," from sNum
before the Split
operation.
Sub Sample()
Dim sNum As String
Dim i As Integer
Dim myArray
'~~> Create a string with 13 three hundred times separated by comma
'~~> 13,13,13,13...13,13 (300 times)
sNum = WorksheetFunction.Rept("13,", 300)
sNum = Left(sNum, Len(sNum) - 1)
myArray = Split(sNum, ",")
For i = LBound(myArray) To UBound(myArray)
Debug.Print myArray(i)
Next i
End Sub
Using the variant array in calculations
Sub Sample()
Dim sNum As String
Dim i As Integer
Dim myArray
'~~> Create a string with 13 three hundred times separated by comma
sNum = WorksheetFunction.Rept("13,", 300)
sNum = Left(sNum, Len(sNum) - 1)
myArray = Split(sNum, ",")
For i = LBound(myArray) To UBound(myArray)
Debug.Print Val(myArray(i)) + Val(myArray(i))
Next i
End Sub
Fancy way to put @rdhs answer in a function:
Function arrayZero(size As Integer)
arrayZero = Evaluate("=IF(ISERROR(Transpose(A1:A" & size & ")), 0, 0)")
End Function
And use like this:
myArray = arrayZero(15)
For VBA you need to initialise in two lines.
Sub TestArray()
Dim myArray
myArray = Array(1, 2, 4, 8)
End Sub
Source: Stackoverflow.com