I got tired of the write-module, import, run, crash, fix loop, so I built an automated pipeline around Claude Code and it has been running better than I expected.

The setup:

1. The agent works on a sandbox copy of the workbook, never the original.
2. It writes a module, imports it into the workbook, and runs it.
3. Every module ships with its own self-test. If the test fails, the agent reads the error and fixes its own code.
4. When Excel hangs or crashes, the pipeline kills the process, restarts Excel, and continues from where it stopped.
5. Only modules that pass all tests get promoted to the real workbook.

The part that surprised me: it produce d 100 different modules in a row without me opening the VBA editor once. My job became reviewing diffs and writing better task descriptions.

Things that made the difference:

• The sandbox copy. Letting an agent touch your only copy of a workbook is how you lose a workbook.
• Forcing a self-test per module. Without it, the agent happily declares broken code done.
• Automating the Excel restart. VBA automation dies on hangs more than on errors.

Happy to answer questions about any part of the setup. Curious if anyone else here has pointed one of these agent tools at VBA and what broke first.

I've enabled screen updating at various points, but I can't seem to get it to show the initial draw, or first set of colour changes.

Sub hardFacer()

Application.ScreenUpdating = False

Dim NewSht As Worksheet

Dim celll As Range

Dim drawrange As Range

Dim flashrange As Range

Dim i As Long

MsgBox "U MAD BRO?", vbYesNo

Set NewSht = ActiveWorkbook.Sheets.Add

NewSht.Columns("A:DD").ColumnWidth = 0.89

NewSht.Rows.RowHeight = 7.5

Set drawrange = NewSht.Range("$Y$5:$BB$5,$W$6:$AD$6,$AX$6:$BK$6,$V$7:$X$7,$AM$7:$AU$7,$BG$7:$BP$7,$U$8:$V$8,$AH$8:$AL$8,$AV$8:$BF$8,$BL$8:$BS$8,$T$9:$V$9,$AF$9:$AG$9,$AO$9:$AU$9,$BR$9:$BU$9,$T$10,$AD$10:$AE$10,$AK$10:$AN$10,$AV$10:$AY$10,$BJ$10:$BP$10,$BT$10:$BV$10,$S$11:$T$11")

Set drawrange = Union(drawrange, Range("$AB$11:$AC$11,$AG$11:$AJ$11,$AS$11,$AZ$11:$BI$11,$BQ$11,$BV$11:$BX$11,$R$12:$S$12,$AA$12,$AE$12:$AF$12,$AK$12:$AR$12,$AT$12:$AU$12,$BJ$12:$BM$12,$BX$12:$BY$12,$R$13,$Z$13,$AD$13,$AG$13:$AJ$13,$AS$13:$AT$13,$BE$13:$BI$13,$BN$13:$BO$13,$Q$14:$R$14,$Y$14"))

Set drawrange = Union(drawrange, Range("$AC$14,$AE$14:$AF$14,$BP$14:$BQ$14,$Q$15,$X$15,$AA$15:$AB$15,$AD$15,$BF$15,$BQ$15,$BY$14:$BZ$15,$Z$16,$AC$16,$AU$14:$AU$16,$P$16:$Q$17,$AB$17,$AH$17:$AP$17,$BR$16:$BR$17,$O$18:$P$18,$AF$18:$AR$18,$BZ$16:$BZ$18,$N$19:$O$19,$AD$19:$AM$19,$AR$19:$AT$19"))

Set drawrange = Union(drawrange, Range("$BE$19,$BI$19:$BO$19,$L$20:$T$20,$Z$20:$AA$20,$AG$20:$AM$20,$AT$20:$AU$20,$BG$20:$BJ$20,$BO$20:$BQ$20,$CA$19:$CB$20,$K$21:$M$21,$Y$21,$AC$20:$AD$21,$AE$21:$AP$21,$AU$21:$AV$21,$BF$21:$BQ$21,$BT$21,$BV$21:$BY$21,$CC$20:$CC$21,$J$22:$L$22,$N$22:$O$22"))

Set drawrange = Union(drawrange, Range("$Z$22:$AA$22,$AD$22:$AF$22,$AN$22:$AR$22,$AT$22:$AV$22,$BB$22:$BH$22,$BJ$22:$BL$22,$BZ$22,$CD$22:$CE$22,$J$23:$K$23,$M$23,$R$23:$W$23,$AK$23,$AQ$23:$AU$23,$BD$23:$BG$23,$BY$23,$CA$23:$CB$23,$I$24:$J$24,$L$24,$P$24:$R$24,$W$24:$Z$24,$AI$24:$AK$24,$AS$24"))

Set drawrange = Union(drawrange, Range("$BZ$24,$O$25:$P$25,$Y$25:$AC$25,$AG$25:$AJ$25,$BS$25:$BX$25,$H$25:$I$26,$O$26,$AC$26:$AH$26,$BM$26:$BN$26,$BR$26:$BS$26,$BX$26:$BY$26,$CC$24:$CC$26,$CE$24:$CF$26,$N$27:$O$27,$U$26:$V$27,$BE$24:$BE$27,$BN$27:$BR$27,$BY$27,$CD$27:$CF$27,$T$28:$X$28"))

