Working with variables

To have better understanding in variables lets take look below example:
Sample without Variable:

Option Explicit
Sub example1()
  MsgBox(“Hisyam”)
End sub

Sample with Variable:

Option Explicit
Dim myName as String
  myName = “Hisyam”
  MsgBox(myName)
End sub

Open your Excel then VBE and insert module, copy paste the above code into this module and try to run both example1 and 2. There is no different in between because end result is the same. The different example1 we use constants and example2 we use variable to declare our name with myName. If you notice when we use constants we need to use quote before and after but not for variables. Use must remember this rule before get started because variables is more effective in repeating process like looping and for next. 

Constant = MsgBox(“Hisyam”) with quote before and after. 

Variable = MsgBox(myName) without quote.

Click here if you have any new project! Let us create for u for free. 

Thanks Good luck

How to declare variables in VBA?

Basically same with Visual Basic when declaring variables, you usually use a Dim statement. A declaration statement can be placed within a procedure to create a procedure-level variable. Or it may be placed at the top of a module, in the Declarations section, to create a module-level variable. Another method is to use Public statement to make this variable available to all procedures in the project. For Examples: Dim x as Integer (Commonly use) Public x as Integer x is our variables and the data range is in between -32,768 to 32,767 (Integer) which is reserve by computer to store x. Meaning if your data is more or less when you run this macro your computer either ignore or error message to trigger value is out of range. To have clearer picture lets try below example:

Option Explicit
Sub CalculateAndB()

  Dim ColA as Integer
  Dim ColB as Integer
  Dim Result as Integer
  ColA = Range(“A1”).Value
  ColB = Range(“B1”).Value
  Result = ColA*ColB ‘Multiply value of Range A1 and B1
  Msgbox (Result)

End Sub

Now copy the above code into your module and try. 1. Input 8 into Range A1 and 100 into Range B1 and Run the macro Your message box will prompt 800 (No problem because still within integer range) 2. Input 80 into Range A1 and 1000 into Range B1 and Run the macro You will receive error message “Run time error 6 Overflow”. This is because after multiply result will be 80,000 (Out of integer range) From the above example you should declare “Result” as Long instead of integer because Long can accept data range in between -2,147,483,648 to 2,147,483,648 Repeat the above testing but this time changed Dim Result as Integer ---à Dim Result as Long Sometimes we tend to forgot to declare if we have too many variables. As a reminder you can use “Option Explicit” statement above Sub or 1st line in a module. This will force you declare. Click here if you have any new project! Let us create for u for free. Thanks