Excel + VBA Getting Started Guide

📅 2026-01-06

1. Automate repetitive tasks

VBA lets you get rid of manual repetitive work

  • Scenario:Data for 100 worksheets needed to be updated every week, which took hours to do manually.
  • VBA solution:Write macros to complete all operations with one click, saving more than 80% of time.
  • Application scope:Data import, formatting, calculation summary, report generation, etc. can all be automated.
  • Earnings:Reduce manual errors, improve work efficiency, and free up time to focus on high-value tasks.

2. Process massive data quickly

VBA easily processes hundreds of thousands of rows of data

  • Scenario:Eligible records need to be extracted and classified from 500,000 rows of sales data.
  • VBA solution:Writing loop code takes seconds, while doing it manually would take days.
  • Performance comparison:VBA processing of 1 million rows of data can be completed in 1-2 minutes, 100 times faster than GUI operations.
  • Application scope:Data cleaning, deduplication, merging, sorting, filtering, etc. are all supported.

3. Create interactive tools and dashboards

Build professional tools without a programming language

  • Scenario:Create a quotation system for the sales team to automatically calculate prices and discounts by entering product names and quantities.
  • VBA solution:Combine buttons, drop-down boxes, dialog boxes and other controls to achieve a complete interactive process.
  • Application scope:Professional applications for sales tools, inventory management, costing, performance evaluation and more.
  • Advantages:Users do not need to learn programming and can use it by clicking a button, reducing training costs.

4. Cross-system data integration

VBA easily connects multiple data sources

  • Scenario:Need to regularly import data from ERP systems, databases, and websites into Excel for summary.
  • VBA solution:Automatically connect to the database, call API, crawl web page data and import into Excel.
  • Application scope:Data integration, ETL operations, automatic report generation, and data synchronization.
  • Advantages:No need to learn database or API tools, do it all in Excel.

5. Calculation and analysis of complex conditions

Tasks that formulas cannot handle, VBA can easily handle them

  • Scenario:Employee bonuses are calculated based on a combination of 10 conditions, and the nested formulas are complex and difficult to maintain.
  • VBA solution:Use If-Then-Else to make the logic clear and easy to maintain, and can handle any complex conditions.
  • Application scope:Complex calculations, multi-condition judgments, custom business logic, and risk assessment.
  • Advantages:The code structure is clear, easy to understand and modify, and is more readable than formulas.

6. Automatically generate professional reports and documents

Generate standardized reports and presentations with one click

  • Scenario:Sales reports for 50 departments need to be generated every month, with a uniform format but different data.
  • VBA solution:Automatically fill in data, set formats, insert charts, and generate PDFs with just one click.
  • Application scope:Financial statements, sales analysis, project summaries, and audit reports are automatically generated.
  • Advantages:Ensure consistent reporting formats, reduce low-level errors, and free up team time.

7. Seamless integration with other Office tools

VBA can control Word, PowerPoint, Outlook, etc.

  • Scenario:Need to automatically import data from Excel into Word contracts and PowerPoint presentations.
  • VBA solution:Automatically open Word/PPT through VBA, fill in data, and save the file.
  • Application scope:Report automation, batch email sending, automatic document generation, and data distribution.
  • Advantages:One script can control multiple tools, with the highest degree of workflow integration.

8. No additional software costs

VBA is a built-in function of Excel, completely free

  • Cost:VBA is included with Office purchase at no additional cost.
  • Comparison:The same function would cost tens of thousands of yuan to purchase professional software, but the cost of VBA is zero.
  • Maintenance:Code is stored in Excel files, requiring no additional servers or maintenance.
  • Easy to share:Files can be sent directly to colleagues for use, with no installation or authorization required.

🚀 Simple introduction to VBA

Step 1: Open the VBA editor

  • Operation:Press Alt + F11 in Excel to open the VBA editor window.
  • Another way:Click the menu "Developer Tab" and click "Visual Basic".
  • Enable development tools:If there is no "Development Tools" in the menu, you need to enable it first: File → Options → Customize the Ribbon → Check "Development Tools".
  • Interface understanding:On the left is the project browser, in the middle is the code editing area, and below is the immediate window.

Step 2: Create the first macro (Sub program)

  • Operation:Enter the following code in the editing area:
  • Sub HelloWorld()
  • MsgBox "Hello Excel!"
  • End Sub
  • Execution:Press F5 or click the "Run" button on the toolbar, and a prompt box will pop up showing "Hello Excel!".
  • Description:MsgBox is a command that pops up a prompt box, and Sub represents a subroutine (the most commonly used macro type).

Step 3: Access and operate cells

  • Read cells:
  • Dim value As String
  • value = Range("A1").Value
  • This code reads the value of cell A1.
  • Write to cell:
  • Range("B1").Value = "数据"
  • This code writes "data" to cell B1.
  • Set format:
  • Range("C1").Font.Bold = True
  • This code sets the text in cell C1 to bold.

Step 4: Use a loop to process multiple cells

  • Code example: Multiply the numbers A1:A10 by 2
  • Sub DoubleValues()
  • Dim i As Integer
  • For i = 1 To 10
  • Range("A" & i).Value = Range("A" & i).Value * 2
  • Next i
  • End Sub
  • Description:The For loop goes from 1 to 10, and each time the value of the cell is taken out, it is multiplied by 2 and then put back.

