make money

jav online; jav streaming; 1pondo; caribbeancom; heyzo - tokyo hot

Award Winning Design Blog

Excel VBA – Create A Basic Array Part 1

By on May 31, 2011 in Programming, SPPID, Tutorial with 4 Comments

Most design software output’s to Microsoft Excel in some form or fashion. In this 2 part tutorial, I will show you how to collect an array of cell values in M$oft Excel VBA. There a few ways to loop through each row in excel VBA. For me, the quickest way is to dump the data into an array.

[frame_right src=”http://www.davetyner.com/wp-content/themes/Sabuy/timthumb.php?src=http://www.davetyner.com/wp-content/uploads/2011/05/excelArray011.jpg&h=180&w=320&zc=1″ href=”http://www.davetyner.com/wp-content/uploads/2011/05/excelArray011.jpg”]
Example Data
[/frame_right]
1. Open your excel spreadsheet.
2. Save it as another file name so you don’t inadvertently mess something up while you’re playing. Here is mine:


[frame_right src=”http://www.davetyner.com/wp-content/themes/Sabuy/timthumb.php?src=http://www.davetyner.com/wp-content/uploads/2011/05/excelArray-create-module.jpg&h=180&w=320&zc=1″ href=”http://www.davetyner.com/wp-content/uploads/2011/05/excelArray-create-module.jpg”]
Create Module
[/frame_right]
3. Open the VBA Editor (Press Alt+F11)

4. Create a new Module


5. Paste in the code below


sub CollectRowValuesInAnArray()
dim startrow as long
dim lastrow as long
dim myArray()
dim a as long
dim i as long
dim myRowValue as string
dim myColumn as long

'set the column number you want to find the value for A=1 B=2 C=3 etc
myColumn = 2

'set your startrow to the begining of your row data
'this could be row 1 but sometimes there is a header to consider
startrow = 1

'we want to find the last row that contains data
lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row 'the last row with data

'now we just do a simple "for" loop

For i = startrow to lastrow
'store the row value in the myRowValue variable
myRowValue = cells(i,myColumn).value

'you must have this line to preserve what's in the array so when you add a new
'value it doesn't overwrite the old value

redim preserve myArray(a)
myArray(a) = myRowValue
a=a+1

next

For n = lbound(myArray) to ubound(myArray)

debug.print myArray(n)

next

end sub

[frame_right src=”http://www.davetyner.com/wp-content/themes/Sabuy/timthumb.php?src=http://www.davetyner.com/wp-content/uploads/2011/05/excelArray-immediate1.jpg&h=180&w=320&zc=1″ href=”http://www.davetyner.com/wp-content/uploads/2011/05/excelArray-immediate1.jpg”]
Immediate Window
[/frame_right]

This works great for just collecting objects into an array. As you can see from your immediate window in the VBA editor (view > immediate)




Tomorrow, I will show you how to capture the row that the item is stored on and do something with it.

edit: Check out part 2!
Hope this helps someone. 🙂

Tags: , , ,

About the Author

About the Author: Hi! I am currently an Autodesk Solutions Engineer working to deliver real time solutions to our Enterprise customers. I have been involved with industrial design software for nearly 15 years and am a 3DS Max Professional who is passionate about design visualization. .

Subscribe

If you enjoyed this article, subscribe now to receive more just like it.

There Are 4 Brilliant Comments

Trackback URL | Comments RSS Feed

  1. Yo-Mama says:

    I don’t know whether to be happy or upset!!!! I’m only 15 and I like burgers unlike DW’s intellectual appetite? I’ll let you know which emotion I decide to embrace after viewing this. All kidding aside good post. I’m not much of a programmer but would love to get into it and these posts will be a great feather in my starter cap.

    • raul says:

      hello, thanks in advance for the info.

      I am new to macros and arrays that I find very useful.
      I’m trying to extract data from an array, this based on a criterion or more … eg

      I want to get the name and favorite food for people under 30. (Note that the new array does not contain all the columns of the original matrix)

      I know this can be done with a crossing loop row by row, but when you work with a lot of data that would be too slow.

      How could I do to make only those data from the original array (according to criteria that can be written in a cell), create a new array and write it to another sheet.

  2. dave says:

    raul, thanks for your comment. Check out this post. I posted a modified routine from one of our users 2nd post from the bottom. http://www.davetyner.com/forum/showthread.php?11688-Data-Import-Error-Finding-Number-of-Rows

    Let us know if that doesn’t help you.

  3. nidhi says:

    hey thanks for this..bt have a prob in getting the graph from an array..and even if u can pls help me how to transfer data from one form to other..i am working on a project in vb..

Post a Comment

Your email address will not be published. Required fields are marked *

Top