IP 244 VBA

.xlsm

• Save as .xlsm

• Otherwise VBA code will be deleted

Creating module & comments

• Insert > Module

• Use ‘ at start of line to make entire line comment

Sub

• eg. Sub worksheet_VBA()

• Block of code that doesn’t return value.

Sheet naming

• Excel format: Sheet1, Sheet2, ect. (use this referencing)

• When renamed: Sheets(“Sheet name”)

• Excel format stays constant but if renamed format used & a sheet is renamed then VBA code needs to be updated

Cells & Ranges

• eg. Sheet1.Cells(2, 1).Value = 20

  • row, column

• eg. Sheet1.Range(“B4:C12”).Clear

Active sheet

• Code executed on current active sheet AKA sheet currently open in excel

Worksheet functions

• Excel functions in VBA

• eg. Sheet3.Range(“F2”).Value = WorksheetFunction.Average(Sheet3.Range(“A2:A200”))

Generate random variable from normal distribution

• Sheet3.Range("G2").Value = WorksheetFunction.NormInv(Rnd, 5, 1.5)

• Within min&max: Sheet3.Range("H2").Value = WorksheetFunction.RandBetween(Sheet3.Range("E2").Value, Sheet3.Range("F2").Value)

• NormInv(probability; mean; standard deviation) - normal distribution inverse

Count If

• Sheet3.Range("D2").Value = WorksheetFunction.CountIf(Sheet3.Range("A2:A200"), ">5")

Interior colour

• Sheet3.Range("C2:H2").Cells.Interior.Color = vbYellow

Declaring variables

• Dim variable_name As Double

• double, integer,

Assign value, cell or user input to variable

• variable_name = 7.5

• Sheet1.Range(“D7”) = variable_name

• variable_name = Sheet1.Range(“D7”)

Function condition specified by variable

• Sheet3.Range("D2").Value = WorksheetFunction.CountIf(Sheet3.Range("A2:A200"), ">" & n_temp_threshold)