Step 5: Bind the macro to the button (convenient execution)

  • Operation:Insert a button into an Excel worksheet: Developer → Insert → Button (Form Control).
  • Draw the button:Drag the mouse to draw a button on the worksheet.
  • Assign macro:In the pop-up dialog box, select the macro you created (such as DoubleValues) and click OK.
  • Use:Then clicking the button will automatically execute the macro without opening the VBA editor.
  • Modify button name:Right-click the button → Edit text and change it to a descriptive name such as "Multiply by 2".

💼 VBA practical cases

Case 1: Automatically generate sales reports

Automatically aggregate sales data from raw data and generate reports

  • Scenario:There is a sales data table (product, sales volume, amount) that needs to be summarized by product category.
  • VBA code logic:
  • 1. Read all data in the data source worksheet
  • 2. Calculate total sales volume and total amount by product category
  • 3. Create a summary table in a new worksheet
  • 4. Add chart visualization display
  • Effect:It's done automatically with the click of a button, takes half an hour manually, and only takes 2 seconds with VBA.

Case 2: Batch import data and clean it

Batch import data from external files, automatic deduplication and formatting

  • Scenario:Customer information needs to be imported from 10 CSV files, merged and deduplicated.
  • VBA code logic:
  • 1. Iterate through all CSV files in the specified folder
  • 2. Open each file and read the data into Excel
  • 3. Remove duplicate rows (based on customer ID)
  • 4. Unified format and date format
  • Effect:1 million rows of data completed in 1 minute, which would take hours manually.

Case 3: Automatically calculate employee bonuses

Automatically calculate complex bonuses based on multi-dimensional conditions

  • Scenario:The bonus rules are complicated: sales + commission + performance bonus + seniority bonus.
  • VBA code logic:
  • 1. Read employee information (sales, performance ratings, length of service)
  • 2. Determine the bonus level based on multiple If conditions
  • 3. Calculate each part of the bonus and summarize it
  • 4. Generate bonus table and sort by amount
  • Effect:The bonus calculation for 50 people is completed in 3 seconds, reducing manual calculation errors.

Case 4: Automatically send emails and reports

Automatically generate reports and send them to relevant personnel via email

  • Scenario:Department reports need to be generated and sent to leaders and customers via email every week.
  • VBA code logic:
  • 1. Generate data summary report for the current week
  • 2. Set the email body and attachments
  • 3. Automatically send emails to designated recipients through Outlook
  • 4. Record sending log to Excel
  • Effect:Automatically complete with the click of a button, no need to manually manipulate emails.

Case 5: Interactive parameter query tool

Automatically filter and display results after user inputs parameters

  • Scenario:Sales query system: Enter the product name and date range to query sales.
  • VBA code logic:
  • 1. Create user interface: input box and query button
  • 2. Read the parameters entered by the user
  • 3. Find matching records in the data source
  • 4. Display summary data and charts in the results area
  • Effect:There is no need for the IT department to develop database tools, business personnel can query by themselves.

📚 VBA learning route and common statements

Commonly used phrases cheat sheet

  • Variable declaration:Dim 变量名 As 数据类型 (such as String, Integer, Boolean)
  • Assignment:变量 = 值
  • Conditional judgment:If 条件 Then ... Else ... End If
  • Loop:For i = 1 To 10 ... Next i
  • Prompt box:MsgBox "提示内容"
  • Input box:InputBox "请输入内容"
  • Reference cell:Range("A1") or Cells(行号, 列号)
  • Quoting the entire column:Columns("A") or quote the entire line Rows(1)
  • Count rows:Rows.Count or UsedRange.Rows.Count

Beginner to Intermediate Learning Route

  • Week 1: Basic GrammarUnderstand variables, data types, assignments and simple judgments
  • Week 2: Loops and Cell OperationsMaster For loops, reading and writing cells, and access ranges
  • Week 3: Worksheets and data manipulationCreate/delete worksheets, copy and paste, sort and filter data
  • Week 4: Practice small projectsComplete a simple data processing or report generation project
  • Weeks 5-6: Advanced FeaturesFunctions, error handling, interacting with Word/PowerPoint
  • Suggested resources:Official help documentation, YouTube video tutorials, and actual project exercises

Common errors and debugging

  • Syntax error:The editor will check spelling and keywords with red wavy lines.
  • Run error:An error occurred during execution. Check whether the variable type and cell reference are correct.
  • Logic error:The code runs but the result is wrong, use MsgBox to output the variable value for debugging.
  • Debugging tips:Set a breakpoint (click the line number in the left column), press F8 to execute step by step, and observe the variable values.
  • Check the error message:When an error occurs, click the "Debug" button to locate the error location.

💡 VBA usage suggestions and best practices

Start small:Start with simple single cell operations and gradually expand to complex data processing.

Backup file:Always back up the original file before writing VBA to avoid data loss or overwriting.

Add annotation:Add comments to the code to facilitate future maintenance and understanding by others.

Modular programming:Decompose complex functions into multiple small Sub programs to improve code readability and reusability.

Tested multiple times:Before running on the official data, repeatedly test on the replica to ensure that the logic is correct.

Security protection:Important VBA tools can be password protected to prevent accidental changes.