VBA Excel Array: How to understand array in VBA
Friday, December 17, 2010
It has been a very hectic day for me recently. I’ve been involved in one of my company project that required me to program some intricate array of datas. As a new employed staff there my knowledge on programming in vba is just quite a few. To think and handle with a huge of data after data make my day so miserable. This project is actually required me to sort and to recognize what type of data that need to be used in the calculation within this project. But after completing this task my knowledge on sorting and the use of the function of array in VBA become very rigid that I think I can easily handle an easy array now. :)
And today I would like to share some of my experiences handling with quite a bunch of datas that are related with one another and how to understand the metric use in array function within VBA environment. Dealing with VBA Excel array is actually quite the same as VB language applied. Specifically, I would like to make some understanding on how to sort a datas and place them in the excel spreadsheet. If you don’t know about the power of spreadsheet function you can review the topic before I post this entry here.
I will start with an example that I think would ease the way you understand this. VBA Excel array is actually counted in metric and it would normally start with (0,0) metric. As you can see here the zero on the left bracket normally represent the column while the right zero represents the row of array. Say you have a simple table represent two types of data below:
If you would like your program to be able to read this table and place it in excel sheet all you have to do is to make an array metric of (1,0)->Robert, (1,1)->A114562, (2,0)->Adam, (2,1)->A123345, (3,0)->Julia, (3,1)->A567678. Noted that metric (0,0) and (0,1) represent the “Name:” and “id. Number” respectively. This is how you can give a metric on each of your data. After recognizing all the metrics required than you can easily program your code in VBA.
Here are the steps:
1) Open your VB editor within your Microsoft Excel. If you miss up on how to open VB editor you can back refer here.
2) Define your VBA Excel array. As in the example above you need to define your array to be say:
‘Every time you loop your array, the value of i will increase from 1 to 3
3) Write your code for sorting your VBA Excel array:
4) The codes above are specifically written if you want to read an array in userform table that you must create earlier. And from this userform table you can call the data and place it in the listbox option. For example you can wrote your code as shown below:
This quick example would just show on how VBA read and program the data that need to be sort in an array form. Next time I would like to share on how this VBA Excel array function can be so much interesting when dealing with a huge datas. Till then~
Posted byMatt at 1:42 AM
Labels: Excel, Userform, VBA Array, VBA Function