Learning centre · Macro Wizard

Build, test and manage your own Excel routines.

A complete practical guide, from your first description to editing, saving, reusing and recovering macros. Choose a section or work through the guide in order.

Start here

What Macro Wizard does.

Macro Wizard turns a plain-English description into VBA automation. Brief it as you would a developer: describe the data, the exact rules, the output and what must stay unchanged. Review the explanation and test the generated routine before saving it.

Download the practice workbook Explore Macro Wizard

Create your first routine

The six-step workflow.

Step	What to do	Check / next action
1. Open Assist Pro	Open Excel, select the Assist Pro tab and check the active workbook. Save a backup before starting.	The ribbon exposes Macro Wizard and the saved-macro controls.
2. Describe one task	Name the sheet, table, columns, rules and output. Use Accounting Demo and AccountingDemoData for the practice workbook.	A small, specific request is easier to review and test.
3. Generate	Choose Create Macro and click Get Macro after reviewing your prompt.	Review the generated code, explanation and safety checks. Confirm the sheet and ranges.
4. Review	Check the explanation against your request. Look for deletion, overwriting, fixed ranges and undefined blank handling.	Edit anything unclear before running.
5. Run and test	Run on the copy. Check row counts, totals, known exceptions and known clean rows.	Compare the result with the expected outcome and refine it where necessary.
6. Save and reuse	Choose Save Macro / Save Last Macro, name the routine and select an available slot.	Select the tested version from Saved Macros when the next file arrives.

Brief it precisely

Six things to decide before you generate.

Protect a copy

Keep the original table and answer-key fields unchanged. Macros can change many cells quickly.

One job per macro

Clean, test and report in separate stages. Start with text cleanup before duplicate testing.

Exact targets

Name Accounting Demo, AccountingDemoData and the headers exactly as they appear in Excel.

Exact business rules

Define valid values, thresholds and tolerances. For example, allow R0.02 when checking VAT.

A named output

Add or reuse Audit_Issue, or create a named report sheet. Do not overwrite source fields.

A reconciliation

Inspect both good and bad rows, check counts and run the routine twice.

Know your ribbon

What each macro control does.

Control	When to use it	Good practice
Last Created Macro / Last Macro Detail	Review the newest macro and its details.	Check its target before running, especially after switching workbooks.
Edit Last Macro	Regenerate the latest macro using additional instructions.	Describe only the change and restate what must stay unchanged.
Run Last Macro	Run the most recently generated macro again.	Confirm the active workbook, sheet and data layout.
Save Macro / Save Last Macro	Keep a tested macro in an available named slot.	Use a unique name such as AP_VAT_Test_v1.
Macro Management / Saved Macros	Select, run, edit or replace a saved routine.	Check the purpose and version before running.
Edit Saved Macro	Regenerate a saved macro with your requested changes.	Choose Save as New if the original must stay available.
Recycle	Restore the most recently deleted macro.	Make an empty saved-macro slot available first.

Build a clear brief

The strong prompt formula.

Create a macro that [ACTION] on [SHEET/TABLE]. Use [COLUMNS]. Apply these exact rules: [RULES]. Put the result in [OUTPUT]. Do not [SAFETY LIMITS]. At the end, [SUMMARY/CONFIRMATION].

Copy-ready VAT practice example

Create a macro that checks supplier invoices in AccountingDemoData on the Accounting Demo sheet. Use Record_Type, Net_Amount, VAT_Rate and VAT_Amount. Compare VAT_Amount with Net_Amount multiplied by VAT_Rate, allowing an absolute difference of R0.02. Flag missing or invalid inputs separately. Add or reuse Audit_Issue and write a clear reason for each failed check. Keep source values and every row unchanged. At the end, report rows checked and exceptions found. Make a second run reuse the same output column.

This is a calculation check for the demo data. Confirm the applicable business and tax rules before adapting it to live work.

Get better results

Eight prompting habits that matter.

Habit	Use this approach	Avoid	Why / example
Be specific	Name the sheet, table and columns.	“Check my data.”	VBA needs a precise target, such as AccountingDemoData[VAT_Amount].
Give exact rules	State thresholds, tolerances and valid values.	“Find strange entries.”	For example, high risk if the absolute amount is at least R500,000.
Protect the source	Say what must not change or be deleted.	Assuming cleanup will preserve everything.	Add flags without overwriting amounts.
Request an explanation	Ask for a reason or status column.	Colour-only output.	Text in Audit_Issue is filterable and reviewable.
Use one macro per stage	Clean, test and report separately.	One huge prompt.	Shorter code is easier to validate.
Handle blanks explicitly	Explain whether blanks are valid, skipped or errors.	Letting VBA guess.	Blank and zero differ. A blank Approver can be a specified risk flag.
Define duplicates	List the fields that make a duplicate.	“Remove duplicates.”	Exact duplicates differ from potential duplicates. Keep the rows and explain the match.
Ask for a completion message	Request counts and actions performed.	No confirmation.	Show rows checked and issues found so you can reconcile the result.

Examples you can adapt

Weak prompts, better prompts.

Cleanup

From vague to specific

Weak prompt: Clean this sheet.

Trim spaces in text columns of AccountingDemoData. Standardise known department names and highlight values that cannot be standardised. Do not delete rows.

