12.4 The VBA Editor (VBE)
Open with Alt + F11 (or Developer › Code › Visual Basic).
| Part | Open with | Purpose |
|---|---|---|
| Project Explorer | Ctrl + R | Tree of open workbooks: Microsoft Excel Objects (sheets, ThisWorkbook), Modules, Forms |
| Properties window | F4 | Properties of the selected object (e.g. a sheet's Name/CodeName) |
| Code window | double-click a module | Where you write code |
| Immediate window | Ctrl + G | Run one line or print values: ?Range("A1").Value, Debug.Print output |
| Locals / Watch windows | View menu | See variable values while debugging (12.15) |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on the vba editor vbe.
Steps in the VBE
- Insert › Module – creates
Module1. General macros and functions go in standard modules. - At the top of every module type
Option Explicit(forces you to declare variables). Make it automatic: Tools › Options › Editor › tick Require Variable Declaration. - Rename a module: select it › F4 › change (Name) to
modReports. - Export/import modules (right-click) to reuse code in other workbooks.
- In the Immediate window type
?Worksheets.Countand press Enter to see the number of sheets.
Ravindra Bagale's Tip
If you write code in a sheet's module (for example Sheet1 (Orders)), it works only for that sheet and the function can't be found on the worksheet – many students get stuck here. Always write normal macros and UDFs in Insert › Module. And don't write code without Option Explicit – spelling mistakes get caught immediately.
Ravindra Bagale's Tip – मराठी
Code sheet च्या module मध्ये (उदाहरणार्थ Sheet1 (Orders)) लिहिला तर तो फक्त त्या sheet साठी चालतो आणि function worksheet मध्ये सापडत नाही – बरेच students इथे अडकतात. Normal macros आणि UDF नेहमी Insert › Module मध्ये लिहा. आणि Option Explicit शिवाय code लिहू नका – spelling ची चूक लगेच पकडली जाते.
Ravindra Bagale's Tip – हिंदी
Code को sheet के module में (जैसे Sheet1 (Orders)) लिखा तो वह सिर्फ़ उसी sheet के लिए चलता है और function worksheet में नहीं मिलता – बहुत से students यहाँ अटक जाते हैं. Normal macros और UDF हमेशा Insert › Module में लिखो. और Option Explicit के बिना code मत लिखो – spelling की गलती तुरंत पकड़ी जाती है.
Worked example — the vba editor vbe
Why
Aarti Kulkarni in Pune and Liam Murphy abroad both need a clear, repeatable way to use the vba editor vbe on a Orbit Logistics sheet without breaking totals.
What
The Vba Editor Vbe is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for the vba editor vbe.
- Enter a small made-up value for Aarti Kulkarni using $ where money appears.
- Add a second value for Liam Murphy using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once the vba editor vbe is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of the vba editor vbe on a blank sheet for QuickCart Delivery.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Aarti Kulkarni and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Anna Kowalski.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply the vba editor vbe to a 5-row made-up QuickCart Delivery table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with the vba editor vbe.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as QuickCartDe_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
- Late orders (Easy): How many rows have Status "Late"?
- Total amount (Easy): Add up the Amount column.
- Worst delay (Easy): What is the largest number in the Days late column?
- Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
- Customer for an order (Medium): Find the Customer for Order ID "DL-704".
- Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
- Big orders (Medium): How many rows have an Amount of 2000 or more?
- Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
- Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
- Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
- Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
