-
Jan 8th, 2012, 08:58 PM
#1
Thread Starter
Member
[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
-
Jan 9th, 2012, 01:13 AM
#2
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!
-
Jan 9th, 2012, 01:36 AM
#3
Thread Starter
Member
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)
-
Jan 9th, 2012, 05:56 AM
#4
Re: [VBA Excel] Assigning values to array in a single line
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!
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!
-
Jan 10th, 2012, 08:13 PM
#5
Thread Starter
Member
Re: [VBA Excel] Assigning values to array in a single line
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 )
-
Jan 10th, 2012, 08:52 PM
#6
Thread Starter
Member
Re: [VBA Excel] Assigning values to array in a single line
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).
Last edited by Earlien; Jan 10th, 2012 at 08:58 PM.
Reason: Additional info
-
Jan 10th, 2012, 09:26 PM
#7
Thread Starter
Member
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?
-
Jan 9th, 2012, 04:36 AM
#8
Frenzied Member
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
-
Jan 10th, 2012, 09:33 PM
#9
Addicted Member
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:
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
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.
-
Jan 10th, 2012, 10:16 PM
#10
Thread Starter
Member
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
-
Mar 17th, 2016, 09:43 AM
#11
New Member
Re: [VBA Excel] Assigning values to array in a single line
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.
-
Jan 10th, 2012, 10:21 PM
#12
Addicted Member
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.
-
Jan 10th, 2012, 10:31 PM
#13
Thread Starter
Member
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.
-
Jan 10th, 2012, 10:45 PM
#14
Addicted Member
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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"
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¢.
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.
-
Jan 10th, 2012, 10:55 PM
#15
Thread Starter
Member
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
-
Jan 10th, 2012, 11:12 PM
#16
-
Jan 10th, 2012, 11:21 PM
#17
Thread Starter
Member
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.
-
Jan 10th, 2012, 11:29 PM
#18
Addicted Member
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.
-
Jan 11th, 2012, 09:20 PM
#19
Thread Starter
Member
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.
-
Jan 12th, 2012, 12:58 AM
#20
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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
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!
-
Jan 12th, 2012, 06:35 PM
#21
Thread Starter
Member
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
-
Jan 11th, 2012, 11:32 PM
#22
Addicted Member
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.
-
Jan 12th, 2012, 06:43 PM
#23
Thread Starter
Member
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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
-
Mar 17th, 2016, 09:50 AM
#24
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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?
-
Mar 17th, 2016, 09:52 AM
#25
New Member
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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.
-
Mar 17th, 2016, 10:03 AM
#26
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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
-
Mar 17th, 2016, 02:50 PM
#27
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
this will work to create an array of string arrays, though that is probably not what you were asking
Dim MCCinfo(2)
MCCinfo(0) = Split("Wires,Voltage,MCB,Bus,kAIC,source,NEMA,Bus Type,Neutral", ",")
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 6th, 2020, 05:48 PM
#28
New Member
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
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")
-
Feb 6th, 2020, 05:57 PM
#29
New Member
Re: [RESOLVED] [VBA Excel] Assigning values to array in a single line
Never mind, just re-read the initial post...my bad
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|