This article describes the basic component of excel which VBA handles through scripting.
A VBA Programmer can be better if has good knowledge of excel which helps to use inbuilt functionality and leads to excellent control on excel.
VBA – Active commands on objects
“Active” it refers the current state.
Example – If user working in workbook (like Mywork.xlsx) and his selection of cell in worksheets (“sheet1”) and cell “a2”. So VBA reads it
Selection of windows = Windows (“Mywork.xlsx”).activate
Active Workbook = Myworkbook.xlsx
Active Sheet = Sheet1
Active Cell = A2
Range – can be single cell or combination of multiple cells. On range certain operation can be done like formatting, manipulation, copy and paste etc.
Multiple ways to select range in excel
Single cell selection = Range(“a1”).select
Single cell selection = Cells(1,1).select
Multiple cells selection = Range (“a1: e5”). selects
Whole worksheet’s cells selection = Cells.select
A complete Single Row selection = Rows(“1:1”).select
Multiple rows selection = Rows(“1:20”).select
Single column selection = Columns(“A:A”).select
Multiple Columns selection = Columns(“E:E”).select
Selection different rows (with gaps) = Range(“A1,A5,A10”).EntireRow.Select
Selection different columns (with gaps) = Range(“A1,E1,H1”). EntireColumn.Select
Copy below codes and try (paste in module) with F8 Key
Range(“A1, E1, H1”).EntireColumn.Select
Sheets – VBA can target to a sheet by multiple method.
Select sheet sequentially = Sheets(1).select
Sheet selection by name = Sheets(“Sheet1”).select
Use property of sheet (click on properties) = Sheet1.select