Excel VBA Basic Training

Excel VBA Basic Training

Variables

Variables are used to store values that are either derived from a calculation or pulled in from the excel data.  They can be used interchangably just like variables in an equation. If you continually need to reference a certain cell in your macro you can save that cell as a variable and then use the variable instead of having to reference the cell directly.

Referencing Example:
Below you are referencing Cell A2 in References tab multiple times. Instead of writing Sheets(“References”).Range(“A2”).Value out everytime you want to reference that cell you can use a variable.

‘No Reference Variable’

If Range("A2").Value>10 and Range("A3").Value<100 THEN
Range("A4").Value = Range("A2").Value * Range("A3").Value
End IF

dim varx, vary as long
varx = Range("A2").Value
vary = Range("A2").Value
If varx>10 and varY <100 THEN
varz = varx * vary
End IF

Calculation Example:
Or maybe you need to calculate a value and use that calculated field in a formula

Dim quantity, price, netvalue, fee, settlementvalue As Long
quantity = Range("A2").Value
price = Range("B2").Value
netvalue = quantity*price
fee = netvalue*0.0002
settlementvalue = netvalue - fee
Range("C2").value = settlementvalue

Data Type or SubtypeRequired MemoryDefault ValueVBA ConstantRange
Integer2 bytes0vbInteger–32,768 to 32,767
Long Integer4 bytes0vbLong–2,147,483,648 to 2,147,486,647
Single4 bytes0vbSingle–3402823E38 to –1.401298E–45 or 1.401298E–45 to 3.402823E38
Double8 bytes0vbDouble–1.79769313486232E308 to –4.94065645841247E–324 or 1.79769313486232E308 to 4.94065645841247E–324
Currency8 bytes0vbCurrency–922,337,203,477.5808 to 922,337,203,685,477.5807
Date8 bytes00:00:00vbDateJanuary 1, 100 to December 31, 9999
Fixed StringString’s lengthNumber of spaces to accommodate stringvbString1 to 65,400 characters
Variable String10 bytes plus the number of charactersZero- length string (“”)vbString0 to 2 billion characters
Object4 bytesNothing (vbNothing)vbObjectAny Access object, ActiveX component or Class object
Boolean2 bytesFalsevbBoolean–1 or 0
Variant16 bytesEmpty (vbEmpty)vbVariantSame as Double
Decimal14 bytes0vbDecimal-79,228,162,514,264,337,593,543,950,335 to 79,228,162,514,264,337,593,543,950,335 or –7.2998162514264337593543950335 to 7.9228162514264337593543950335
Byte1 byte0vbByte0 to 255

[efb_likebox fanpage_url="https://www.facebook.com/vbamarket/" box_width="840" box_height=340"" responsive="1" show_faces="1" show_stream="1" hide_cover="1" small_header="0" hide_cta="0" locale="en_US"]

Add comment