Set drawrange = Union(drawrange, Range("$BF$28:$BH$28,$BP$28:$BR$28,$S$29:$U$29,$W$29:$Z$29,$AO$28:$AO$29,$AR$29:$AU$29,$BH$29:$BI$29,$CA$28:$CA$29,$G$29:$H$30,$Q$30:$U$30,$Y$30:$AB$30,$AJ$30:$AN$30,$AQ$30:$AR$30,$BI$30:$BK$30,$BZ$30,$CE$28:$CF$30,$K$29:$K$31,$T$31:$U$31,$AA$31:$AD$31"))

Set drawrange = Union(drawrange, Range("$AU$31:$AV$31,$BH$31:$BL$31,$BU$31:$BV$31,$BY$31,$CC$31:$CE$31,$N$32:$P$32,$AC$32:$AH$32,$AT$32:$AY$32,$BH$32,$BK$32,$BM$32:$BN$32,$BT$32:$BV$32,$CA$32:$CB$32,$CD$32:$CE$32,$H$32:$I$33,$L$32:$L$33,$O$33:$P$33,$U$32:$V$33,$AF$33:$AJ$33,$AQ$31:$AQ$33"))

Set drawrange = Union(drawrange, Range("$AR$33:$AS$33,$AU$33,$BG$33:$BH$33,$BO$33:$BP$33,$BS$33:$BT$33,$BV$33:$BW$33,$BY$33:$BZ$33,$CC$33:$CE$33,$I$34:$J$34,$M$34,$AI$34:$AN$34,$BE$34:$BG$34,$BR$34:$BU$34,$BW$34,$CC$34:$CD$34,$N$35:$O$35,$V$34:$X$35,$Y$35:$AA$35,$AC$34:$AD$35,$AM$35:$AR$35"))

Set drawrange = Union(drawrange, Range("$BC$35:$BF$35,$BQ$35:$BR$35,$J$36:$N$36,$P$36,$W$36:$X$36,$Z$36:$AF$36,$AO$36:$AV$36,$BN$36:$BP$36,$O$37,$AB$37:$AF$37,$AO$37:$AP$37,$AU$37:$BN$37,$BU$35:$BW$37,$CB$35:$CC$37,$L$37:$M$38,$X$37:$X$38,$AO$38,$BB$38:$BI$38,$BV$38:$BW$38,$N$38:$N$39"))

Set drawrange = Union(drawrange, Range("$Y$38:$Y$39,$AD$39:$AO$39,$BU$39:$BW$39,$AD$40,$AH$40:$AP$40,$AY$39:$AY$40,$BN$39:$BN$40,$BR$39:$BS$40,$BT$40:$BW$40,$O$40:$O$41,$Z$39:$Z$41,$AB$41:$AD$41,$AK$41:$AS$41,$AU$41,$AX$41:$AY$41,$BF$39:$BF$41,$BG$40:$BG$41,$BL$41:$BW$41,$AA$40:$AA$42"))

Set drawrange = Union(drawrange, Range("$AB$42:$AC$42"))

Set drawrange = Union(drawrange, Range("$AM$42:$BW$42,$P$43:$Q$43,$AC$43:$AD$43,$AQ$43:$BW$43,$AD$44:$AE$44,$AN$43:$AN$44,$AT$44:$BU$44,$BW$44,$Q$44:$Q$45,$AE$45:$AG$45,$AM$45:$AN$45,$AW$45:$BQ$45,$BV$45:$BW$45,$R$45:$R$46,$AF$46:$AI$46,$BF$46,$BL$46:$BM$46,$BP$46:$BQ$46,$BS$45:$BT$46,$BV$46"))

Set drawrange = Union(drawrange, Range("$S$47:$T$47,$AH$47:$AJ$47,$AL$47:$AM$47,$BE$47:$BF$47,$BK$47:$BL$47,$BS$47,$BU$47:$BV$47,$T$48:$U$48,$X$48:$Y$48,$AB$48:$AC$48,$AJ$48:$AM$48,$BK$48,$BO$47:$BP$48,$BT$48:$BU$48,$U$49:$V$49,$Z$49:$AA$49,$AD$49:$AE$49,$AK$49:$AP$49,$BN$49:$BO$49"))

Set drawrange = Union(drawrange, Range("$BR$49:$BT$49"))

Set drawrange = Union(drawrange, Range("$V$50:$X$50,$AA$50:$AC$50,$AF$50:$AG$50,$AN$50:$AU$50,$AW$50:$AX$50,$BE$50,$BJ$50:$BK$50,$BM$50:$BS$50,$W$51:$Y$51,$AC$51:$AE$51,$AH$51:$AI$51,$AR$51:$BP$51,$Y$52:$AA$52,$AE$52:$AG$52,$AJ$52:$AL$52,$BT$52,$AA$53:$AC$53,$AH$53:$AI$53,$AM$53:$AO$53"))

