Excel macros, VBA and formulas written by a local AI and tested on a copy

Excel macros and VBA from an AI, with the spreadsheet staying in-house

A language model running on a workstation in your company can write Excel macros, VBA code, Power Query steps and formulas from a description in plain words, and none of the spreadsheet leaves the building. You describe the sheets, the columns and what should happen, the AI returns the code with an explanation, and you test it on a copy of the workbook. For formulas and short macros, a workstation with one NVIDIA RTX PRO 4000 Blackwell (24 GB) is enough; for a team with longer macros we recommend one NVIDIA RTX PRO 4500 Blackwell (32 GB), which serves a few people at once.

This guide is for people in controlling, logistics, purchasing and the back office who build reports and lists in Excel and would like the repetitive parts automated, and for the manager who decides whether that is allowed. Three terms in plain words. A macro is a recorded or written sequence of steps that Excel runs at the click of a button. VBA (Visual Basic for Applications) is the programming language in which Excel macros are written. A local model is an AI that runs on your own hardware, on a graphics card (GPU) with enough memory, instead of in a provider's cloud.

Running the model locally matters here because a useful macro needs your sheet names, column headings and a few example rows. In a cost centre report or a supplier list, those alone can reveal more than you want to share. Our guide to local AI and the GDPR explains what own hosting solves and what stays your company's duty.

What a local AI writes for Excel

Most office requests fall into four kinds of code. The table shows which one fits which job.

Power Query M and Excel VBA documentation, openpyxl documentation, read 30 October 2026; the assignment of tasks is our recommendation.

Power Query is often the better answer for imports. Its documentation describes the M language as being "used to filter, combine, and mash-up data from one or more supported data sources", and the steps repeat on next month's file without a macro. To paste AI-written M code, open the Power Query editor, select Advanced Editor on the View tab, and replace the query code; the editor shows whether the code is free of syntax errors. M is case-sensitive, so a function name with the wrong capital letter fails.

A local AI does not see your workbook by itself and cannot run the macro. It writes code from what you tell it. The quality of the result depends on how precisely you describe the layout and the rule, which is why the workflow below matters more than the choice of model.

Which local model writes VBA well

We found no published benchmark that measures how well open models write VBA, Power Query or Excel formulas, as of October 2026. The model cards list general coding tests: the card of Qwen3.8-27B, published by Qwen in August 2026 under the Apache 2.0 licence, reports 90.3 on LiveCodeBench v6 and 61.7 on SWE-bench Pro, and mentions no Excel or VBA task. Those are the vendor's own figures, measured on other programming work.

That leaves a practical test. Take three tasks from your department's daily work, for example a report macro, a messy import and a lookup formula, ask the model on the workstation, and check the results on a copy. We start from the 27B class (27 billion parameters), such as Qwen3.8-27B, as in our other guides to coding with local models; for formulas and short macros a smaller model such as gpt-oss-20b (Apache 2.0) may be enough. Our article on whether a local model is good enough for coding compares local and cloud models on the published tests.

A safe way to work, from the request to a tested macro

1. Describe the structure and leave out the real data. Name the sheets, the columns and their formats, and add three or four invented example rows. The macro needs the layout; real customer names do not help it.
2. Say what should happen in numbered steps. "Copy every row with status 'open' to the sheet 'Report', sorted by due date, then save as PDF in folder X" works better than "make me a report".
3. Ask for comments and a summary. Let the AI explain in plain words what the macro changes, deletes or overwrites, and ask it to stop with a message if a sheet is missing.
4. Paste and save. Insert the code as a module in the Visual Basic Editor and save the file as a macro-enabled workbook (.xlsm).
5. Test on a copy. Run the macro on a copy of the real file and compare the result with a case you know by heart.
6. Keep the version. Store the macro text with a short note on what it does and who asked for it.

German Excel adds one trap. In German, functions have German names (SUMME instead of SUM). VBA's FormulaLocal property works "in the language of the user": the documentation's example shows a SUM formula that appears as =SUMME(A1:A10) on a German installation, while the examples for the plain Formula property use English names and commas. When a macro writes formulas into cells, tell the AI which of the two it should use.

You can order the workstation alone, the setup of Ollama, the model and AnythingLLM in its MIT-licensed Docker version as the chat window alone, or both together: the workstation delivered with everything installed and configured; write to us and we suggest the best solution for your tasks and budget.

Macro security settings that stay switched on

AI-written macros are code like any other, and Excel's protections apply to them. The Office documentation, last updated on 17 July 2026, states that Office on Windows blocks VBA macros in files from the internet by default, which includes downloads and email attachments marked by Windows (the "Mark of the Web"). Such a file opens with a security banner and no option to enable the content. This matters when you email a macro workbook to a colleague: if Windows marks the attachment, the macros are blocked on their PC.

Four rules keep the protection in place:

• Keep Excel's Trust Center at "Disable VBA macros with notification" or stricter. The setting "Enable VBA macros" carries the note "not recommended, potentially dangerous code can run".
• Use Trusted Locations sparingly. The documentation recommends exactly that, and advises against setting network locations as Trusted Locations.
• Let IT decide how macro workbooks are shared. The Trust Center also offers "Disable VBA macros except digitally signed macros": macros signed by a trusted publisher run, unsigned ones stay disabled.
• Never enable macros in a file whose macros you do not know; the documentation says the same. A macro you received from someone else is checked like any other.

