VBA background and how we access our worksheet cells?

This page will capture the background, go through the object based structure of this

programming language and teach you how to access worksheets, and cells in your excel workbook.

What is VBA and how can we use it?

Vba stands for visual basic for applications and is the language in excel that allows you to program repetitive tasks for automation. Since it is the included language in excel, it makes it very practical as it is on every single work computer. This means that you can bring your skills to any workplace as you don't need to convince them to buy a special program for you. This also means that it is quite often the starter language for many people that didn't study programming in school.

Whilst sometimes controversial, it is very practical to use, and it serves as a great foundation for any further development in programming as most programming languages are Object based, and you normally run IF statements or Loops of some sort.

VBA's object based structure?

VBA is built on an Object based hierarchy like many other languages.

The top level object is called "Application".

For accessing different cells in different workbooks and different worksheets we need to specify the workbook and worksheet we want to write in. If we don't specify workbook and worksheet, it will write in the current workbook and current worksheet. Most of the cases this is what we want, but sometimes we may want to write or collect data from different sheets, and possibly even other workbooks. For this reason, it is good to know about the object based set up.

'Two different examples. One calls the worksheet by number and the other one by name on the tab.
Application.Workbooks("CurrentWorkbook").Worksheets(1).Range("A1").Value = "Hello"
Application.Workbooks("CurrentWorkbook").Worksheets("MySheetName").Range("A1").Value = "Hello"

'Worksheets are often shortened to just Sheets. Sheets contain both chart-sheets and work-sheets. 
Application.Workbooks("CurrentWorkbook").Sheets("MySheetName").Range("A1").Value = "Hello"

'If you are already writing your code in your open workbook you can simply skip the workbook specification and just do the following. 
Sheets("MySheetName").Range("A1").Value = "Hello"

'My recommendation is however to give you worksheet a codename instead of referring to the tab name which could easily be changed in the bottom left properties window once you open your VBE (where you write your code).
MySheetName.Range("A1").Value = "Hello"

Hierarchy:

• Application.

• Workbooks.

• Worksheets or Sheets.

• Range or Cells.

Accessing Ranges or Cells to write our data?

Below you will find the code to access different ranges or cells in one line. You can also write more lines for each cell that you want to add data or text into. Later on however, you will find the below syntax neat as you can print data to multiple cells at once. Should something change in your sheet, it will be easier to change one line. Primarily you can access cells with the Range syntax or the Cells syntax. you will find that you will vary these two depending on your needs.

'Entire Sheet.
Cells.Select
​
'Currently selected cells syntax. This syntax is not really needed, but is used heavily in the macro recorder function.
Selection
​
'First column
Range("A:A").Select
​
'First row
Range("1:1").Select
​
'Several columns
Range("A:A,D:D,G:G").Select
​
'Several rows
Range("1:1,3:3,5:5").Select
​
'Type data into one cell.
'Print "Hello" into cell A1
Range("A1") = "Hello"
​
'The one indicates Row,Column number.
'Cells(Row,Column)
Cells(1, 1) = "Hello"

'Type data into multiple adjacent cells
'Print "Hello" into cells A1:C3
Range("A1:C3") = "Hello"
Range(Cells(1, 1), Cells(3, 3)) = "Hello"

'Type data into single non-adjacent cells
'Print "Hello" into cell A1 and C3
Range("A1,C3") = "Hello"
Application.Union(Range(Cells(1, 1), Cells(1, 1)), Range(Cells(3, 3), Cells(3, 3))) = "Hello"

'Type data into multiple non-adjacent cells
'Print "Hello" into cells A1:C3 as well as cells D4:F6
Range("A1:C3, D4:F6") = "Hello"
Application.Union(Range(Cells(1, 1), Cells(3, 3)), Range(Cells(4, 4), Cells(6, 6))) = "Hello"
​
'Select downward range from cell A1
Range("A1", Range("A1").End(xlDown)).Select

'Select upward range from cell C6
Range("C6", Range("C6").End(xlUp)).Select

'Select range to the right from cell A1
Range("A1", Range("A1").End(xlToRight)).Select

'Select range to the left from cell C6
Range("C6", Range("C6").End(xlToLeft)).Select