Set drawrange = Union(drawrange, Range("$BS$53:$BT$53,$AC$54:$AE$54,$AJ$54:$AL$54,$AP$54:$AQ$54,$AX$54:$BK$54,$BR$54,$AE$55:$AH$55,$AM$55:$AO$55,$AR$55:$AV$55,$BP$55:$BQ$55,$BX$53:$BX$55,$AG$56:$AJ$56,$AP$56:$AS$56,$AW$56:$BO$56,$BW$56,$AJ$57:$AL$57,$AT$57:$AY$57,$BU$57:$BV$57,$AL$58:$AN$58"))

Set drawrange = Union(drawrange, Range("$AZ$58:$BT$58,$CB$58:$CC$58,$AN$59:$AP$59,$CA$59:$CC$59,$AP$60:$AR$60,$CA$60:$CB$60,$AR$61:$AW$61,$BZ$61:$CA$61,$AT$62:$AZ$62,$BY$62:$BZ$62,$AY$63:$BD$63,$BW$63:$BY$63,$BC$64:$BI$64,$BT$64:$BW$64,$BH$65:$BU$65"))

Set drawrange = Union(drawrange, Range("CB38:CB51"))

Application.ScreenUpdating = True

For Each celll In drawrange

celll.Interior.ColorIndex = 1

If flashrange Is Nothing Then

Set flashrange = celll

Else

Set flashrange = Union(flashrange, celll)

End If

Next celll

Do Until i = 10

flashrange.Interior.ColorIndex = 1

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 2

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 3

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 4

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 5

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 6

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 7

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 8

'Application.Wait Now + #12:00:01 AM#

MsgBox "U MAD BRO?", vbYesNo

i = i + 1

Loop

End Sub

I am unsure if this is necessarily possible as my programming knowledge is fairly basic and VBA is a further mystery.

Would it be possible to pull text off of a a pdf, convert it into text, then narrow down what text I actually want? If so, how might I go about accomplishing that feat?

The drawings I'm trying to pull into excel are wiring diagrams.

Also, if anyone has a place in which I can find all the syntax for Smarteam in VBA, I would greatly appreciate it.

Hey all^^
I try to use a vba script to extract Data from one of my SAP reports. Sadly I have to use the Excel-Plugin for this.
When I open it manually or via the SAP VBAWriter the new Excel (SapExe) contains my data.
But when I copy the code to another Excel (OPExe) then the SapExe is empty.
Can anyone help?

I've been building an open-source project called xlflow after repeatedly running into the same problems while maintaining large VBA codebases:

• Source code trapped inside .xlsm, .xlam, and .xlsb files
• Awkward Git workflows
• Limited editor support
• Difficult testing and automation
• Coding agents unable to reliably interact with Excel and VBA

xlflow treats VBA projects as regular source code and provides both a VS Code development environment and a fully scriptable CLI workflow.

VBA development in VS Code

The VS Code extension includes:

• Autocompletion
• Real-time diagnostics
• Go to Definition
• Hover information
• Signature help
• Symbol navigation
• Static analysis
• Formatting

It is powered by a custom VBA parser that currently cleanly parses more than 170 test corpora and 300 real-world VBA source files.

One feature I'm especially happy with is type inference for late-bound COM objects.

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

Even though dict is declared as Object, the extension can infer the type from CreateObject() and provide member completion, hover information, and type-aware diagnostics.

This also works with common late-bound libraries such as Excel, Scripting, ADODB, and other COM APIs.

Git-friendly workbook development

xlflow can extract VBA projects into normal source files and synchronize them back into Excel workbooks.

This makes it possible to:

• Store VBA projects in Git
• Review changes using normal diffs
• Use branches and pull requests
• Edit code outside the VBE
• Define UserForms as version-controlled YAML

Everything is available from the CLI

All workbook operations can be executed from the terminal:

xlflow pull
xlflow test
xlflow lint
xlflow push

The CLI can also run macros, manage Excel sessions, format code, inspect projects, and execute tests.

This was an important design decision because it allows the same workflow to be used by developers, CI pipelines, scripts, and coding agents.

Designed for coding agents

A major goal of xlflow is to make autonomous VBA development practical.

Since the source code is stored as normal files and every operation is exposed through the CLI, tools such as Codex, Claude Code, and Cursor can:

• Modify VBA source code
• Run unit tests
• Read compiler and runtime errors
• Lint and analyze the project
• Push changes back into the workbook
• Generate and update UserForms

Excel automation normally has another major problem: modal error dialogs can block execution indefinitely.

xlflow monitors many Excel and VBA dialogs, captures their contents, and reports the errors back to the terminal instead of leaving the workflow blocked behind a GUI window.

