Questionnaires and Aggregation in Excel

Overview of VBA

Modules, worksheet, userforms, UDFs

Modules are essentially where the VBA code (or script) is stored and edited. The VB Editor is accessible in any Microsoft Office product by pressing ALT+F11 together.

A script in a module (procedure) will always start with Private_Sub or Sub followed by the unique name which should not contain any spaces and end with End Sub.

Using an apostrophe will comment out / ignore any writing or code on that line and is useful when you are coding and want a description or commentary or if you want to turn off certain sections of your code. For instance:

Sub Commenting_Out

'Nothing on this row will be considered by Excel when running this macro
Msgbox "Just a message"
'This row will also be ignored….

End Sub

UDFs are User Defined Functions and there are plenty of these available on the internet through a Google search or here on the Datanology website. They allow additional formulas to be used in a worksheet or in VBA codes – These won’t be required for this session.

Userforms are powerful and can be used for fully functioning CRM databases, information management systems, login screens etc.

Open the VBA Editor using ALT+F11 or Right clicking the worksheet tab and choosing ‘View code’.

2 different sets of VBA actions can be set using a module or the ‘This Workbook’ section. Things that are associated with the workbook opening, closing or a trigger event on change in a worksheet is controlled best within the ‘This Workbook’ part.

Sub Button10_Click()
Application.ScreenUpdating = False
'Hiding and unhiding rows
Worksheets("Sheet1").Rows.Hidden = False

Worksheets("Sheet1").Rows("7:10").Hidden = True
End Sub

Sub Button11_Click()
Application.ScreenUpdating = False
'Hiding and unhiding rows
Worksheets("Sheet1").Rows.Hidden = False

'Worksheets("Sheet1").Rows("6:6").Hidden = True
Worksheets("Sheet1").Rows("8:10").Hidden = True
End Sub

Sub Button12_Click()
'Triggering an event on the selected cell (activecell)
ActiveCell.EntireRow.Hidden = True
End Sub

Sub Add_Director()
If Worksheets("Sheet2").Range("A3").EntireRow.Hidden = True Then
Worksheets("Sheet2").Range("A3").EntireRow.Hidden = False
Exit Sub
End If

If Worksheets("Sheet2").Range("A4").EntireRow.Hidden = True Then
Worksheets("Sheet2").Range("A4").EntireRow.Hidden = False
Exit Sub
End If
End Sub

Sub colours()
For i = 1 To 28
If Worksheets("Sheet2").Range("D" & i).Interior.Color = 65535 Then
MsgBox "D" & i
End If
Next i

Activesheet.unprotect Password:"XXXX"
Activesheet.protect Password:"XXXX"


Leave a Reply

Your email address will not be published. Required fields are marked *