Introduction to VBA in Excel

VBA = Visual Basic for Applications

Programming language integrated into Excel (and other Office programs)

Allows you to automate tasks, create custom functions and interact with spreadsheets in an advanced way

Press ALT + F11 (universal shortcut)

Or visit:

Developer Guide > Visual Basic

If you don't see the "Developer" tab:

File > Options > Customize Ribbon > Check "Developer"

In the VBA Editor, you will work with:

• Módulos: Onde escreve os códigos (Inserir > Módulo)
• Sub: Executa ações (macros)
• Function: Retorna valores (funções personalizadas)

Sub Example:

Sub WelcomeMessage()

MsgBox "Hello, VBA world!"

End Sub

The above terms mean:

• Sub: Começa uma "receita" (chamamos de macro)
• ' (Apostrofo): É um comentário - o Excel ignora, serve para você anotar
• MsgBox: Mostra uma mensagem na tela
• End Sub: Termina a "receita"

Function Example:

Function Double(number As Double) As Double

Double = number * 2

End Function

Objective: To greet you!

Code:

Sub SayHello()

Dim name As String

name = InputBox("What is your name?")

MsgBox "Hello, " & name & "!"

End Sub

How to run:

- Press F5 in the VBA Editor

- Or assign to a button in the spreadsheet

Go back to Excel and follow:

• Guia Inserir > Formas > Escolha um retângulo ou botão
• Desenhe o botão na planilha
• Clique com o botão direito no botão criado
• Selecione Atribuir Macro...
• Escolha sua macro na lista (MensagemSimples)
• Clique em OK
• Para formatar o botão Clique direito > Editar Texto para mudar o rótulo (ex: "Clique Aqui")

Ad
• Use a guia Formatar para mudar cores e estilo

Basic Declaration: Dim Variable Name As Type

Main types:

• Integer: Números inteiros
• Double: Números decimais
• String: Texto
• Boolean: Verdadeiro/Falso

Ad
• Range: Intervalo de células

Example:

1. Conditionals (IF)

If condition Then

'code if true

Else

'code if false

End If

Example:

Sub CheckAge()

Dim Age As Integer

age = Range("B1").Value

If age >= 18 Then

MsgBox "Of legal age"

Else

MsgBox "Minor"

End If

End Sub

2. Loops (For, Do While)

For example:

For i = 1 To 10

'repeated code

Next i

Do While:

Do While condition

'repeated code

Loop

Practical example:

Sub ColorEvenLines()

Dim i As Integer

For i = 1 To 10 Step 2

Rows(i).Interior.Color = RGB(200, 200, 200)

Next i

End Sub

Referencing Cells

'Ways to reference:

Range("A1").Value = 100

Cells(1, 1).Value = 100 'Row 1, Column 1

Practical Example: Automated Addition

Sub SumRange()

Dim total As Double

total = Application.WorksheetFunction.Sum(Range("A1:A10"))

MsgBox "The total is: " & total

End Sub

1. Add Two Numbers. Objective: To create a macro that reads two numbers from cells A1 and B1, calculates their sum, and displays the result in C1. (Use Range("A1").Value to access values.)

2. Create a macro that formats cell A1 with: Yellow background color. Bold text. Red font. (Hints => .Interior.Color = RGB(255, 255, 0) (yellow), .Font.Bold = True, .Font.Color = RGB(255, 0, 0) (red)

3. Convert to Uppercase. Objective: Create a macro that converts the text in cell A1 to UPPERCASE and displays the result in B1. (Hint: Use the UCase() function.)

4. Create a Multiplication Table. Objective: Create a macro that fills in a multiplication table from 1 to 5 in cells A1:E5. (Hint: Use two nested For loops.)

5. Clear Cell Contents. Objective: Create a macro that clears the contents of cells A1 through C10. (Hint: Use Range("A1:C10").ClearContents().)