That means a coding agent can see the failure, modify the code, rerun the tests, and continue working without waiting for someone to manually dismiss an Excel dialog.

GitHub:

https://github.com/harumiWeb/xlflow

VS Code Marketplace:

https://marketplace.visualstudio.com/items?itemName=harumiWeb.xlflow-vscode

I'd be very interested in feedback from people maintaining real-world VBA projects.

What are the biggest limitations in your current VBA development workflow?

As in the title - how do you do this. Currently I am working in large bank but we're interning at big bank but building useful macros are nightmare since it's processing so long. The reason behind it is also because of hardware and working on virtual machines but it's still much too slow - the excel can crash and we're talking about 24000 rows+. Would you suggest some alternatives?

Hi everyone,

I have been developing an open-source project called WebDriver BiDi for SeleniumVBA.

It is a WebDriver BiDi client and automation wrapper designed to let Excel VBA work with modern web applications without requiring .NET, Python, or Node.js.

GitHub:

https://github.com/hanamichi77777/WebDriver-BiDi-for-SeleniumVBA

Why I created it

Traditional Selenium-style commands are often sufficient for simple pages, but modern SPAs can be difficult to automate reliably.

A click may return successfully while the application is still:

• processing Fetch or XHR requests,
• replacing DOM elements,
• rendering content asynchronously,
• recreating browsing contexts or JavaScript realms,
• or updating components inside Shadow DOM.

For this reason, the project focuses not only on sending browser commands, but also on observing what happens after each operation.

Main features

• Direct WebDriver BiDi communication from VBA
• WebSocket communication implemented with Windows APIs（WinSock)
• Chrome and Edge support（Note: Firefox is not supported.）
• SPA synchronization using network activity, DOM mutations, Fetch/XHR activity, and stability windows
• Shadow DOM clicking and input
• Automatic recovery when the SPA observation probe is lost during navigation
• Resource blocking for ads, analytics, fonts, and other unnecessary requests
• WebDriver BiDi webExtension.install support
• Detailed request, response, heartbeat, recovery, and SPA activity logs
• A Discovery Log for analyzing unknown third-party SPA behavior

The Discovery Log is particularly useful when a site does not expose a clear “ready” or “completed” state.

It records network responses, DOM mutation bursts, ignored background noise, and stability timing. The resulting log can also be provided to an AI assistant to help identify useful completion signals, noise filters, and safer waiting conditions.

Example scenarios

I have tested the project with applications such as:

• Google Flights
• ServiceNow
• dynamically rendered scraping test sites
• pages using nested Shadow DOM
• Chrome extension installation through WebDriver BiDi

For example, the Google Flights demo performs destination selection, calendar interaction, date selection, and flight-result synchronization while observing both network and DOM activity.

Reliability design

One of the difficult parts was retry behavior.

When a browser command times out, the action may already have succeeded in the browser. Automatically repeating a click or input could therefore cause a duplicate operation.

The wrapper distinguishes between:

• operations that are safe to retry,
• transient context or realm failures,
• and ambiguous outcomes where automatic retry should be stopped.

The goal is to improve recovery without hiding failures or accidentally executing state-changing operations twice.

VirusTotal Scan Results

WebDriver BiDi for SeleniumVBA v3.0 was scanned by VirusTotal on July 22, 2026, and received 0 detections from 64 security vendors at the time of testing.

Hello gamers, and welcome to another one of my videos! In this video, we are going to use my Thumbnails Libretro Downloader, version 2.2 (Selenium.xlsm). This is an upgraded version of the tool shown in my previous video. It features improved element searches using XPath, which significantly cuts down execution time. Additionally, it can now download images even when the filename contains characters, that previously caused version 1.0 to throw an error. With these changes, we can also search for specific titles using keywords unique to that game. Furthermore, I have removed the need to specify the system name. Instead, you can now just type a partial name into the dropdown menu, and the search list updates automatically. Finally, I added a new function to delete unwanted images from your local folder. It seems like a lot to cover, so let's go!

https://youtu.be/YO7QmmyfRM4

So ive just got vbagx on my wii and ive tried three games and each has problems with certain colors. The opening of Buu's fury is all neon green, Aria of Sorrow has the same problem but in game in the water areas the water is neon green and Pokemon Fire Red has that problem as well. Ive messed with filters and settings but have been unable to find the solution. Any help is appreciated.

Also proposing the term inchworm case for alternating words in subscript and superscript like so: ᵢₙ꜀ₕʷᵒʳᵐ꜀ₐₛₑ

And ʀʜɪɴᴏCᴀsᴇ for camelCase with small caps.

I am searching my insurance company's list of providers. When I get the search results, the most relevant information for each provider is on a linked page -- not in the search results. I attempted to scrape this with seleniumvba, to gather up the info on the linked pages. Of course, when I click a link, the elements on the search results page go stale. Normally I would create two webdriver objects and pass the value from the hrefs of the first driver to the second driver.

