[RESOLVED] [VBA Excel] Assigning values to array in a single line

I haven't worked with data arrays in VBA for a while, but thought that it would be a fairly simple procedure to assign values to a one-dimensional array in a single line of code (as opposed to assigning each value in the array separately).

For example, if I wanted an array containing the numbers "one", "two", and "three", I would have expected something like the following to work:

Code:

---

Dim My_Array(1 To 3) As String

My_Array = Array("one", "two", "three")

---

I managed to find someone else with the same problem on another forum (link here), and the accepted solution was more or less exactly what I have above.

However, this gives me a compile error "Can't assign to array". Is there a way to achieve what I want to do?

Thanks in advance

If you'd do it with a declared variant, it would work!

Code:

---

Dim My_Array
My_Array = Array("one", "two", "three")

---

Thanks for the reply Opus.

I can't say I'm satisfied with that answer though because I know it would work declaring the array as a string and entering the values separately. There has to be a more efficient way than resorting to variant type variables (which to my knowledge requires more memory than is necessary) :(

i think the problem is that once you give an array its dimensions it then relies on the use of them for boundary, acess and checking issues

just checked (experiment an dread the floaty label thingy)

array() is a variant making function

so you need to use variant holding array

It's in the manual!

here to talk

Quote:

---

Originally Posted by Earlien

Thanks for the reply Opus.

I can't say I'm satisfied with that answer though because I know it would work declaring the array as a string and entering the values separately. There has to be a more efficient way than resorting to variant type variables (which to my knowledge requires more memory than is necessary) :(

---

You didn't ask for a way with less memory usage, you asked for a way to fill the array using a single line!

Quote:

---

Originally Posted by opus

You didn't ask for a way with less memory usage, you asked for a way to fill the array using a single line!

---

Well, naturally any good programmer wishes to minimise memory used and the time taken to run the code (i.e. less lines of code) :). Based on your first answer and the lack of other replies, I'm beginning to think that it is a case of choosing one or the other - declare it as a variant and use one line of code to assign values, OR declare it as some other data type and assign values one by one.

If anyone has any other ideas, I'd be happy to hear them.


(P.s. @ Opus: Actually, your idea of using a variant data type may not be as inefficient as I would have thought. I found that a variable length string takes up 10 bytes + string length, a fixed length string takes up memory equal to the length of the string, and a variant takes up 16 bytes of memory. According to my understanding, that means a string of 7 characters long stored as a variant takes up less memory than if it was stored as a string data type :ehh: )

Quote:

---

Originally Posted by opus

If you'd do it with a declared variant, it would work!

Code:

---

Dim My_Array
My_Array = Array("one", "two", "three")

---

---

Actually, I tried this and I still get the compile error "Can't assign to array."

Code:

---

Dim Rows_Array(1 To 7) As Variant

Rows_Array = Array(5, 9, 14, 19, 25, 29, 34)

---

Any other ideas?


Edit: I'm not looking for a solution which must use the "= array()" command. I just want a solution which can assign values to an array without having to assign them individually, line by line. (In fact, considering the array command requires the data type to be a variant, I would rather not use this at all).

Ok, I've discovered it works if you declare the array as variant AND as a dynamic array. I doesn't appear to work for fixed-length arrays, no matter what data type you use.

Is there no better solution? :(

Here's how I would accomplish what I think you're requesting.

vb Code:

Private Sub DarrensTest()
    Dim strMyArray()    As String
    
    strMyArray() = Split("A,B,C,1,2,3", ",", -1, vbBinaryCompare)
    
End Sub

On the edit: Here's a sample w/o using a comma. Hopefully this removes some possible confusion about the comma.

vb Code:

Private Sub DarrensTest()
    Dim strMyArray()    As String
    
    strMyArray() = Split("A|B|C|1|2|3", "|", -1, vbBinaryCompare)
    
End Sub

Aha!! That's exactly what I was looking for :) I must look into this split function sometime and find out exactly what its doing (although it seems fairly self-explanatory).

Thanks Darren, you're a life saver :D

You are very welcome. And, like you, when I found out about Split() I did a "face palm". Great little function. I use it in almost every app I have. (That may not be a good thing! :o) However, it has served me well and I'm sure I haven't used it to it's fullest potential.

Hmm, I tried declaring the array as an integer then removing the "A,B,C" part but it returns type mismatch error. I'm guessing that's because the first expression in the split function must be string?

But no big problem. Using a string to store several integers is better than 500 lines of code to assign the values one by one in an integer array.

Thanks again.

Quote:

---

Originally Posted by Earlien

Hmm, I tried declaring the array as an integer then removing the "A,B,C" part but it returns type mismatch error. I'm guessing that's because the first expression in the split function must be string?

But no big problem. Using a string to store several integers is better than 500 lines of code to assign the values one by one in an integer array.

Thanks again.

---

Post your code.... I'd like to see the Split() fail.

Also, your original post had nothing but strings...... I'm just saying. :)

On the edit.....
Here's a quip from the "HELP"

Quote:

---

Originally Posted by VB Help

Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.

---

Yup, string is required. While not ideal, re: memory/speed, the simplicity of converting data from integer to string may be worth while. Be wary of doing this with singles and especially with doubles! Just my 2¢.

Its true, I was originally looking at any sort of array, strings, integers or otherwise. However, I think I may only end up requiring integers in the array, so thought I'd push this split function to the limit and see how it performed :)

A short version of my code is something like:

Code:

---

Private Sub Main()

    Dim Rows_Array() As String
    Dim i as integer, j as integer

    Rows_Array() = Split("5 9 14 19 25 29 34 66 67 68 72 88 101 102 103 105 118 119 120 123 126 129 144 146 149", " ")
   
    For i = LBound(Rows_Array()) To UBound(Rows_Array())
        j = Rows_Array(i)
        Debug.Print j        'This is where my code actually does stuff with j
    Next i
   
End Sub

---

Try changing the array to type integer and you get a type mismatch error. Even though the expression in split is a string, the components of it could be converted to integers (i.e. they really are numbers) so not sure why this should pose any problem to the compiler.


Edit: Yea I agree with you. I think the convenience of being able to assign values in one (or a few) lines outweighs the loss in memory/speed by declaring the array as a string.

Edit: I wonder if its worth declaring a second array as an integer, then assigning the values of the first array to the second, then setting the second array to nothing, effectively freeing up that memory. So basically the string array is only used for the assigning of values then immediately retired. Something like:

Code:

---

Private Sub Test()

    Dim Rows_Array1() As String
    Dim Rows_Array2() As Integer
    Dim i As Integer, j As Integer
   
    Rows_Array1() = Split("5 9 14 19 25 29 34 66 67 68 72 88 101 102 103 105 118 119 120 123 126 129 144 146 149", " ")
   
    ReDim Rows_Array2(0) As Integer
    For i = LBound(Rows_Array1()) To UBound(Rows_Array1())
        ReDim Preserve Rows_Array2(0 To (UBound(Rows_Array2) + 1)) As Integer
        Rows_Array2(i) = Rows_Array1(i)
    Next i
    ReDim Preserve Rows_Array2(0 To (UBound(Rows_Array2) - 1)) As Integer
   
    Erase Rows_Array1

End Sub

---

Well, convenience always comes at a price, doesn't it? ;) Run your code, get it working where you're happy with it then simply put a For Next loop that iterates through the code 100,000,000 times and report on the time it takes it to run. If the time taken is with in expected results for 100 million iterations go on with life. (Assuming 100 million is HUGE in comparison to the absolute maximum expected number of iterations * 100.)
I've often found myself coding because of principle when I should have been coding because of real world expectations. LaVolpe proved this to me just the other day. Just proves we ALL have room to grow and learn. :)

Good advice :) I may try this when I'm done and post the results.

Good luck Earlien, and welcome to the forum! (Sorry, didn't notice your join date before. :))

Well, I tried implementing my idea of declaring a second array as an integer, then assigning the values of the first array to the second, then setting the second array to nothing, effectively freeing up that memory. This works great. (See code a few posts above)

To see how much more efficient it was to use an array declared as the most appropriate data type, I used Darren's suggestion of timing how long it took to run 1 million iterations of a simple routine using the original array (string) and the second array (integer)

Code:

---

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Option Explicit

Private Sub Test()

    Dim Rows_Array1() As String
    Dim Rows_Array2() As Integer
    Dim i As Integer, j As Integer
    Dim k As Long
    Dim Sum As Integer
    Dim t As Double
   
    Rows_Array1() = Split("5 9 14 19 25 29 34 66 67 68 72 88 101 102 103 105 118 119 120 123 126 129 144 146 149", " ")
   
    ReDim Rows_Array2(0) As Integer
    For i = LBound(Rows_Array1()) To UBound(Rows_Array1())
        ReDim Preserve Rows_Array2(0 To (UBound(Rows_Array2) + 1)) As Integer
        Rows_Array2(i) = Rows_Array1(i)
    Next i
    ReDim Preserve Rows_Array2(0 To (UBound(Rows_Array2) - 1)) As Integer
   
    'Calculate time taken to use Rows_Array1 (string)
    t = GetTickCount
    For k = 1 To 1000000
        Sum = 0
        For i = LBound(Rows_Array1()) To UBound(Rows_Array1())
            Sum = Sum + Rows_Array1(i)
        Next i
    Next k
    MsgBox "Macro took " & GetTickCount - t & " milliseconds to run.", vbInformation, "Time Taken"
   
    Erase Rows_Array1
   
    'Calculate time taken to use Rows_Array2 (integer)
    t = GetTickCount
    For k = 1 To 1000000
        Sum = 0
        For i = LBound(Rows_Array2()) To UBound(Rows_Array2())
            Sum = Sum + Rows_Array2(i)
        Next i
    Next k
    MsgBox "Macro took " & GetTickCount - t & " milliseconds to run.", vbInformation, "Time Taken"

End Sub

---

On my computer it took 8438 milliseconds to run using the string array, and only 844 milliseconds to run using the integer array (roughly ten times faster).

The results are clear: if you require the convenience of using the split function to assign values to an array in a single line, and the goal is to maximise the speed of the macro and/or minimise memory used, then create a second array and copy the values from the first array to the second array which is declared as the data type desired, then delete the first array, thereby freeing up that memory too. This saves lines of code to assign values, memory used, and time taken to run code.

Very nice follow up to your original post! Thank you for contributing to the forum! I certainly hope you book mark the forum and continue to ask questions and participate.
Cheers!

Quote:

---

Originally Posted by Earlien

The results are clear: if you require the convenience of using the split function to assign values to an array in a single line, and the goal is to maximise the speed of the macro and/or minimise memory used, then create a second array and copy the values from the first array to the second array which is declared as the data type desired, then delete the first array, thereby freeing up that memory too. This saves lines of code to assign values, memory used, and time taken to run code.

---

Sorry, but I have to disagree!
Your benchmark proved that doing calculations on number-type variables is way faster then doing it on STRINGS.That was a known fact!
Try this change in the String calculation:

Code:

---

Sum = Sum + CInt(Rows_Array1(i))

---

Now the String is changed to an Integer and then the calculation is done, the time used is drops to 30% of the original!

Coming back to your starting problem, I don't see that you are saving code lines! Instead of

Quote:

---

assign values to a one-dimensional array in a single line of code (as opposed to assigning each value in the array separately)

---

, you are now creating a secondary array, for which you are assigning each value separately.

Hi Opus. Perhaps the following can allay your confusion :)

Suppose you want to store the integers 1 to 10 in an array. This can be done easily with a loop right?

Code:

---

Private Sub Test1()

    Dim My_Array(1 To 10) As Integer
    Dim i As Integer
   
    For i = 1 To 10
        My_Array(i) = i
    Next i
   
End Sub

---

But what if there is no obvious pattern amongst the values you want to assign? What if the "values" are strings? Using a loop is no longer an option. The only other way is to assign the values one-by-one. Suppose you want to assign the words "The", "cat", "in", "the", "hat". You would have to assign them as follows:

Code:

---

Private Sub Test2()

    Dim My_Array(1 To 5) As String
   
    My_Array(1) = "The"
    My_Array(2) = "cat"
    My_Array(3) = "in"
    My_Array(4) = "the"
    My_Array(5) = "hat"
   
End Sub

---

Imagine if you had 1,000 "values" to enter....or even more! This is tedious and takes up many lines of code.

The solution offered by Darren was to use the split function. This makes assigning values to an array much simpler, and works perfectly if you want to store strings in an array.

However, this raised a new issue. The split function requires that the array be declared as a string data type (or higher - variant should also work). But if you only need to store integers, bytes, or other data types which use less memory than strings in the array, the convenience of assigning values using the split function comes at the price of using more memory than necessary (and taking longer to run as my code above shows).

It would be nice to have a solution that allows you to assign values to an array concisely AND be able to store them as whatever data type you wish. This lead to my idea above of declaring a second array, transferring the values to that array, and erasing the first. Then when the second array is used in code, it runs much faster (and as you correctly pointed out, this is expected behavior - I just wanted to see how much faster :))

