Any VBA experts out there?

[Nigel_O]

I'm fairly handy in Excel and many years (25+) ago, I built a tool for financial advisers. I employed a programmer to write the VBA, as that was beyond me. The workbook has many thousands of lines of VBA. I couldn't write it to save my life, but I have enough of an idea what's going on to navigate my way around it.

I now only have a very small number of advisers using the tool - I'm keeping it alive simply because they rely on it, but I accept the fact that an MS update will kill it eventually - maybe that time is now...

Over the years, various Microsoft updates have broken it, especially when Excel moved to XML format in 2007. I can usually work out what has happened and fix it, but this time, I'm really stuck.

My original programmer died three years ago, so unless a PH'er has access to the afterlife, that option is out....

I appreciate no developer likes fixing another dev's code. However, I've nailed the exact point that is failing, but I just don't know why.

I'm willing to pay proper rates or decent beer tokens to get this sorted, as I feel an obligation to the financial advisers that reply on the tool

Reply here with any questions, or PM me if you can help

[CubanPete]

You could try using one of the AI tools to fault find / modernise it.

Not a programmer, but use copilot to generate MATLAB code at work. I know the softies use it far more heavily.

[duff-man]

If you don’t find anything on here, have a look on fiverr for freelancers, I’ve had luck in the past with app development things I’ve been stuck with in the past.

https://www.fiverr.com/gigs/vba-automation

[964Cup]

Nigel_O said:

I'm fairly handy in Excel and many years (25+) ago, I built a tool for financial advisers. I employed a programmer to write the VBA, as that was beyond me. The workbook has many thousands of lines of VBA. I couldn't write it to save my life, but I have enough of an idea what's going on to navigate my way around it.

I now only have a very small number of advisers using the tool - I'm keeping it alive simply because they rely on it, but I accept the fact that an MS update will kill it eventually - maybe that time is now...

Over the years, various Microsoft updates have broken it, especially when Excel moved to XML format in 2007. I can usually work out what has happened and fix it, but this time, I'm really stuck.

My original programmer died three years ago, so unless a PH'er has access to the afterlife, that option is out....

I appreciate no developer likes fixing another dev's code. However, I've nailed the exact point that is failing, but I just don't know why.

I'm willing to pay proper rates or decent beer tokens to get this sorted, as I feel an obligation to the financial advisers that reply on the tool

Reply here with any questions, or PM me if you can help

Can you share the code where it's breaking?

[Nigel_O]

964Cup said:

Can you share the code where it's breaking?

The code isn't actually breaking - there's a sub that reads values from one small external workbook (1 column, 6,000 rows) into varying locations in the 'master' workbook. It isn't just data items, the data 'read-in' also sets the properties of form controls and even replicated Excel comments in the same location / size when they were last edited.

The sub itself is about 500 lines and 25,000 characters (and is just one sub out of hundreds of others, often inter-dependent)

