1. How to Create a UserForm in VBA?

2. Internal Elements of the UserForm – Buttons, TextBox, ComboBox, and Others

2.1. Image

2.2. Label

2.3. TextBox

2.4. ComboBox

2.5. CheckBox

2.6. OptionButton

3. Properties of the UserForm and Its Elements

Private Sub CommandButton1_Click()

  Dim greeting As String
  greeting = "Hello " & TextBox1.Value & " " & TextBox2.Value & "!"

  MsgBox greeting, vbInformation, "Greeting"
  
End Sub

Private Sub CommandButton2_Click()

  Unload Me
  
End Sub

Key Benefits of Collections:

• Effortless dynamic resizing.
• Efficient data handling with unique identifiers (Keys), avoiding duplicates.
• Ideal for grouping elements in a structured way.

1. Declaring a Collection

Dim CollectionName As Collection
Set CollectionName = New Collection

Dim Square As Collection
Set Square = New Collection

2. Adding Elements to a Collection

Square.Add "Black"         'Index 1
Square.Add "Grey", "Favorite" 'Index 2 with Key "Favorite"
Square.Add "Green"         'Index 3

Square.Add "Red1", before:=2      'Before index 2
Square.Add "Red1", before:="Favorite" 'Before Key "Favorite"

Dim i As Long
Dim ws As Worksheet

Set ws = Sheet1 'Sheet name
Set Square = New Collection

i = 2
Do While Not IsEmpty(ws.Cells(i, 1))
    Square.Add ws.Cells(i, 2)
    i = i + 1
Loop

3. Accessing Collection Elements

Debug.Print Square(2)

Debug.Print Square("Favorite")

4. Removing Collection Elements

Square.Remove 3 'Removes the element at index 3

5. Iterating Through a Collection

5.1 Using For-Next or Do-Loop

For i = 1 To Square.Count
    Debug.Print Square(i)
Next

i = 1
Do While i <= Square.Count
    Debug.Print Square(i)
    i = i + 1
Loop

5.2 Using For Each

For Each Elem In Square
    Debug.Print Elem
Next

Conclusion

How to Find the Last Occupied Cell with a Loop in VBA

Sub getLastRow()
i = 1
 Do
     If IsEmpty(Cells(i, 1)) = True Then Exit Do
     i = i + 1
 Loop
lastRow = i - 1
Cells(lastRow + 1, 1) = "First available cell" 
End Sub

Sub getLastColumn()
 j = 1
 Do
     If IsEmpty(Cells(1, j)) = True Then Exit Do
     j = j + 1
Loop 
lastColumn = j - 1
Cells(1, lastColumn + 1) = "First available cell" 
End Sub

Quick Method: Find the Last Occupied Cell without a Loop

Sub getLastRow()
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastRow + 1, 1) = "First available cell"
End Sub

Sub getLastColumn()
lastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Cells(1, lastColumn + 1) = "First available cell"
End Sub

Other Methods for Finding the Last Occupied Cell

Using UsedRange

Sub getLastRowUsedRange()
    Dim lastRow As Long
    lastRow = ActiveSheet.UsedRange.Rows.Count
    MsgBox "The last occupied row is: " & lastRow
End Sub

Sub getLastColumnUsedRange()
    Dim lastColumn As Long
    lastColumn = ActiveSheet.UsedRange.Columns.Count
    MsgBox "The last occupied column is: " & lastColumn
End Sub

Using Find

Sub getLastRowFind()
    Dim lastRow As Long
    On Error Resume Next
    lastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    MsgBox "The last occupied row is: " & lastRow
End Sub

Sub getLastColumnFind()
    Dim lastColumn As Long
    On Error Resume Next
    lastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    On Error GoTo 0
    MsgBox "The last occupied column is: " & lastColumn
End Sub

In VBA (Visual Basic for Applications), selecting cells in Excel is one of the most common operations. Whether you want to select a specific cell, a range of cells, or move between cells in a loop, understanding how to reference cells in VBA is essential. Below, we show you how to do it efficiently and optimized for your Excel macros.

1. Selecting Cells by Direct Reference

The simplest way to select a cell in VBA is by referring to its address directly, just like you would in Excel. For example, if you want to select cell "E2", you would use the following code:

