VBA variable Part 1

Variable Definition

Declaring and using variables is one of the most important aspect of any programming language.

In Excel VBA or any programming language, variables temporarily store a piece of information or a value on RAM for further process and usage.

How to declare variables in Excel VBA

By two ways one can write VBA variables.

1^st way  – In this we declare a variable name and immediately assign some value like below

	
Sub displayMsgBox()
  msgStr = "My name is Jack"
  MsgBox msgStr
End Sub

In the above example msgStr is variable name , this variable has been assigned value of “My name is Jack”.

But one should not use above method as it is very shorthand and not considered good practice.

2^nd way – In this method we first declare variable using Dim (Dimension) keyword with respective data type.

After declaring variable we assign value to it.

	

Sub displayMsgBox()
'variable declaration
 Dim msgStr  As String
'assigning value to variable
  msgStr = "My name is Jack"
  MsgBox msgStr
End Sub
	

In the above example msgStr  is variable name whose data type is String. One must prefer 2^nd  method for declaring variables in VBA.

You can change a variable value at any time as per your requirement but const (constant) type variable is an exception to this.

	

Sub test2()
 Dim var1 As String
 var1 = "abc"
 MsgBox var1
 var1 = "xyz"
 MsgBox var1
End Sub
	

Rules for writing variable name in VBA

• Number of characters must be less 255
• Must not contain space
  Example:- Msg Str not allowed
  Instead write MsgStr Or Msg_Str
• Must not begin with a number
  Example:- 1stName not allowed
  Instead write firstName or First_Name
• Dot or period is not allowed
  First.Name not allowed
• Must not be reserved keywords
  Example:- If, Else, Case, Sub, Function ETC. as a variable name not allowed.

Variable name writing conventions

These conventions are only for code readability and uniformity, they do not affect code execution if not implemented.

• Constant variable name should in Upper case
  Const MYNAME
• To differentiate between two or more words either write variable in
  Camel case Example:- myName or MyName

  OR

  Separate words by an underscore
  Example:-My_Name or my_name

• Use descriptive variable name which have some meaning.This will increase code readability.Dim x As RangeDim y As String, var1 as stringThe above x , y, var1 variable  names are very cryptic and not giving idea of what they represent at the very first place.Dim copyRng As Range
  Dim msgStr As String

  In the above example copyRng name is more descriptive suggesting a range to be copied and msgStr indicates string for the message to display.

Declaring VBA variables in a single line and in multiple lines

In single line

In Multiple lines

Note:- When type is not defined then data type is variant.

Dim x, y, z As String

In above declaration x and y are variant data type and z is string.

Dim x, y, z As String and Dim x As String, y As String, z As String both lines are different.

Dim x, y, z As String can be alternative written as

Dim x as Variant, y as Variant, z As String here x and y data type is explicitly (in written) declared.

Placement of variables declaration in Sub or Function

The question arises whether to declare all the variables at the very start or at its nearest first use.

There is no harm in declaring all the variables at the very start. But when you have Sub or Function having 100 or more lines and one have to use scroll to see below codes then declaring a variable at its nearest first use is good practice with respect to readability.

Excel VBA data types

Numeric Data Types

Non-numeric Data Types

Good practices in assigning VBA data types

• Always assign data type explicitly. Here explicit means write down data type do not leave on guessing or default
  Dim copyRng not considered good instead write Dim copyRng As Range or Dim copyRng As Variable
• Assign lowest data type whenever possible
  Dim monthNum As Integer is not good.
  As we know month number range is between 1 to 12, so integer with max value 32767 will only consume unnecessary more memory and will make programme
  Less efficient. It will take 2 bytes of memory.

  Dim monthNum As Byte is optimal data type
  Byte will consume only 1 byte of memory and will cover all the values of month number.

• Variable name must be descriptive not cryptic
  Dim x as Range is not good
  Dim copyRng as Range is good

Note:- If a variable is not assigned to some value then it will take default value of its respective data type. For numeric data type like Byte, Integer, Double, Single, Long default value is zero and for string data type default value is null string (“”).

If you run above sub then you will get no string and 0 in message box because no value is assigned to these two variable hence they auto get there default value.

Variable data type in VBA

In variable data type VBA IDE internally performs some test on a value then determines its type. One can say its is just in time determination of data type.

	

Sub test2()
  Dim var1 As Variant
  var1 = "apple"
  Range("A1").Value = var1
  var1 = 1
  Range("A2").Value = var1
End Sub
	

If you run test2 sub then no error is going to produce due to variant data type flexibility in adjusting data type as per value.

Sub test3()
 Dim var1 As Byte
 var1 = "apple"
 Range("A1").Value = var1
 var1 = 1
 Range("A2").Value = var1
End Sub

But test3 sub will throw error Type Mismatch since var1 is Byte data type (1 to 255) and it has assigned string value.

So, from these two cases one may say Variant is good , a big No, since Variant data type will consume 16 to 22 bytes to memory, thus making programme less efficient for memory and makes a programme prone to wrong inputs. But there are cases where you have no choice for Variant data type. So must have strong reason for assigning variable data type.