Expected output: Clean text and highlighted exceptions.

Duplicates

From vague to specific

Weak prompt: Find duplicates.

On Accounting Demo, add or reuse Duplicate_Flag in AccountingDemoData. Flag exact duplicate rows and potential supplier-invoice duplicates where Counterparty, Document_No and Net_Amount match. Keep every row and explain the match.

Expected output: Duplicate_Flag with reasons.

Risk

From vague to specific

Weak prompt: Find risky transactions.

On Accounting Demo, add or reuse Risk_Level in AccountingDemoData. Mark High when Approver is blank, Approver equals Prepared_By, or the absolute Net_Amount is at least R500,000. Otherwise leave it blank. Preserve source fields.

Expected output: Repeatable classification using your defined rules.

Report

From vague to specific

Weak prompt: Make a report.

Create or replace Audit Exceptions from flagged rows in AccountingDemoData. Sort High, Medium, then Low, format as a table and show counts by Record_Type and Risk_Level. Leave the original table unchanged.

Expected output: A consistent exception report.

Refine a working routine

Edit safely and keep useful versions.

Select the right macro. Choose Edit Last Macro or select a saved slot and click Edit. Read its name and purpose.
Describe the exact change. For example, change a threshold from R500,000 to R250,000. Restate fields that must not be overwritten.
Click Edit Macro and review. Confirm the new change is present and the existing rules remain.
Run on test data. Compare the new result with the previous version. Check both affected and unaffected records.
Choose Save as New or Overwrite. Save as New preserves the original. Use it until the new version is proven. Overwrite replaces the saved version.

Choose the right path

Recommended workflows.

Goal	Sequence	Use when / check
Create	Describe → Get Macro → Review → Run → Save	New automation. Test on a copy.
Improve	Edit → Describe changes → Regenerate → Review → Run → Save	Nearly correct macro. Check that old rules remain.
Reuse	Select saved slot → Run	Existing routine. Confirm sheet and column names match.
Modify saved	Select slot → Edit → Describe changes → Regenerate → Save as New or Overwrite	Revised rules. Choose versioning deliberately and test the result.
Recover	Recycle → Review → Restore → Rename	Latest deleted macro. An empty saved slot is required.

Find the right command

Quick feature selector.

I want to…	Use	Remember
Create VBA from a description	Macro Wizard	Give exact targets, rules and outputs.
See the newest macro	Last Created Macro / Last Macro Detail	Review it before running.
Change the newest macro	Edit Last Macro	Restate safety limits.
Run the newest macro again	Run Last Macro	Check the active workbook and layout.
Keep a tested macro	Save Macro / Save Last Macro	Use a descriptive, unique name.
Use an older saved macro	Macro Management / Saved Macros	Select the correct slot and version.
Change a saved macro	Edit	Test the regenerated version.
Keep old and new versions	Save as New Macro	Add a version number.
Replace the saved version	Overwrite	Only after testing.
Recover the latest deletion	Recycle	An available slot is required.

Before you save

Testing and safety checklist.

Check	Pass condition	If it fails	Demo test
Scope	Only the named table or sheet changed.	Restore the backup and narrow the prompt.	Other worksheets and answer-key fields remain unchanged.
Row count	No rows disappeared unless deletion was requested.	Restore and specify “do not delete rows”.	AccountingDemoData still has 500 records.
Known exception	A planted bad row is flagged.	Make the rule more explicit.	Use a row with a known issue for the rule being tested.
Known clean row	A valid row remains unflagged.	Review blank and zero handling.	Check a normal record as well as an exception.
Amounts	Totals and signs remain unchanged unless requested.	Restore and prohibit overwriting source fields.	Reconcile Debit, Credit and Bank_Amount.
Repeat run	A second run does not duplicate columns or sheets.	Ask it to reuse or clear named output.	Only one Audit_Issue column exists.

In the practice workbook, AA:AD contain Source_Row, Expected_Issue, Severity and Suggested_Macro_Test. Compare relevant flags with these expected-result fields and leave them unchanged. These fields are part of the table, rather than a separate hidden answer-key sheet.

When the result needs work

Troubleshoot a specific problem.

Wrong sheet changed

Likely cause: The target was not named.

State exact sheet and table names. Restore the backup, then rerun on a copy. Do not keep testing on the live file.

Too many false positives

Likely cause: The rule was subjective.

Add precise conditions and tolerances. Test known clean records. Avoid words such as “unusual” without a definition.

Macro stops on blanks

Likely cause: Blank handling was not defined.

Specify which blanks are skipped or flagged and include blank test cases. Do not automatically replace blanks with zero.

Second run breaks output

Likely cause: Existing columns or sheets were not handled.

Reuse, clear or replace named output. Test repeatability rather than creating unnamed copies.

One-time Excel setup

Allow Macro Wizard to access the VBA project.

If Macro Wizard cannot create or insert a VBA action, Excel may be blocking programmatic access to the VBA project. In Excel, open File → Options → Trust Center → Trust Center Settings → Macro Settings, then enable Trust access to the VBA project object model.

Close and reopen Excel, then try the workflow on a copy of a workbook.

Put the guide to work

Create your first saved routine.

Download the sample workbook, follow the interactive practice and save a routine once it passes your checks.

Start the interactive practice Get Assist Pro · Start your trial