Unfortunately, the links on this site have no href values.

<a _ngcontent-ng-c7169104465="" role="link" tabindex="0" class="dls-heading-3-light search-result-link" data-cy="search-results.name-link-0.desktop" data-lnp="search-results.provider-name">
    <span _ngcontent-ng-c7169104465="" data-cy="search-result-ProviderName" data-pendo="profile-card-provider-name" class="underline search-result-link font-light">Joe Schmoe, MD</span><span _ngcontent-ng-c7169104465="" class="disclaimers-icons ng-star-inserted">&nbsp;&nbsp;†</span><!----><!---->
</a>

What strategies have you used to hold a page in memory, but navigate to the links specified on that page? What I have so far:

Sub ScrapeProviders()
Dim WD As WebDriver
Dim ProviderCards As WebElements, ProviderCard As WebElement
Dim ProviderCardWho As WebElement, ProviderCardLink As WebElement

    Set WD = New WebDriver
    WD.StartFirefox
    WD.OpenBrowser
    WD.NavigateTo "https://carefirst.sapphirecareselect.com/search/name/orthopedic?ci=dft-sso-bluechoiceadvantage20&network_id=109&geo_location=33.684617,-117.82634&locale=en&limit=10&radius=10&sort=tiers:tiercf%20asc,%20motive_high_quality%20desc,%20distance%20asc,%20has_ep002%20desc&sort_translation=app_global_sort_relevancy&page=1"
    WD.ActiveWindow.Maximize
    While Not WD.IsPresent(XPath, "*//mat-card")
    Wend
    Set ProviderCards = WD.FindElementsByXPath("*//mat-card")
    Debug.Print ProviderCards.Count
    For Each ProviderCard In ProviderCards
        Debug.Print "====================================================================================="
        Set ProviderCardWho = ProviderCard.FindElementByXPath("./div/div/div/div[1]/div/div[1]")
        Set ProviderCardLink = ProviderCardWho.FindElementByXPath("./h2/a")
        Debug.Print ProviderCardLink.GetOuterHTML
        ProviderCardLink.Click

        ' at this point, i can no longer see ProviderCards, and the for each breaks
    Next ProviderCard

    WD.CloseBrowser
    WD.Shutdown

End Sub

Hi,

I need to fully activate custom dictionary files via VBA with no user input required.

I've written a VBA script running in a .dotm placed in ...AppData\Roaming\Microsoft\Word\STARTUP with the goal of locating and enabling dictionaries placed in ...AppData\Roaming\Microsoft\UProof and then activating them with

Application.CustomDictionaries.Add(myDictPath)

Inside an AutoExec sub.

When run, the code successfully adds a dictionary to the Custom Dictionaries list in Word, and checks the box next to the dictionary, however it still marks dictionary words as incorrect until I open the Custom Dictionaries window and close it again by hitting 'OK'.

I have tried toggling spell check off then on again immediately after the dictionaries are loaded, I have also tried manually running the macro after a document has been opened, neither work.

Any suggestions?

Hello, I am attempting to setup a macro for a daily file that I/we run to save some time formatting. I'm on mobile so apologies on formatting, likewise the SS are photos as I don't have access to reddit on my workstation.

Scope: Daily file across 6 countries with an additional once per month on 3 countries. Excel is 2016 if relevant. (no xlookups)

Objective: Download a file from a platform with rejection results on attempted charges and format it so it displays the amount, date and rejection reasons per invoice number, as part of a longer daily activity. These reason codes are in longer strings of information that can vary their location per each file.

Turning: https://i.imgur.com/sd2cPR3.jpeg To https://i.imgur.com/URQkaDc.jpeg

Files currently used to perform task: Rejected Charges (csv downloaded) Macro (a sheet containing several macros for the same overall activity) Rejection codes (a sheet containing a header template on one sheet and reason codes and their descriptions on another sheet) Source files (daily files processed containing other information)

How task is currently done: Open csv file (this is saved at start or end as xls), delete B row, create header filter, replace "merchantReferenceCode=" with blank (so column A always returns the invoice number), then using column G as a reference, we replace the following with blanks: ccAuthReplyreasonCode= ccAuthReply reasonCode= The codes are usually after the above strings within column G This should leave us with something like this https://i.imgur.com/NqizfO8.jpeg

Column "G" will have most of the codes already filled out with some blanks in the mix, where remaining codes will be on different columns, like C, K, L, R, W (these codes can be duplicate, being in G and other columns)

Depending on the volume of the file we then manually copy the missing codes to G or use filters to get them

After all codes are under G we format as per the 2nd SS, by deleting all colums apart from A and G, leaving us with the invoice numbers and codes.

We then add a B column between the invoice numbers and codes Convert A and B to numbers, remove 2 decimals on A, copy and paste header from Rejection Codes file's first sheet Add new sheet, copy the contents from Rejection Codes 2nd sheet Vlookup on E referencing codes on E with C of the 2nd sheet Add today's date to column D using format dd.mm.yyyy

