Introduction to VBA in Excel

07/04/25 •0•0

Welcome to the world of Excel automation with VBA (Visual Basic for Applications)! In this lesson, you will learn the basics to start creating macros and custom functions.

作者Leon

翻訳者Cards Realm

📌 What is VBA?

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

🔧 How to Access the VBA Editor

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"

📝 Basic VBA Structure

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

🛠 Getting Started: Creating a Simple Macro

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

Assign macro to button

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")
Use a guia Formatar para mudar cores e estilo

📌 Variables and Data Types

Basic Declaration: Dim Variable Name As Type

Main types:

Integer: Números inteiros
Double: Números decimais
String: Texto
Boolean: Verdadeiro/Falso
Range: Intervalo de células

Example:

Dim nome As StringDim idade As IntegerDim salario As DoubleDim ativo As BooleanDim celula As Range

🔄 Control Structures

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

📊 Working with Spreadsheets

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

Exercises

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().)