Range("E2").Select

This method is ideal for simple selections, but if you need to handle dynamic cells or perform more complex selections, we recommend using more advanced methods.

2. Selecting Cells in Matrix Form

If you need to select cells more flexibly or perform operations on multiple cells, you can use the matrix form. This is especially useful when working with loops or more dynamic structures. In this case, you specify the row and column of the cell you want to select.

To do this, you can use the Cells function along with the reference to the worksheet. This is helpful when the rows and columns are variables and you don’t want to make a fixed reference like in the previous example.

Sheets("Sheet Name").Cells(Row, Column).Select

For example, if you want to select the cell in row 2 and column 5 (which corresponds to cell E2), the code would be:

Sheets("Sheet 1").Cells(2, 5).Select

This approach is useful when working with variable ranges or cases where the destination cell might change depending on certain conditions in your VBA code.

3. Using Loops to Select Cells Dynamically

When you need to select multiple cells dynamically, loops are extremely helpful. You can iterate through rows and columns using For or For Each loops, allowing you to perform actions on each cell without having to manually reference each one.

For example, if you want to select all cells in column "A" up to row 10, you can use a loop like this:

For i = 1 To 10
    Sheets("Sheet 1").Cells(i, 1).Select
Next i

This code will select each cell in column A (from A1 to A10) one by one. You can easily modify it to loop through other columns or cell ranges.

Using loops allows you to automate repetitive tasks efficiently, which is a great advantage when programming macros in Excel.

4. Additional Tips for Selecting Cells in VBA

Here are some additional tips for working with cells in VBA:

• Avoid excessive use of .Select: While it's useful in some cases, it's better to avoid excessive use of the .Select function to optimize your macros' performance.
• Always specify the worksheet: When working with multiple sheets, it's always recommended to specify the worksheet to avoid confusion or errors in cell selection.
• Use the .Activate property with caution: While .Activate can be useful, it may affect the visibility of the selected cells. Use it only when necessary.

These tips can help you make your code more efficient and less prone to errors. If you want to learn more about VBA programming or need help with other Excel topics, check out our tutorials or contactus for personalized assistance.

An Array is a collection of items "packaged" into a single variable; in VBA and many other programming languages, Arrays are commonly used structures.

Defining an Array in VBA

The months of the year provide a good example. If we needed to store information for each month without using Arrays, we would need twelve (12) separate variables, one for each month. To simplify, we could create an array with 12 elements, one for each month. There are several ways to declare arrays in VBA:

The first two options are the most intuitive: the first option creates an array with 12 elements, from element 1 to 12. The second option uses a matrix structure, which may feel familiar. You can also check out our tutorial on Loops in VBA, as well as tips and shortcuts for Selecting a Cell or Finding the Last Occupied Cell.

Arrays in VBA are similar to arrays in other programming languages where the default base is zero. Therefore, the array described in option 3 starts from 0 and goes up to 11, meaning it has 12 elements. However, to make the process more intuitive, VBA allows you to change the default base using the Option Base 1 statement, as shown in option 4. It is recommended to place the Option Base 1 declaration at the beginning of each module.

Array Sizing

Unlike variables, whose declaration is optional (you can read more about this in our Variables in VBA tutorial), the size of an array must be declared before storing any elements in it.

The examples shown so far are one-dimensional arrays, for simplicity. However, it’s important to note that Arrays can have up to 60 dimensions or more.

Let’s look at a three-dimensional example: if you want to store monthly working hours data for each employee based on their shift, a smart approach would be using an array. To size the array, we need to know the maximum values each variable could take. For months, the maximum value will be 12. The total number of employees depends on the company's workforce; here we assume a value of 10. Since each shift is 8 hours, the maximum number of shifts will be 3. The declaration would look like this:

Example:

Option Base 1
Dim Hours (12, 10, 3)

Since the base is 1, 12 represents the number of months, 10 represents the number of employees, and 3 represents the number of shifts.

Therefore, to store the information, you will need to fill in the corresponding spots for each month, employee, and shift. Here are some examples:

Example a): If “Employee #3” worked 125 hours in the second shift in July, the value assignment in the Array would be:

