[RESOLVED] [VBA Excel] Assigning values to array in a single line-VBForums
Results 1 to 22 of 22

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

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

    Resolved [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

  2. #2
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

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

    If you'd do it with a declared variant, it would work!
    Code:
    Dim My_Array
    My_Array = Array("one", "two", "three")
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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)

  4. #4
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,197

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

    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

  5. #5
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

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

    Quote Originally Posted by Earlien View Post
    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!
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  6. #6

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    Quote Originally Posted by opus View Post
    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 )

  7. #7

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    Quote Originally Posted by opus View Post
    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).
    Last edited by Earlien; Jan 10th, 2012 at 08:58 PM. Reason: Additional info

  8. #8

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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?

  9. #9
    Addicted Member Darren M.'s Avatar
    Join Date
    Nov 2005
    Location
    D/FW
    Posts
    200

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

    Here's how I would accomplish what I think you're requesting.
    vb Code:
    1. Private Sub DarrensTest()
    2.     Dim strMyArray()    As String
    3.    
    4.     strMyArray() = Split("A,B,C,1,2,3", ",", -1, vbBinaryCompare)
    5.    
    6. 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:
    1. Private Sub DarrensTest()
    2.     Dim strMyArray()    As String
    3.    
    4.     strMyArray() = Split("A|B|C|1|2|3", "|", -1, vbBinaryCompare)
    5.    
    6. End Sub

    My attempt at a thought provoking sig.
    * Remove ALL assumptions. You'll probably find your problem.
    * New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
    * Learn how to fish!
    * Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
    * If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
    * Please mark all resolved threads as such. It makes searching for fixes SO much easier!
    * Have fun. We aren't on this rock for long, enjoy it.
    * Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.

  10. #10

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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

  11. #11
    Addicted Member Darren M.'s Avatar
    Join Date
    Nov 2005
    Location
    D/FW
    Posts
    200

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

    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! ) However, it has served me well and I'm sure I haven't used it to it's fullest potential.

    My attempt at a thought provoking sig.
    * Remove ALL assumptions. You'll probably find your problem.
    * New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
    * Learn how to fish!
    * Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
    * If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
    * Please mark all resolved threads as such. It makes searching for fixes SO much easier!
    * Have fun. We aren't on this rock for long, enjoy it.
    * Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.

  12. #12

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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.

  13. #13
    Addicted Member Darren M.'s Avatar
    Join Date
    Nov 2005
    Location
    D/FW
    Posts
    200

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

    Quote Originally Posted by Earlien View Post
    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&#162;.
    Last edited by Darren M.; Jan 10th, 2012 at 10:50 PM.

    My attempt at a thought provoking sig.
    * Remove ALL assumptions. You'll probably find your problem.
    * New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
    * Learn how to fish!
    * Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
    * If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
    * Please mark all resolved threads as such. It makes searching for fixes SO much easier!
    * Have fun. We aren't on this rock for long, enjoy it.
    * Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.

  14. #14

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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
    Last edited by Earlien; Jan 11th, 2012 at 09:25 PM. Reason: Amended code slightly

  15. #15
    Addicted Member Darren M.'s Avatar
    Join Date
    Nov 2005
    Location
    D/FW
    Posts
    200

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

    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.

    My attempt at a thought provoking sig.
    * Remove ALL assumptions. You'll probably find your problem.
    * New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
    * Learn how to fish!
    * Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
    * If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
    * Please mark all resolved threads as such. It makes searching for fixes SO much easier!
    * Have fun. We aren't on this rock for long, enjoy it.
    * Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.

  16. #16

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

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

  17. #17
    Addicted Member Darren M.'s Avatar
    Join Date
    Nov 2005
    Location
    D/FW
    Posts
    200

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

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

    My attempt at a thought provoking sig.
    * Remove ALL assumptions. You'll probably find your problem.
    * New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
    * Learn how to fish!
    * Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
    * If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
    * Please mark all resolved threads as such. It makes searching for fixes SO much easier!
    * Have fun. We aren't on this rock for long, enjoy it.
    * Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.

  18. #18

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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.
    Last edited by Earlien; Jan 11th, 2012 at 09:26 PM.

  19. #19
    Addicted Member Darren M.'s Avatar
    Join Date
    Nov 2005
    Location
    D/FW
    Posts
    200

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

    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!

    My attempt at a thought provoking sig.
    * Remove ALL assumptions. You'll probably find your problem.
    * New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
    * Learn how to fish!
    * Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
    * If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
    * Please mark all resolved threads as such. It makes searching for fixes SO much easier!
    * Have fun. We aren't on this rock for long, enjoy it.
    * Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.

  20. #20
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

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

    Quote Originally Posted by Earlien View Post
    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
    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.
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  21. #21

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    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

  22. #22

    Thread Starter
    Member
    Join Date
    Dec 2011
    Location
    Brisbane, Australia
    Posts
    43

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

    Quote Originally Posted by Darren M. View Post
    I certainly hope you book mark the forum and continue to ask questions and participate.
    Cheers!
    I have, and I will

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.