Excel 2/3

13 Dec 2021

You are limited only by your imagination. If you can describe it, you can program it.

To add a Macro, Create a module in the VBA window. Insert -> Module

You can select this module and change its name in the properties window.

Protecting your VBA Codes
1. Open VBA editor
2. Tools -> VBAProject Properties
3. Go to ‘Protection’ tab
4. Check the box ‘Lock project for viewing’ and set a password
Objects Methods and Properties

Objects and Methods

Everything in Excel is an Object. It is like a class equivalent in any OOPS.
- A Method is an action or something to do with the Object.
  
  Example: Banana.Eat Here Banana is the Object and Eat is the Method
- A Method may have many Parameters. Between Method and Parameter there is always a space.
- After specifying a parameter, there is always a := sign. Indicating that you are working with a method parameter. After this sign, appears the value of the parameter.
  
  Example: Banana.Eat EatingStyle:=PeelAndMunch Here Banana is the Object, and Eat is the Method,
  
  EatingStyle is the parameter and PeelAndMunch is the value of the parameter
Objects and Properties

Objects also have properties just like it has got methods.
- A Property is specified just like calling a Method of an Object, but here, = sign is used to assign the value of the property.
  
  Example: Banana.Color = Yellow Here Banana is the Object and Color is the Property and Yellow is the value assigned to the Color property.
  
  A property can have many other properties which can be called by a . (dot) operator.
  
  Example: Range("A5").Font.Bold = True
Hierarchy

Excel -> Worksheet -> Cells

Example: Workbooks("Book1.xlsx").Sheets("Sheet1").Range("A5")
Range Object

Example: Range("A5:A14").Select, Range("A5, B7, C9").Select , Range("A5:B5, A7:C7,A9:C10").Select
Whatever is selected gets assigned to the object Selection
Cells Object

Example: Cells(5, 1).Select, Range(Cells(5, 1), Cells(14, 1)).Select
ActiveCell Property

Whatever cell is selected at present is referred to as ActiveCell

Example: ActiveCell.Font.Bold = True
Offset Property

Offsets by some row and column.

Example: Range("A5").Offset(1, 0).Select will select cell A6. Range("A5").Offset(-1, 0).Select will select cell A4
End Property

Does the job of Ctrl+Left_arrow, Ctrl+Right_arrow, Ctrl+Up_arrow, Ctrl+Down_arrow

Example: Range("A5").End(xlToRight).Select, Range("A5").Select Selection.End(xlToRight).Select, ActiveCell.End(xlToDown).Select, ActiveCell.End(xlUp).Select, ActiveCell.End(xlDown).Select
CurrentRegion Property

CurrentRegion Property of an object selects anything which is bordered by blank cells.
ActiveCell is different from Select cell. There can be many selected cells, but there will always be one and only one active cell.
ActiveSheet Property

Just like ActiveCell

Example: ActiveSheet.Next.Select, ActiveSheet.Previous.Select
Calling a sheet by VB Name

What if some user renames a sheet. The reference would be lost. Therefore after creating a sheet, open macro and give a VB name to the sheet. This name is different from what the user gives.

Example, In the VB editor window, the sheet will be shown by the name fruits (MySecondSheet). Here fruits is the user given name and MySecondSheet is the name given by the developer of the full Excel Workbook and Macros. This name in parenthesis won,t change unless the sheet gets deleted.

MySecondSheet.Select
Sheets Vs. Worksheets

Sheets will recognize any kind of sheet in excel be it chart sheet or worksheet or anything, whereas Worksheets will recognize regular excel sheets. They are the sheets that you can see down the bottom.
Activating a Workbook

ThisWorkbook.Activate, Workbooks("Jeet first book").Activate, Workbooks(1).Activate

A sheet of a workbook can only be selected only after the workbook is Activated.
Value Property and Writing data

Example: Range("A1").Value = "Hello World", Cells(13, 1).Value = "Hello World", Range("A1:G11").Value = "Hello World"
Reading data to a variable

Example: MyFirstVariable = ActiveCell.Value