Hours(7, "Employee#3", 2) = 125

Example b): If “Employee #8” worked 150 hours in the first shift in April, the value assignment in the Array would be:

Hours(4, "Employee#8", 1) = 150

Example c): If “Employee #1” worked 200 hours in the third shift in December, the value assignment in the Array would be:

Hours(12, "Employee#1", 3) = 200

Extracting or Reading Data from the Array

Fortunately for us, VBA is very intuitive. To write a value from a specific element of an array to a cell or variable, simply refer to the array’s position, as shown in the following example for a three-dimensional array:

Example in a cell:

Range("A2") = Hours(7, "Employee#3", 2)

Example in a variable:

ResultHP = Hours(7, "Employee#3", 2)

With this approach, make sure that the elements you are referring to exist in the array, as VBA will show a dimensioning error if they don’t. To avoid such errors, you can use the Lbound and Ubound functions to find the lower and upper bounds of your array, respectively.

Dynamic Sizing of the Array

An advanced use of arrays refers to dynamic sizing. If we revisit the previous example, the number of employees might change over time.

To handle this, we must initialize the array as empty, and it is essential to assign its dimensions before storing any data in it using the Redim command, as shown below:

Option Base 1
Dim Hours() As Integer
Redim Hours(12, 10, 3)

The size values for redimensioning can be constants, as in the example above, or they can also be variables previously defined, as shown here:

Option Base 1
Dim Hours() As Integer
Nemp = 10
Redim Hours(12, Nemp, 3)

The Redim command can expand or reduce the array's size. However, keep in mind that it will delete any previously stored information in the array. To expand the array and preserve its data, use the Redim Preserve statement. Below is an example showing "empty spaces" added to the array, represented by the green-backgrounded elements.

Here is a code example for resizing the number of employees in the illustrated example:

Option Base 1
Dim Hours(12, 10, 3) As Integer
Nemp = 12
ReDim Preserve Hours(12, Nemp, 3)

Remember that reducing the array's size will cause you to lose data for the elements that are "excluded" from the array. For instance, if we resize from a 3x3 array to a 2x2 array, we lose data from the white-background elements.

As with the previous example, to preserve data for the elements in the blue-background areas, use the ReDim Preserve statement.

Want to learn more? Contact us...

What is a Loop in Programming?

Types of Loops in VBA

For Next Structure

Example of the For Next Loop in VBA

Sub Tutorial5a_Loops()
For i = 1 To 10
  If Cells(i + 1, 1) = "" Then
    LastRow = i 
    Exit For
  End If
Next
MsgBox ("Last Row: " & LastRow)
End Sub

Do Loop Structure

Do Mode

Example of the Do Loop in VBA

Sub Tutorial5b_Loops()
i = 2
Do
  If Cells(i, 1) = "" Then
    LastRow = i - 1
    Exit Do
  End If
  i = i + 1
Loop
MsgBox ("Last Row: " & LastRow)
End Sub

Do While Mode

Sub Tutorial5c_Loops()
i = 2
Do While Cells(i, 1) <> ""
  'Repetitive Activity
  i = i + 1
Loop
LastRow = i - 1
MsgBox ("Last Row: " & LastRow)
End Sub

Do Until Mode

Sub Tutorial5d_Loops()
i = 2
Do Until Cells(i, 1) = ""
  'Repetitive Activity
  i = i + 1
Loop
LastRow = i - 1
MsgBox ("Last Row: " & LastRow)
End Sub

Conclusion

What is a Conditional?

How to Write a Conditional in VBA?

Example of If Conditional in VBA: Movie Tickets

• Children aged 12 or younger will receive a "Preferential" ticket.
• Adults older than 12 will receive a "Regular" ticket.

Sub AssignTicket()
    Dim age As Integer
    ' Read age from cell B2
    age = Range("B2").Value

    ' Evaluate the condition
    If age <= 12 Then
        Range("C2") = "Preferential" ' Ticket for children
    Else
        Range("C2") = "Regular" ' Ticket for adults
    End If
End Sub

• If 10 <= 12 is true, cell C2 will show "Preferential".
• If the user enters 15, the system will evaluate 15 <= 12, which is false, and cell C2 will show "Regular".

