VBA Code Writing

[sosofresh123]

Hi guys,

Recently I was away the last two days of class where the topics of VBA were introduced. I have no idea how to write VBA Coding, and I have this assignment due monday. Any help would be appreciated!

Write the VBA code for the following situations:


1.
An array of Currency values is called Sales. It contains all of the sales in a given month. Write the code to calculate the total sales in that month
and the average amount of a sale in that month. Declare all variables necessary.


2.
There are two arrays of the same size: Customers and Sales. The Customers array contains Long values, which are the Customer IDs. The
Sales array contains Integer values, and contains the number of sales made by each of those Customers. Sort the Sales array in order of highest
number of sales to lowest number of sales. Make sure to keep the Customers array values in the same order, so that the index of a Customer
ID would be the same index used in the Sales array to find their total sales.


NOTE: You can use whatever sorting algorithm you wish. If you don’t know any, you might need to find one online. You can use a separate
array to move the sorted items into temporarily, but then you must copy the values, in order, back into the Sales and Customers arrays.

[SamT]

1. Loop thru the array totaling all values. The Ubound function will tell you the number of values.
2. I would use the VBA Functions Index and Max to place both values in a Scripting Dictionary Object. After that a simple Array = Keys and Array = Items will suffice.

[Bob Phillips]

Do you know how to create a VBA procedure?
Do you know how to write and control loops?
Do you know what an array is?
Do you know how this pre-loaded array will be passed to your code?
Do you know how to match items in separate arrays?
Do you know of any sorting methods so that you can lookup an algorithm?

If you don't know (at least) all of that, you are going to struggle. I think the second question is particularly hard for a novice, unless that topic was specifically covered in your class, because there is nothing in the two arrays to link the array members aside from its index, which is a rubbish connection.