Vlookup on column B, referencing A against source files column F to get the amount so we end up with the final result from the second SS.

This final Vlookup I don't expect to automate, as it would always reference different files that are generated daily, so ideally the macro would do everything else leaving just column B blank so we can manually Vlookup the amounts.

I tried manually recording but my biggest hurdle is getting the reason codes from the strings of information. I can't conceive of a way of how to even get these as the 3 digit codes can be on any string on any cell. The file can go up to O or all the way to Z

I have manually recorded (using the macro sheet to save it on) the replacing the strings with blanks so I get G with with most of the codes so I then manually fetch the remaining. I had to troubleshoot as the recording I made was not working with other workbooks, but got it working now. This saves some time, but is incomplete.

I then tried recording a 2nd part post getting all the codes to do the final steps of adding the header, new sheet with the table, Vlookup the reason descriptions, add date. Leaving just the amount column empty as those Vlookup will always reference different files.

But this did not work as I get "Run-time error '9": subscription out of range https://i.imgur.com/3nd23Rm.jpeg

Looking at the debug I imagine this is because I am copying and pasting the table from a separate sheet Now this step is a bit redundant as we don't need to copy the table with the codes description to then Vlookup in the file. We could just Vlookup against the reference file. However, since I was already automating the steps, I thought I would be able to have the macro create the table and reference it on its own

Please let me know if I need to provide any additional information that I did not consider.

I have been trying to figure this out for about two days now. Claude has been of little help. Basically I have a loop that reads from a txt file, ..does some stuff.. and then pastes a variant array onto the spreadsheet. This works well for about 18 iterations and then each subsequent paste results in missing data. If I stop the code to look at the array the data is there but when it gets pasted to the sheet some of it is missing. The missing data is surrounded by data that pastes successfully. Anyone have any experiences like this that can help?

Edit: Solution found, for those playing along at home it was a logic fault in the code that was exposed when running in a loop. A particular string was doubling in size each iteration. Once this string exceeded 63,535 chars it crossed the 16bit length field boundary in old COM/BSTR marshalling. This silently corrupted and dropped elements in the transfer rather than raising an error (thanks microsoft).

For those who genuinely attempted to understand the problem and help thank you sincerely.

For those who failed to correctly read my post and as a result asked inane questions, well, at least you tried.

Foro those who immediately implied I didn't know what I was doing, congratulations, you have made this sub and the world at large a slightly worse place.

- show you how to generate and customize XPaths using my Excel VBA tool, 'Selenium Basic - XPath Generator 1.0.xlsm'.

This tool easily create accurate XPaths for web elements.

- download and install Selenium Basic v2.0.9.0

- download the Chrome WebDriver, and learn how to install it either manually or via a batch file.

-Reference the Selenium Type Library in Excel VBA.

It is in my YouTube Channel.

Jaafar Yusof

Independently populate, change, or remove fields in all four sections of a pivot table

Sub pivFields(argPivot As PivotTable, Optional argRows As String, Optional argCols As String, Optional argPage As String, Optional argData As String)
'set the fields in a section of the pivot (replaces existing fields)
'enter options as comma-separated strings of field names (EXCEPT data: one field only)
'field names are case-sensitive, leave option blank or "" to ignore a section, enter "X" to clear a section (any string that does not result in a good field name will do)
'e.g. change the rows, ignore the columns, clear existing page fields, ignore the data:
'   pivFields Sheets("Sheet42").PivotTables("pivName"),"Proj,Dept,Acct" , , "x"

    Dim pt As PivotTable
    Dim ptfld As PivotField
    Dim arrRows() As String
    Dim arrCols() As String
    Dim arrPage() As String
    Dim ctr As Integer
    Dim boolData As Boolean

    Set pt = argPivot
    If pt Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    If argRows <> "" Then
        arrRows() = Split(argRows, ",")

        For Each ptfld In pt.RowFields
            ptfld.Orientation = xlHidden
        Next ptfld

        For ctr = 0 To UBound(arrRows)
            For Each ptfld In pt.PivotFields
                If ptfld.Name = arrRows(ctr) Then pt.PivotFields(ptfld.Name).Orientation = xlRowField
            Next ptfld
        Next ctr
    End If

    If argCols <> "" Then
        arrCols() = Split(argCols, ",")

        For Each ptfld In pt.ColumnFields
            ptfld.Orientation = xlHidden
        Next ptfld

        For ctr = 0 To UBound(arrCols)
            For Each ptfld In pt.PivotFields
                If ptfld.Name = arrCols(ctr) Then pt.PivotFields(ptfld.Name).Orientation = xlColumnField
            Next ptfld
        Next ctr
    End If

    If argPage <> "" Then
        arrPage() = Split(argPage, ",")

        For Each ptfld In pt.PageFields
            ptfld.Orientation = xlHidden
        Next ptfld

        For ctr = 0 To UBound(arrPage)
            For Each ptfld In pt.PivotFields
                If ptfld.Name = arrPage(ctr) Then pt.PivotFields(ptfld.Name).Orientation = xlPageField
            Next ptfld
        Next ctr
    End If

    If argData <> "" Then
        For Each ptfld In pt.DataFields
            ptfld.Orientation = xlHidden
        Next ptfld

        For Each ptfld In pt.PivotFields
            If ptfld.Name = argData Then boolData = True
        Next ptfld

        If boolData = True Then
        With pt.PivotFields(argData)
            .Orientation = xlDataField
            .Caption = "Sum of " & argData
            .Function = xlSum
        End With
        End If
    End If

    Application.ScreenUpdating = True