Hope that clears up any confusion :)

Quote:

---

Originally Posted by Darren M.

I certainly hope you book mark the forum and continue to ask questions and participate.
Cheers!

---

I have, and I will :)

Quote:

---

Originally Posted by Darren M.

Here's how I would accomplish what I think you're requesting.

vb Code:

Private Sub DarrensTest()
    Dim strMyArray()    As String
    
    strMyArray() = Split("A,B,C,1,2,3", ",", -1, vbBinaryCompare)
    
End Sub

On the edit: Here's a sample w/o using a comma. Hopefully this removes some possible confusion about the comma.

vb Code:

Private Sub DarrensTest()
    Dim strMyArray()    As String
    
    strMyArray() = Split("A|B|C|1|2|3", "|", -1, vbBinaryCompare)
    
End Sub

---

This code is great! Thank you so much for posting it. A quick question, any ideas on how you would go about putting this code to use populating the second part of a 2D array. The code below is my attempt at it but it gives a type mismatch error.

Code:

---

Dim MCCinfo(2) As String
MCCinfo(0) = Split("Wires,Voltage,MCB,Bus,kAIC,source,NEMA,Bus Type,Neutral",",",xlBinaryCompare)

---

Any thoughts are appreciated.

Assuming you mean you want to place some values into the 2d array's second dimension...what do you want to place there? The "zero" (first) element of the split string (in this case "Wires")? Or something else?