The AI itself is part of this. Ask it to explain any line that deletes files, sends mail or opens a connection to another system, and do not run code you do not understand.

Power Query, Python in Excel and local Python scripts

Python in Excel sends the work to the vendor's cloud. Its data-security page, read on 30 October 2026, states that the Python code runs in a secure container there; the code can reach only the data the formula references, has no network access, and the data is not persisted afterwards. For a company that wants spreadsheets to stay in the building, a Python script that runs on the workstation is the local route.

The AI can write such scripts with openpyxl, an MIT-licensed Python library that reads and writes xlsx and xlsm files. Its documentation has two warnings worth passing on to the AI in your request: openpyxl does not read every item of a workbook, so shapes are lost when a file is opened and saved under the same name, and an .xlsm file must be loaded with keep_vba=True, or Excel will not open the saved document. Let the script write to a new file.

How much GPU memory an Excel assistant needs

Memory decides what a workstation can do: the model and a working memory (the cache) for each request must fit on the graphics card. A token is a word or part of a word. The 4-bit and 8-bit versions of a model are compressed copies of it: the 8-bit version stays closer to the original, the 4-bit version needs less memory. Macro requests are short compared with programming whole applications, and Ollama's default context on a card with 24 to 48 GiB of memory is 32K tokens, enough for a sheet description, some example rows and the answer; on the 24 GB and 96 GB cards the setup sets the same value. Our users-per-card rule takes 90 per cent of the memory the driver reports, less 3 GiB per card, and that has to hold the model plus a cache for every request answered at the same moment. We count the cache in 16-bit, Ollama's default: for Qwen3.8-27B, a request of 32,000 tokens needs about 2.1 GiB, by our arithmetic from the model's configuration as in our Qwen hardware guide.

Our estimates by the users-per-card rule with usable memory of 18.5, 25.7, 40.0 and 83.0 GiB, Ollama's 4-bit (18 GB) and 8-bit (30 GB) files of Qwen3.8-27B and the 13.76 GB checkpoint of gpt-oss-20b, each request at 32,000 tokens with a 16-bit cache; not measurements. A Max-Q has the same 96 GB as the Workstation Edition.

On the entry tier, the 4-bit model (16.8 GiB) leaves 8.9 GiB, room for four requests at once. A request is a question being answered at that moment, and people in an office rarely ask in the same second. Ollama answers one request per model at a time until its OLLAMA_NUM_PARALLEL setting is raised, so that setting is part of the setup for several users. The RTX PRO 4000 tier runs the smaller gpt-oss-20b; the 27B model in 4-bit leaves it too little room for even one request of this size. On the department tier, vLLM, a serving program built for many users at once, holds 47 requests of 32,000 tokens by the same rule with Qwen's own 8-bit (FP8) version and an 8-bit cache, the figure our other Qwen3.8-27B guides use.

StorageReview's review of 14 October 2026 gives a speed reference: Qwen3.8-27B in NVIDIA's NVFP4 format, served with vLLM 0.27.1, wrote between 17 and 22 tokens per second for a single request on every card that ran it, from the RTX PRO 4500 Blackwell Server Edition and an L40S to an RTX PRO 6000 Blackwell Workstation Edition. A macro with an explanation of 1,000 tokens then takes about 45 to 60 seconds by our arithmetic. On your workstation the speed depends on the card's memory bandwidth, the model's format and the serving software.

We estimate power from the card, a 350 W processor and 15 per cent for the rest: about 0.6 kW at full load for a workstation with one RTX PRO 4500, 0.75 kW with one RTX PRO 5000 and 1.1 kW with one RTX PRO 6000 Workstation Edition, each on an ordinary 230 V office circuit.

The macro assistant as one task on a company AI server

Writing macros is a modest job for a language model, and a workstation dedicated to it alone is rarely the best use of the hardware. The same model answers questions about documents, drafts emails and summarises meetings, so the larger tier, or a company AI server with two RTX PRO 6000 Server Edition cards, can carry the macro assistant next to these tasks for several departments, as our guide to an AI server for companies of 10 to 100 people describes. For a shared setup, the chat window moves from the workstation to the network: AnythingLLM runs as a desktop app for one person, whose terms of use allow personal and internal business use, or as an MIT-licensed Docker server with user accounts for a team, as our comparison of AnythingLLM desktop and team server explains. On a single workstation, Ollama's own app for Windows and macOS, available since July 2025, gives one person a chat window directly.

A few points for whoever looks after the system: Ollama listens only on the local machine by default and has no login of its own, so a shared model server belongs behind the firewall and an application with user accounts, as our guide to moving from Ollama on a PC to a company AI server shows. Use Ollama 0.35.0 or later because of CVE-2026-103663. Ollama also offers cloud models and web search; the setting OLLAMA_NO_CLOUD=1 described in its FAQ removes both, so every request stays on the workstation. AnythingLLM collects anonymous usage data until telemetry is switched off with DISABLE_TELEMETRY, as its README explains. A rule in the firm's AI usage policy says which data may go into the chat and who checks macros before they are shared. If your team would rather build small web tools than macros, our guide to internal tools built with AI without developers covers that path, and IT departments that want a platform for forms and approvals find the options in our comparison of Power Apps alternatives with local AI.

Send us a typical Excel task from your department and the number of people who would use the assistant, and we propose the workstation or server for it.

FAQ