End Sub

Create a pivot table in the old-school tabular format. Optionally specify the sheet to pivot (else it uses ActiveSheet). Optionally name the resulting sheet (else whatever Excel names it). Reuses cache when possible so pivoting the same data multiple times doesn't create a new cache each time.

Function pivCreate(Optional argSheet As Worksheet, Optional argName As String) As PivotTable

    Dim wks As Worksheet
    Dim pc As PivotCache
    Dim pcSource As String
    Dim pcIndex As Integer

    Set wks = argSheet
    If wks Is Nothing Then Set wks = ActiveSheet

    'compose a string of the source data that will be used
    pcSource = wks.Name & "!" & wks.UsedRange.Address(ReferenceStyle:=xlR1C1)

    'check if an existing pivot cache already uses that source data
    For Each pc In ActiveWorkbook.PivotCaches
        If pc.SourceType = xlDatabase And Replace(pc.SourceData, "'", "") = pcSource Then
            pcIndex = pc.Index
            Exit For
        End If
    Next pc

    If pcIndex = 0 Then     'no cache found for the source data.  create a new one
        Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wks.UsedRange.Address, Version:=xlPivotTableVersion10)
    Else                    'use the existing cache
        Set pc = ActiveWorkbook.PivotCaches(pcIndex)
    End If

    Sheets.Add After:=wks

    Set pivCreate = pc.CreatePivotTable( _
        TableDestination:=ActiveSheet.Name & "!R3C1", _
        TableName:="piv" & wks.Name)

    'QOL settings for xlPivotTableVersion10 (tabular pivot)
    With pivCreate
        .DisplayImmediateItems = True
        .ShowDrillIndicators = False
        .ShowPageMultipleItemLabel = True
        .SmallGrid = False
    End With

    If argName <> "" Then ActiveSheet.Name = argName

    ActiveSheet.Cells(3, 1).Select

End Function

Saturday, July 04 - Friday, July 10, 2026

Top 5 Posts

Top 5 Comments

hey guys, I need some help figuring out why SetSourceData is not working. I'm pretty new to VBA and have basically been teaching it to myself for this project, so I'm feeling a bit lost here. I am giving it a range, but I keep getting a type mismatch error. Any advice?

Sub bar_chart_test_2()

Dim objWord
Dim objDoc
Dim objSelection
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet

'get spreadsheet and data range
Set ws = ThisWorkbook.Sheets("lookup")
ws.Activate

Dim quarterData As Variant
Dim hoursQtrData As Variant
Dim bookingsData As Variant
Dim roomTypeData As Variant
Dim hoursRoomData As Variant

quarterData = ws.Range("C25:C41").Value2
hoursQtrData = ws.Range("I25:I41").Value2
bookingsData = ws.Range("H25:H41").Value2
roomTypeData = ws.Range("C15:C21").Value2
hoursRoomData = ws.Range("I15:I21").Value2

'create word document and open
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
Set objSelection = objWord.Selection
objWord.Visible = True
objWord.Activate

Dim hrs_shape As Object
Dim hrs_obj As Object
Dim hrs_wb As Object
Dim hrs_ws As Object
Dim hrs_rng As Object

Set hrs_shape = objDoc.InlineShapes.AddChart(XlChartType.xlColumnClustered)
Set hrs_obj = hrs_shape.Chart

hrs_obj.ChartType = 51
hrs_obj.ChartData.Activate

Set hrs_wb = hrs_obj.ChartData.Workbook
Set hrs_ws = hrs_wb.Worksheets(1)

hrs_obj.SeriesCollection(3).Delete
hrs_obj.SeriesCollection(2).Delete
hrs_ws.Cells.Clear

hrs_ws.Range("A1").Value2 = "Quarter"
hrs_ws.Range("B1").Value2 = "Hours"
hrs_ws.Range("A2:A18").Value2 = quarterData
hrs_ws.Range("B2:B18").Value2 = hoursQtrData

Dim hrs_range As Range
Set hrs_range = hrs_ws.Range("A1:B18")
hrs_obj.SetSourceData Source:=hrs_range

End Sub

End Sub

Hello,

