Macros in Excel : Learn Excel VBA : Variables

This is continuation of my earlier posts on

   Macros in Excel : Learn Excel VBA : Objects 
   Macros in Excel : Learn Excel VBA : Methods
   Macros in Excel : Learn Excel VBA : Properties

It is not necessary to declare a variable in VBA, Visual Basic automatically creates storage for a variable on first use in the code. Automatically created variables are of type variant and can contain any type of data. It can be strings, boolean values, object , arrays or numbers.

Simple statement such as given below creates a variable and assigns value to it

 Myvariable = 28 

Similarly you can use different variables in calculation to create another variable for your VBA code. Following code will explain the use of Variables in Excel VBA

Sub Explain_Variables()

Var_A = 10
Range("A4").Select ' Value of Cell A4 is 5
Var_B = ActiveCell

Var_Total = Var_A + Var_B

Range("A5") = Var_Total

End Sub

This code explains that

1. Var_A is declared as constant number in the code itself.
2. Var_B is declared as value in ActiveCell i.e. Cell A4 in this case with Value as number 5.
3. We have used Var_A and Var_B to calculated Var_Total
4. We have used Var_Total to update the value in Cell A5, the result of this code is number 15 in Cell A5

Macros in Excel : Learn Excel VBA : Properties

This in continuation of my earlier post on

   Macros in Excel : Learn Excel VBA : Objects
   Macros in Excel : Learn Excel VBA : Methods

Each VBA object has its own properties that controls its appearance. When we talk about range as object typical properties are

   - ColumnWidth
   - RowHeight
   - Font
   - Text
   - Value
   - Formula
   - Borders

Syntax in your macro is

    Object.Property = Value

or

    Range("A1").RowHeight = 60 

or

    Range("A1").FormulaR1C1 = "=R[2]C+R[3]C" 

Following instruction will set the columon width to 25

    Range("A1").ColumnWidth = 25 

In case property is on left hand side of = sign it is updated with the new value mentioned on the right hand side of = sign

In case you have property on the right hand side of = sign it means you are reading value from that object. Following statement will set the column width of Cell A2 as 35 considering that Cell A1 has column widht of 25

 Range("A2").ColumnWidth = Range("A1").ColumnWidth + 10 

If you are new to Macros in Excel, Please go through my earlier posts on

1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)

Macros in Excel : Learn Excel VBA : Methods

This in continution of my earlier post on Macros in Excel : Learn Excel VBA : Objects

Visual Basic is an object oriented language. Objects have methods that perform action on them. In case range is the object you are working upon following methods would perform action on range object

    - Activate
    - Select
    - Cut
    - Copy
    - Clear
    - Delete
    - Paste 

Syntex of many Visual Basis statements is

    Objects.Methods 

Look at the following code to understand the use of methods in VBA

Sub Explain_Methods()
    Range("C4").Copy
    Range("C8").Select
    ActiveSheet.Paste
   
End Sub 

If you are new to Macros in Excel I suggest that you go through earlier posts on Macros
1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)
3. Macros in Excel : Learn Excel VBA : Objects

Macros in Excel : Learn Excel VBA : Objects

It is important to understand Visual Basic Grammar to better understand the macros recorded by you. If you are new to Macros in excel I suggest that you go through earlier posts on Macros
1. Automating Tasks in Excel : Using Macros in Excel
2. Recording Excel Macros / Writing excel macros (VBA)


Visual Basic is object oriented language, it means all items in Excel are considered as objects. There are lots of them but following example will help us understand what an Object is.

   - WorkBook
   - WorkSheet
   - Range
   - Chart
   - Legend

In a Macro Range("A1") is an object. An object can contain other objects.
WorkBook is the larger object followed by WorkSheet and Range.

Workbooks("Macros Book.xls").Worksheets("Data").Range("A1")

Code above refers to Cell "A1" on worksheet "Data" in "Macros Book.xls" file. This is long description but can easily be shortened. The worksheet containing the cursor is called Activesheet, similarly workbook containing active sheet is called Activeworkbook

ActiveWorkbook.ActiveSheet.Range("A1")

There is no difference in the previous line and line above in your macro code.

In case you do not refere larger object, Excel Macro will use ActiveWorkBook and ActiveSheet. So if you write Range("A1") it means same if you have your cursor at Worksheet "Data" in "Macros Book.xls" file while running this code.

Table below will explain the use of various objects on Excel VBA

WorkBooks - Referes to all workbooks currently open in Excel
WorkBooks.Item(1) - Referes to first workbook
WorkBooks.(1) - Referes to first workbook
WorkBooks.(Macros Book.xls) - Referes to Macros Book.xls file open

Sheets - Referes to all sheets in workbook both chart sheets and worksheets
Sheets(1) - Referes First sheet on tab bar.
Sheets("Data") - Refer to Sheet called Data