Macro error message - variable not set

sswcharlie · July 28, 2024, 1:25am

Hi
See attached workbook. When I run the ‘TestNamedRange’ macro I get an ‘variable not set’ on lines 60-63.

Yes. It is an AI generated workbook. Go gentle on me. (its not as smart as is meant!) Looks to do what I want. Just need some advice. AI not getting it fixed!
In the macro dept under ‘MyMacros’ there is something called MyMacros,Form_avance. I do not know what this is and I cannot get to delete, the delete button goes greys out.
ClaudeTest.ods (12.3 KB)

Not sure but it may be in the way!
Thankyou
Charles

elmau · July 28, 2024, 3:07am

Your file is empty…

Zizi64 · July 28, 2024, 5:48am

How the AI can put anything into a Library/Module into your Mymacros?? If the AI generated a Spreadsheet file - I suppose that - it can put macro into the Standard library of the file only. There is not any macro code in the sample file, only there is an empty Form:

.
.
…Ask the AI: Where did it put your macro…

sswcharlie · July 28, 2024, 6:13am

ClaudeTesta.ods (14.1 KB)

Hope this one works. I had the macro in MyMacros not Claude heading. It got left behind in transfer. Have placed it under the Claude heading now. Thanks
Charles

Zizi64 · July 28, 2024, 6:23am

Yes there is macro code in the new sample file.
The AI uses MS Visula Basic constants, but the StarBasic (of the Libreoffice) is NOT VB.

vbInformation
vbCritical
vbNewLine
constants not exist with these names in the StarBasic.

Of course the MsgBox() requests these constants, but you can not use these names without declare them, and without assigning the relevant numeric value to them.

Zizi64 · July 28, 2024, 6:31am

LO Help:

https://help.libreoffice.org/latest/si/text/sbasic/shared/03010102.html

robleyd · July 28, 2024, 6:43am

See also your other post on the same topic.

In summary, if you want to use the VBA constants, you need to declare
Option VBASupport 1

Of course it would be better to use StarBasic constants as in the page @Zizi64 linked.

sswcharlie · July 28, 2024, 6:46am

Hi Zizi64
Thanks for your message. I think IA has taken me down the wrong road.
Should I strip the code back to the bare essentials only. That is without all the message etc. and then try . The dialog1 should have a combo box in it, I can put that back in. Do you think if I ‘recorded’ a macro’ might work, not sure if you can put a dropdown in recording.
Your comments would be much appreciated. My old brain struggles but getting there (I think)
Thanks
Charles

Zizi64 · July 28, 2024, 6:50pm

The macro recorder has a very limited capability. You must learn the API and the StarBasic and need WRITE your macros for an efficient macro programming. API: Application Programming Interface.