Nested If Conditionals in VBA

Example of Nested If Conditional in VBA

Sub AssignCompleteTicket()
    Dim age As Integer
    age = Range("B2").Value

    If age <= 12 Then Range("C2") = "Preferential" ' Ticket for children ElseIf age > 59 Then
        Range("C2") = "Preferential" ' Ticket for seniors
    Else
        Range("C2") = "Regular" ' Ticket for adults
    End If
End Sub

Using Or and And (Logical Operators) in VBA Conditionals

Examples of Using Or and And in VBA Conditionals

Sub AssignTicketWithOperators()
    Dim age As Integer
    age = Range("B2").Value

    If age > 12 And age < 60 Then
        Range("C2") = "Regular"
    Else
        Range("C2") = "Preferential"
    End If
End Sub

Sub AssignTicketWithOr()
    Dim age As Integer
    age = Range("B2").Value

    If age < 13 Or age > 59 Then
        Range("C2") = "Preferential"
    Else
        Range("C2") = "Regular"
    End If
End Sub

Conclusions

What Are Variables in VBA and Why Are They Used?

What Is Variable Declaration in VBA?

Types of Variables in VBA

Levels of Variables in VBA

How to Declare a Variable in VBA?

1. Open the VBA command editor (you can refer to our first tutorial on macros in Excel).
2. Use the Dim statement to declare the variable.

Dim pi As Double

MsgBox(pi)

Tips for Using Variables in VBA

• To require VBA to always declare variables, use Option Explicit at the beginning of any module. This helps you avoid declaration errors.
• If you forget to declare a variable, VBA will highlight the error, making it easy to identify and correct.
• Use the CTRL + Space combination to access a menu of declared variables when typing.

About Variable Names in VBA

• The first character must always be a letter.
• VBA is case-insensitive; Var and var refer to the same variable.
• Spaces and certain symbols are not allowed in names.
• The maximum number of characters for a variable name is 255.

What is the Macro Recorder?

Why Use the Macro Recorder?

• Ease of Use: No programming knowledge is required; simply perform the actions you want to automate.
• Time-Saving: Quickly record repetitive tasks, saving you hours of manual work.
• Learning Tool: Great for beginners to learn how VBA works by analyzing the recorded code.

Step-by-Step Guide to Using the Macro Recorder

Step 1: Enable the Developer Tab

1. Open Excel.
2. Go to File > Options.
3. In the Excel Options window, select Customize Ribbon.
4. Check the box next to Developer and click OK.

Step 2: Start Recording a Macro

1. Click on the Developer tab in the ribbon.
2. Click on the Record Macro button.
3. In the Record Macro dialog box, enter the following:
   • Macro Name: Give your macro a descriptive name (no spaces).
   • Shortcut Key: Assign a shortcut key (optional).
   • Store Macro In: Choose where to save your macro (this workbook, new workbook, or Personal Macro Workbook).
   • Description: Add a brief description of what the macro does (optional).
4. Click OK to start recording.

Step 3: Perform Your Actions

Step 4: Stop Recording

1. Once you’ve completed your tasks, go back to the Developer tab.
2. Click on Stop Recording.

Step 5: View and Edit the Recorded Macro

1. Click on Macros in the Developer tab.
2. Select your macro from the list and click Edit. This will open the VBA editor.
3. Analyze the code and make any necessary modifications.

Tips for Optimizing Your Macros

• Keep It Simple: Start with simple tasks to understand how the Macro Recorder works.
• Edit Recorded Code: Clean up the recorded code for efficiency. The Macro Recorder often includes unnecessary lines.
• Comment Your Code: Use comments to explain what each part of your code does for future reference.
• Use Relative References: If you want your macro to work regardless of where the active cell is, enable Use Relative References in the Developer tab before recording.

What is an Excel Macro?

Activate the Developer Tab in Excel

Access the VBA Editor

Add a New Module in VBA

Write the VBA Program

Sub MyFirstMacro()
  Dim strGreeting As String

  strGreeting = "Hello World!"

  MsgBox strGreeting
End Sub

Run the Program in VBA

Save the Excel Workbook with Macros Enabled