Microsoft VBA Excel Course

Store Code Cleanly

Variables are used to store data that your VBA code can manipulate. Think of them as containers that hold information

which can be referenced and manipulated throughout your code.

Declaring Variables

Why declare variables?

Improves Code Readability: Declaring variables makes your code easier

to read and understand.

Error Checking: VBA can catch errors related to undeclared variables.

Memory Management: Helps manage memory usage efficiently.

How to declare variables?

Use the Dim statement:

Dim variableName As DataType

Example:

Dim total As Integer

Dim name As String

Determining Data Types

Choosing the right data type is crucial for efficient memory usage and performance. Here are some common data types:

Integer: Whole numbers.

Long: Larger whole numbers.

Double: Numbers with decimals.

String: Text.

Boolean: True or False.

Example:

Dim age As Integer

Dim salary As Double

Dim isActive As Boolean

Build Your Own Functions

Creating your own functions in Excel VBA can greatly enhance your productivity by allowing you to perform custom

calculations and operations. Let’s go through the process of creating User Defined Functions (UDFs) with multiple examples.

Writing Your Own User Defined Functions (UDFs)

A UDF is a custom function that you can create using VBA. These functions can be used in Excel just like built-in functions.

Basic Structure of a UDF

To create a UDF, you need to define a function using the Function keyword, followed by the function name, any arguments,

and the data type of the return value.

Function FunctionName(arguments) As DataType

' Function code

FunctionName = result

End Function

Example 1: Simple Addition Function

Function AddNumbers(x As Double, y As Double) As Double

AddNumbers = x + y

End Function

You can use this function in Excel by typing =AddNumbers(5, 10) in a cell, which will return 15.

Handle Errors Gracefully

Determining the Err Object

The Err object contains information about the error that occurred. Key properties include:

Err.Number: The error number.

Err.Description: A description of the error.

Err.Source: The name of the object or application that caused the error.

Example:

Sub ExampleErrObject()

On Error Resume Next

Dim x As Integer

x = 1 / 0

If Err.Number <> 0 Then

MsgBox 'Error ' & Err.Number & ': ' & Err.Description

Err.Clear ' Clear the error

End If

End Sub

Create Smarter Dialogues

Creating custom dialogue boxes with UserForms in Excel VBA can greatly enhance the interactivity of your spreadsheets.

Let’s go through each step with examples:

1. Drawing UserForms

To create a UserForm:

Open the Visual Basic for Applications (VBA) editor by pressing Alt + F11.

Insert a new UserForm by selecting Insert > UserForm.

2. Setting UserForm Properties, Events, and Methods

You can set properties such as the form’s name, caption, and size in the Properties window. For example:

Name: MyUserForm

Caption: Custom Dialog

To handle events like initializing the form, you can use the code window:

Private Sub UserForm_Initialize()

Me.Caption = 'Welcome to My Custom Dialog'

End Sub

3. Using Text Boxes, Command Buttons, Combo Boxes, and Other Controls

You can add controls by selecting them from the Toolbox and drawing them

on the UserForm.

For example:

TextBox: For user input.

CommandButton: To trigger actions.

ComboBox: For dropdown selections.