• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA with Arrays. Completely lost.

S

shaywood_1991

New Member
Start out with a column of randomly generated numbers. There should be 300 numbers in the column. You will need at least 4 arrays in your program, each holding 300 values.


1. Bring the values that you generated in the first column in your spreadsheet into an array named FirstCol.


2.Convert the values into integers and save them into an array named SecondCol.


3. Loop through the values in SecondCol.

a)If the value is less than 10 put that value in an array named Less10.

b)If a value is equal to or greater than 10 but less than 20 put that value into an array named MessTwenty

c) All other values put in an array named MoreTwenty.


4. For each of the 3 arrays just generated, calculate the number of values in the array, the average of all the values in the array, the minimum value in the array, and the maximum value in the array. You should be able to do the last 3 tasks in one loop through the array.


5. For each of the three arrays generated in step 4, count the number of values in the array that are less than the average value you just found.


6.For each of the 3 arrays generated in step 4, display on the worksheet the number of values in the array, the average, the minimum, the maximum, and the number of values that are less than average.


Any help is appreciated. I cannot figure this code out for some reason.
 
Hi, shaywood_1991!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about any question in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


You say that you couldn't figure this code out for some reason.

a) Would you please consider uploading a sample file with the code you've managed to write -even if it doesn't fully work- (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
All I have done is Dim the arrays as string. But I cannot figure out how to get data from my first array into my second array.
 
Hi,

All I have done is Dim the arrays as string.



This looks like a homework exercise so I'm going to limit my input, but why have you declared your arrays as String?


Numbers in a worksheet are Double types, so your FirstCol array should be Double. I say this because I assume the point of the exercise is for you to loop through the cells and put them in the array one at a time. There is a faster way to do the assignment, but this loop is probably what the question is looking for.


The second question tells you to convert the Double types to Integers and then store them in an array called SecondCol, so SecondCol should be declared as an Integer array. MessTwenty (should that be LessTwenty?) and MoreTwenty will hold values from SecondCol, so they should also be declared as Integer arrays.



But I cannot figure out how to get data from my first array into my second array
Click to expand...

Make sure the second array is the same size as the first array. Then loop through the first array and use the CInt() function to convert each element into an Integer and assign it to the second array. Since your second array should be of type Integer, you need to make sure that the values aren't too small or big to be cast to an Integer (check your notes or the helpfile to see what the limits are) otherwise you will get an Overflow error at runtime.
 
@Colin Legg

Hi!

I yet supposed it was surely a homework but a still a valid question at this forums, but homework at last so I decided to ask instead of writing ... 20 lines?

I just wanted to know what has this kid done until now, because I smelt such answer. Well, you posted almost what I'd have done, now it's up to him to exercise an apply what learned.

Regards!
 
Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647


So if your original numbers are less/bigger than -/+32768 you should use Long's instead of Integers for the second and subsequent arrays
 
Hi, shaywood_1991!

Assuming you're not coming back as you didn't get the expected answer at the very first moment, here's the link to my suggested solution:

https://dl.dropboxusercontent.com/u/60558749/VBA%20with%20Arrays.%20Completely%20lost.%20%28for%20shaywood_1991%20at%20chandoo.org%29.xlsm

Regards!
 
Just in case Shaywood does ever come back, I would have loaded FirstCol slightly differently


Everything else is as SirJB7 has done


By loading the array in 1 pass VBA only goes through the VBA - Excel interface once

rather than 300 times as SirJB7 coded.


This should make the program faster (probably doesn't matter in this case) but it is a good code optimisation example

[pre]
Code: 
Dim FirstCol() As Variant
FirstCol = Range("A1:A300").Value 'This loads the FirstCol(300,1) array in 1 pass

For I = 1 To 300
' 1.
'FirstCol(I) = ActiveSheet.Cells(I, 1).Value
' 2.
SecondCol(I) = Int(FirstCol(I, 1))
[/pre]
 
@Hui

Hi!

You're absolutely right, as an example of best practices it's correct.

I was yet wondering if shaywood_1991 would have been able to explain how did he manage to handle 1., 2. & 3. within the same For...Next loop, for not talking about how he achieved the final solution, so I tried to don't make things harder for him to explain the "how did you do this?" to his teachers... still having in mind this comment of him "All I have done is Dim the arrays as string. But I cannot figure out how to get data from my first array into my second array."

Regards!
 
I did a quick timing text from the start of the code to the end of the Loop which loads the FirstCol array


You orginal code does it in 0.014 seconds

my code does it in 0.020 seconds 5 thousandths of a second slower


I suspect that the speed gain in loading the array is lost by use of the variant array compared to the efficiency using the Integer array, as well 300 records is not a significant number either and so the gaps would be bigger if it were 30,000 rows
 
@Hui

Hi!

Changed array sizes from 300 to 30000. My code, 0,35/0,36 sec, your code 0,05/0,06 sec. An improvement between 500% and 600% (i.e., just in case anyone says that my math isn't accurate), a ratio of 6:1 thru 7:1.

Regards!
 
VBA used to get a bad wrap for speed due to this

But it was/is the poor use of the system, not the intrinsic speed of VBA that is the cause of sluggish performance
 
You must log in or register to reply here.
Back
Top