The sub SHOULD work through each row in the data sheet and copy the data into the master sheet. It has worked seamlessly for over a quarter of a century (was originally written in Excel '97!!!) but all of a sudden it has stopped for the majority of data files. Some still work, but most don't.

The sub bales out at row 152, as though it has completed. I've since discovered that if I remove the contents of rows 151-154 (four address fields, as it happens), then the sub works correctly.

It's almost as if something has decided that some exceptionally simple string fields are no longer compatible and it just bales.

Thanks to an earlier suggestion, I've had a very entertaining few hours with CoPilot today, which has taught me a LOT about AI capabilities - its truly scary what it can do - there were times when I cold have sworn I was conversing with a human (albeit a slightly cheesy Americanised human...)

I'm just waiting for CoPilot to string all of the revised VBA into a single sub and I'll give it a go

[Nigel_O]

CoPilot has been very impressive, but it started hallucinating and adding rogue code into the sub. In the end, it was inventing new variables that hadn't even been declared and it kept inventing patches to cover for its earlier mistakes.

After a couple of hours of trying to get the sub to compile, it fell flat on its face at the first attempt to use it, and I found that CoPilot's code had bypassed several user forms and assumed static values instead.

Back to human input, I think. Having lost work before, I've been very careful with backup copies and it was childsplay to revert back to the previous code.

I'm no further forward though...

[Baldchap]

Sounds like invalid characters in the source data. Not touched VBA for decades but my guess is whatever the VBA uses to contain the string is in the string, for example:

Copy 'bla'
write 'bla'

but 'bla' contains a ' so the line is read as

Copy 'bl'
'a write ' bl
'a'

If that makes sense.

TL;DR: Sounds like a dodgy character in the source data.

If reverting to previous version of the VBA fixes it then it implies to me they've changed the ' character (or whatever it is, if that makes sense).

[964Cup]

Nigel_O said:

CoPilot has been very impressive, but it started hallucinating and adding rogue code into the sub. In the end, it was inventing new variables that hadn't even been declared and it kept inventing patches to cover for its earlier mistakes.

After a couple of hours of trying to get the sub to compile, it fell flat on its face at the first attempt to use it, and I found that CoPilot's code had bypassed several user forms and assumed static values instead.

Back to human input, I think. Having lost work before, I've been very careful with backup copies and it was childsplay to revert back to the previous code.

I'm no further forward though...

Happy to have a look at it if you send it to me with the test data that's causing the problem.

[Nigel_O]

As I mentioned in my OP, I've been maintaining this tool for a quarter of a century and it has been at least ten years since I made any money out of it (its still a limited company and the accountancy fees are about the same as my earnings - its genuinely just pocket-money)

I think it's time to switch off the life support....

[vaud]

Try ChatGPT 4.1.

Learn some prompts (be explicit about what you are trying to do, what you see is breaking, etc - you could also ask it for improvements to the code base, and even write extra functionality)

You can even ask it to help you write a prompt.

[MustangGT]

Baldchap said:

Sounds like invalid characters in the source data. Not touched VBA for decades but my guess is whatever the VBA uses to contain the string is in the string, for example:

Copy 'bla'
write 'bla'

but 'bla' contains a ' so the line is read as

Copy 'bl'
'a write ' bl
'a'

If that makes sense.

TL;DR: Sounds like a dodgy character in the source data.

If reverting to previous version of the VBA fixes it then it implies to me they've changed the ' character (or whatever it is, if that makes sense).

Good shout, also what about the destination fields, have the references remained the same so the code can find them?

[Nigel_O]

Technology is conspiring to piss me off - just typed a long reply and got a 403 error (not for the first time on this thread - does the PH forum software have some kind of ban on PH'ers dissing other software? Is PH becoming self-aware?....)

Anyway, I hit the back button a few times and screen-grabbed what I was trying to say...

[Harpoon]

I see 964cup offered to take a look on Friday - I'll have a look if you don't mind sharing the file.

Could be worth comparing Excel versions between a working and non-working copy. In Excel, File > Account and the version of Excel is in the right hand column (mine for example is V2506 Build 18925.20158 Click to Run).

[Nigel_O]

Harpoon said:

I see 964cup offered to take a look on Friday - I'll have a look if you don't mind sharing the file.

Could be worth comparing Excel versions between a working and non-working copy. In Excel, File > Account and the version of Excel is in the right hand column (mine for example is V2506 Build 18925.20158 Click to Run).

Thanks, but its nothing to do with the Excel version (and I'm on an even later version than you - 2506 build 18295.20184). Some client records (the external workbook that holds the data, which is then read into the 'master' spreadsheet) work fine and some don't. There doesn't appear to be any pattern (I'm sure there is, but I haven't twigged it yet)

Sadly, I can't just share a single file. Its an entire ecosystem of inter-dependent files which form a licensing system for the advisers that use it. Users have a licence token on their local PC which permits / forbids access to the tool.

The only way to get the tool working on someone else's PC is by using a self-extracting file, but I suspect your antivirus / IT team would have kittens if you tried to open an EXE file that some stranger on the internet had just sent you!

Edited by Nigel_O on Monday 28th July 15:29

[vaud]

Nigel_O said:

The only way to get the tool working on someone else's PC is by using a self-extracting file, but I suspect your antivirus / IT team would have kittens if you tried to open an EXE file that some stranger on the internet had just sent you!

No one in there right mind is going to do that on a work PC. A home PC with a disposable VM, maybe.

[vaud]

Nigel_O said:

Technology is conspiring to piss me off - just typed a long reply and got a 403 error (not for the first time on this thread - does the PH forum software have some kind of ban on PH'ers dissing other software? Is PH becoming self-aware?....)

Anyway, I hit the back button a few times and screen-grabbed what I was trying to say...

Don't use copilot, try GPT 4.1.

[Sporky]

I do all the VBA at work.

Does the sheet you're trying to read not normally have addresses in it? Could be a wrong data type somewhere; what variable does it read into, and how is that variable created?

Though anything that goes RBAR (row by agonising row) can probably be done better with an array.

[Nigel_O]

vaud said:

Nigel_O said:

Technology is conspiring to piss me off - just typed a long reply and got a 403 error (not for the first time on this thread - does the PH forum software have some kind of ban on PH'ers dissing other software? Is PH becoming self-aware?....)

Anyway, I hit the back button a few times and screen-grabbed what I was trying to say...

Don't use copilot, try GPT 4.1.

I'll give it a try - thanks

[Nigel_O]

Sporky said:

I do all the VBA at work.

Does the sheet you're trying to read not normally have addresses in it? Could be a wrong data type somewhere; what variable does it read into, and how is that variable created?

Though anything that goes RBAR (row by agonising row) can probably be done better with an array.

The 'data sheet' is a single-column, 6,000 row workbook that ALWAYS has address fields. All data, regardless of the client is always in exactly the same place - essentially, the VBA in the 'master' workbook knows exactly where the corresponding data is in the data workbook - a crude form of field mapping, I suppose.

I'll email you the VBA so that yo can have a look. I've been told by a couple of other PH'ers that my programmer's VBA construction and commenting was excellent, so it should be possible to follow it

[Nigel_O]

I've just spent three hours trying a variety of ChatGPT suggestions, but without success. It tried some different approaches, including data logging to a separate worksheet, but after several attempts to make it work, it just crashed Excel. I eventually viewed the log in a recovered version of the workbook and it simply confirmed that the reading-in process stopped after row 151 (which I already knew...)

I spent many more hours with CoPilot and got nowhere, so I'm not inclined to pursue ChatGPT any further

I think the workbook is simply too complex - it's going to need a hands-on programmer to see the whole thing, not just a few VBA routines