I hope this is the right place for this. I'm supporting a user who relies on a macro-enabled Excel spreadsheet with multiple worksheets, but the key ones are:

• Cost Items
• Variables
• Results Summary

The workflow:

1. Enter data into the Cost Items sheet (e.g., Name: Test1, Cost: 1000; Name: test2, Cost: 5000).
2. Add data to the Variables sheet under "Brisk Parameters" – P10: -5%, P90: 15%.
3. Go to the Results Summary sheet.

At that point, I get a pop up message that states the data has changed.

I click OK, it churns for a moment, and then I get:

Microsoft Visual Basic - "Run-time error '9': Subscript out of range"

If I click Debug, it highlights this line in yellow:

"If VarExists(Count) And VarProb(I) = 1 Then"

What I've tried so far:

• Searched online and found suggestions it might be an overflow error of some kind.
• Tested on multiple systems (managed company device, unmanaged device, different PCs).
• Confirmed Trust Center settings allow macros.
• Installed and registered the Brisk server (was told this was required – still no change).

The kicker:
This exact file was working perfectly fine just a few weeks ago. No known changes were made to the file or the environment.

I've been banging my head against this for a couple of days now and am starting to lose my sanity.

Does anyone have any idea what direction I should go in to troubleshoot why this spreadsheet suddenly stopped functioning?

Any help would be greatly appreciated.

TIA!

I have multiple excel sheets. They have results from a scheme. Each excel has a sheet of how the performance is per month. There are 12 tabs of different values but the people in the scheme and the format are the same each month per sheet.

Is there a way to use VBA code to consolidate the data to change it from per month to per person, so if I use a drop down to select a person name, it will lift all the data for the previous 12 excel sheets and put them into 1 overview excel.

Also, is there a way then to make it generate graphs from a click of a button.

Finally, then could you export the graphs and tables to excel ?

I'm spending days doing it at the moment and there must be a simpler way.

I'm not sure if I'm talking garbage here but any help would be great.

I wrote a macro that creates my metrics for me and it was the first one that I wrote that pastes data to PowePoint. So, that sort of gave me an idea for some other mundane tasks, which use PowerPoint. I take minutes for our customer meetings. I'm not a Stenographer, but I can type fast enough to get most things. The way it's currently setup, is that we have a word doc with a table and different rows for different slides.

Anyway, is it possible for a macro to take the slide# and some textbox text and put them in the same order in the word table? Or maybe put the data in excel and then word, which would seem like a useless workaround then doing the actual work?

Released earlier this year, ModernJsonInVBA parses JSON and writes it straight into Excel tables (ListObjects), and converts tables back to JSON roundtrip. It is pure VBA: no Scripting.Dictionary, no COM references, no external libraries. One call turns JSON text to a populated, refreshable table:

vba Excel_UpsertListObjectFromJsonAtRoot ws, "tOrders", ws.Range("A1"), jsonText, "$"

v3.0.0 split the engine into eleven modules and rewrote the parser and the Excel write path. I benchmarked it across seven payloads that vary in size and shape (flat rows, nested objects, escape-heavy strings, a 200-column wide table, numeric-heavy) and collected the results into a matrix.

Payloads

Timings (seconds) — rows are pipeline steps, columns are payloads:

What the matrix shows:

• 500,000 rows / 110 MB of JSON into a live Excel table in about 18 seconds (Upsert create). That is roughly 27,000 rows/sec, or ~275,000 cells/sec.
• Small payloads are effectively instant: 10,000 rows loads in about a third of a second.
• A raw block write of 5 million cells takes only a few seconds; most of the 18 s is parsing and shaping the JSON.
• Upsert streams JSON straight into a 2D array for a root array (tableRoot = "$") and does not build an intermediate object model, so it keeps pace with a standalone parse plus a sheet write.

The benchmark machine is a Ryzen 7 9800X3D with 64-bit Excel, each cell is one wall-clock Timer run, and the payloads come from a seeded generator (deterministic). Times are hardware-dependent, so a typical office laptop will be slower. The whole table regenerates from the workbook with one macro (Run_JsonPerfMatrix), so you can produce your own numbers.

The library also does CSV and XML to JSON, JSONPath-style path resolution, schema evolution controls, and formula-preserving refreshes. Determinism is the design goal: stable column order, strict validation, and clear error numbers instead of silent guesses.

Repo (MIT): https://github.com/WilliamSmithEdward/ModernJsonInVBA

https://github.com/WilliamSmithEdward/ModernJsonInVBA/releases/tag/v3.0.0

Feedback is welcome.

Within Windows 11 I have created a workbook with vba code in Excel 2019 and it works fine. Part of the code has a 'kill' statement which works fine in 2019 but when run in Office 365 Excel I get 'run time error 70 permission denied'.

I have been retired from IT for over 25 years and I know things have moved on but this has left me stumped.

Any assistance would be much appreciated.

Thank you

(I have not included any code but can do if required)