PDA

View Full Version : Solved: Terminology Stuff



JohnnyBravo
07-06-2006, 08:19 AM
I'm just beginning to learn VBA so bear with me.

What is a "variable" and what is an "array". Now some of you have a programming background so please explain this in terms a newbie would understand. I run across these terms so often on the internet that I really need to get a solid grasp of what they mean.

http://www.informit.com/articles/article.asp?p=170285&rl=1

Just by the name alone "variable" I can only guess that it's a piece of data (number/ text) that's going to vary - but beyond that I have no clue.

OBP
07-06-2006, 08:54 AM
A Variable is a name (a memory box) that can be used to store data, it comes from the old BASIC language and was used to allocate memory to store the data. It is volatile, i.e. when you close an Access form or Excel workbook or even close a Procedure in which it is used the data is reset to blank or "Null".
There are lot's of different types of variables depending on what you want to store in them. The most common are string (text) and numeric.
They should be used with a "Dim" statement which tells the application what type of variable to expect (again from BASIC).
An array is a special variable that can contain more than one value and can also have more than one dimension.
So think of an array as a row of boxes that will contain data, a 2 dimension array will be a row of boxes with boxes stacked on top of them.
A three dimensioned array will stacked boxes more than one row deep.
I hope that gives you some idea.

lucas
07-06-2006, 08:58 AM
This is a simple example of an array defined as a Variant. As per obp's explaination you can see that the array is placed in the A1-E1 cells according to their order in the array.

Sub Sheet_Fill_Column_Headings()
Dim myarray As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")
Range("a1:e1").Value = myarray
End Sub

compariniaa
07-06-2006, 09:23 AM
An excellent book for newbies is VBA programming for dummies by john walkenbach

"dim" is short for dimension and, as OBP said, it sets aside memory for data that you will define
the "dim" statement is unneccessary if your code does not say "option explicit" before any subs. option explicit just means you have to use the "dim" statement. however, it's usually a good idea to use "dim" because if you don't specify what type of data your variable is, excel will have to figure it out, which will slow down your code

JohnnyBravo
07-06-2006, 09:37 AM
Nice. First I get a definition followed up by a concrete example. Kewwwwl. Thanks guys! :)

Lucas, just for kicks, I tried this:

Sub Sheet_Fill_Column_Headings()
Dim myarray As Variant
Dim myarray2 As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")
Range("a1:e1").Value = myarray
myarray2 = Array("John", "David", "Lisa", "Nancy", "Bill")
Range("a2:e2").Value = myarray2
End Sub


BTW, when i started to type that very last line, the autofill feature in VBE started to show .Value & .Value2. What's the difference between the two?

JohnnyBravo
07-06-2006, 09:43 AM
Also when I try to populate the names vertically (within column A) it doesn't work.

Range("a2:a6").Value = myarray2

lucas
07-06-2006, 09:44 AM
Glad I could supply you with one that you could follow easily. Not all things will dimmed as a variant as others have pointed out and it is recommened that you use Option explicit in your coding and is a requirement for contributions to the knowledgebase.

If your variable is going to be used as letters dim it as string
If as a number dim as integer, etc.

lucas
07-06-2006, 10:01 AM
You have to transpose the data to use it in a column:

Sub Sheet_Fill_Column_Headings()
Dim myarray As Variant
Dim myarray2 As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")
Range("a1:e1").Value = myarray
myarray2 = Array("John", "David", "Lisa", "Nancy", "Bill")
Range("a2:a6").Value = Application.WorksheetFunction.Transpose(myarray2)
End Sub