For multiline code, use ` ` *i.e* <space> then move on to the next line.

Example:

Workbooks("Book1.xlsx"). _
	Sheets("Sheet2"). _
		Range("A1").Value = "Hello World!"

Copy And Paste

Syntax: <Source Range> <Destination Range>

Example: Range("A1:A3").Copy Range("B1")
Commonly used properties:

Font.Bold (“True”/”False”) , Font.FontStyle (“Bold”/”Regular”/”Bold italic”)

Interior.Color (“vbRed”)

Row

Column
Address Property:

Returns addresses in their absolute cell reference.

MyVeryLastRowAddress = Range("A1048576").End(xlUp).Address

MyFirstBlankAddress = Range("A1048576").End(xlUp).Offset(1, 0).Address
Column the Column Letter

MyColumnLetter = Split(ActiveCell.Address, "$")(1)

MyRowNumber = Split(ActiveCell.Address, "$")(2)
More useful properties:

Get the Windows User Name: MyUserName = Environ$("Username")

Get the active workbook name: MyActiveWorkBookName = ActiveWorkbook.Name Active workbook may not be the one where the code resides.

Get this workbook name (Not necessarily the Acive workbook): ThisWorkbook.Name This workbook is the one where the code resides.

Get the path to the active workbook: ActiveWorkbook.Path

Get the path to the this workbook: ThisWorkbook.Path

Get the full path to the active workbook and append the name at the end of it. MyFullName = ActiveWorkbook.Fullname

Get the full path to the this workbook and append the name at the end of it. MyFullName = ThisWorkbook.Fullname

Get the current sheet name ActiveSheet.Name

Get the active cell address ActiveCell.Address Get various properties of the active workbook

MyVariousAttributes = ActiveWorkbook.BuiltinDocumentProperties("Application Name")

MyVariousAttributes = ActiveWorkbook.BuiltinDocumentProperties("Author")

MyVariousAttributes = ActiveWorkbook.BuiltinDocumentProperties("Company")

MyVariousAttributes = ActiveWorkbook.BuiltinDocumentProperties("Creation Date")

MyVariousAttributes = ActiveWorkbook.BuiltinDocumentProperties("Last Author")

MyVariousAttributes = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")

Get count of number of sheets in the workbook ActiveWorkbook.Sheets.Count

Get the sheet index number of the current sheet ActiveSheet.Index

Get count of the number of open workbooks Application.Workbooks.Count
Opening a Workbook in various modes:

Opening

Workbooks.Open Filename:="C:\Users\Jeet\Document\Book1.xlsx"

Opeing as readonly:

Workbooks.Open Filename:="C:\Users\Jeet\Document\Book1.xlsx", Readonly:=True

Opeing a password protected file:

Workbooks.Open Filename:="C:\Users\Jeet\Document\Book1.xlsx", Password:="ThisIsPassword"
Closing a Workbook:

Closing ActiveWorkbook.Close

Closing by index Workbooks(1).Close

Close the activeworkbook and save changes ActiveWorkbook.Close savechanges:=True

Close the activeworkbook and without saving changes ActiveWorkbook.Close savechanges:=False

Coding Toolbox

Variables

Dim is used to define local variable (Inside a Sub or a Module). Dim strMyString As String

Public is used to define project level variable. Public strMyString As String

Project level variables are declared in the “General Declaration Area” , declaration statement : Public, Scope: Available to all Modules and Subs in the project

Call a subroutine stored in a different Workbook: Application.Run("'My Other Workbook.xlsx'!Module1.RunMe")

Forcing Variable Declaration: Option Explicit at the very top of all codes

This site has a good description on the different kind and declaration of VBA variables

If then else

If Condition1 Then
  'Runs if Condition1 is True
ElseIf Condition2 Then
  'Runs if Condition2 is True
'.....
Else 
  'Runs if neither Conditions above are True
End if

Boolean Operator: AND, OR, NOT

Logical Operators: =, <>, < , >, <=, >=

One-line IIF: maxOfTwo = Iif(val1 > val2, True, False)

For Next Loop