I am looking to replace a code similar to this one:

Code:

---

MCCinfo(0, 0) = "MCB"
MCCinfo(0, 1) = "Voltage"
MCCinfo(0, 2) = "Bus"
MCCinfo(0, 3) = "Bus Type"
MCCinfo(0, 4) = "Neutral"
MCCinfo(0, 5) = "source"
MCCinfo(0, 6) = "Voltage"
MCCinfo(0, 7) = "NEMA"
MCCinfo(0, 8) = "kAIC"

---

I apologize that the values have changed order, i am actively progressing this code.

for example:

Code:

---

Sub pop2d()
    Dim mccInfo(0, 8) As String
    Dim str As String
    Dim splits() As String
    Dim j As Integer
   
    str = "MCB,Voltage,Bus,Bus Type,Neutral,source,Voltage,NEMA,kAIC"
    splits = Split(str, ",")
    For j = 0 To 8
        mccInfo(0, j) = splits(j)
    Next j
End Sub

---

this will work to create an array of string arrays, though that is probably not what you were asking

Quote:

---

Dim MCCinfo(2)
MCCinfo(0) = Split("Wires,Voltage,MCB,Bus,kAIC,source,NEMA,Bus Type,Neutral", ",")

---

I believe what you are looking for is something like:

dim arr(1 To 4) As Integer
arr() = Array(3, 7, 5, 2)

or

dim arr(1 to 4) As String
arr() = Array("How", "Now", "Brown", "Cow")

Never mind, just re-read the initial post...my bad