For Counter = Start To End [ Step StepIncrement ]
   '...Code here...
Next [ Counter ]

Example:

Dim i as Long
 
For i = 5 To 1 Step -3
   Debug.Print i
Next i
'Result: 5,2

For Each Loop (very slow than For loop)

Dim x(4) as Long, xIterator as Variant
x(0) = 0: x(1) = 1: x(2) = 2: x(3) = 3
 
For Each xIterator in x
   Debug.Print x
Next xIterator
'Result: 0,1,2,3

While Wend

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
While i < 12
   MsgBox i
   i = i + 1
Wend

Do While

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
Do 
   MsgBox i
   i = i + 1
Loop While i < 12

OR,

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
Do While i < 12 
   MsgBox i
   i = i + 1
Loop 

Do Until

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
Do 
   MsgBox i
   i = i + 1
Loop Until i < 12

OR,

i = 0
'Will display 0,1,2,3,4,5,6,7,8,9,10,11
Do Until i < 12 
   MsgBox i
   i = i + 1
Loop 

Exit Do While/Do Until: Exit Do

Select Case

Select Case MyMoney
Case Is < 20
    'Do something
Case Is <= 30
    'Do something
End Select

Writing Formulas

Absolute Reference: ActiveCell.Formula = "65-G2" OR, ActiveCell.FormulaR1C1 = "65-R2C7"

Relative Reference: ActiveCell.FormulaR1C1 = "65-RC[-1]" Means, whatever is the active cell, RC[-1] will be on the same row of that active cell, but one column left of the active cell.

For Range, this do not hold.

The .Formula is theoretically less efficient than .FormulaR1C1 style but for most purposes it is indistinguishable.

To use Excel’s builtin functions you have to preceed it with Application.WorksheetFunction:

Example: Application.WorksheetFunction.VLookup(...)

Getting User-data using InputBox Function

Sub InputBox()
    Dim a as String
    Dim b as String
    a = InputBox("Enter first name", "Name Window Title - first")
    b = InputBox("Enter last name", "Name Window Title - second")
    FullString = a & b
    Range("A1").Value = FullString
End Sub

Getting User-data using InputBox Method

This allows the user to click on a cell in Excel so that you get the cell address.

InputBox Method has different types specified by Type

Sub InputBox
    Dim a as Range
    set res = Application.InputBox("Click a cell", "Select Cell", Type:=8)
    MyAddress = res.Address
End Sub

Message Box

MsgBox Range("A1").Value, vbOKOnly, "Hello is the Window Title"

Array

To make array indexing start from 1, add Option Base 1 at the top of the Module

Static Arrays: Before I forget the cons about static array is that even if you erase the data, a static array will still hold on to that memory.

Dim MyArrayA(8) As String , Dim MyArrayA(8, 4) As String

Accessing Array elemements: MyArrayA(1), or MyArrayA(2), or MyArrayA(5)

Dynamic Arrays:

To declare a dynamic array, Dim MyDynArray() As String

To assign the size of array later, ReDim MyDynArray(sizeRow, sizeColumn) where sizeRow and sizeColumn are the number that you assign as size.

Destroy a dynamic array with Erase MyDynArray

Read a whole bunch of data directly at one-shot:

Dim MyArrayA() As Variant
MyArrayA = Range("A6").CurrentRegion

Length of array:

RowCount = UBound(MyArrayA)
ColCount = UBound(MyArrayA, 2)

Write a whole bunch of data directly to a range at one-shot:

Range(Cells(1, 1), Cells(RowCount, ColCount)) = MyArrayA

GoTo

GoTo statement can be used to go to a certain checkpoint marked by a unique Name.

Example:

checkPointName:
	...
	...
if sum = 0 Then GoTo checkPointName

Using Array as a Data Source

Let’s say you want to VLOOKUP a value from an array MyArrayA. The syntax is very similar

MyName = Application.WorksheetFunction.VLookup(value, MyArrayA, 2, False)

Good Resources

https://analystcave.com/vba-cheat-sheet/

Jeet Majumdar

jeet.Log

Excel 2/3

Coding Toolbox