Calc: Run Macro doesn't execute a known-good macro

[MineralMike]

I have this knowqn-good macro:

Sub InitNewBudget

sheet = ThisComponent.CurrentController.ActiveSheet

'Clear "previous actuals."

    target = sheet.getCellRangeByName("C5:C7")
	target.clearContents(7)
    target = sheet.getCellRangeByName("C11:C29")
	target.clearContents(7)

End sub

It runs fine from the Macro Editor and in the Macro Organizer, but if I use Tools → Macros → Run Macro, and select InitNewBudget (the only one in the list), then click Run, the macro does not execute.

Oddly, if I save the sheet at an Exel .xlst file, then load that into Calc, the macro runs fine from Tools → Macros → Run Macro.

Can someone help me figure out what’s going on? Thanks.

[JohnSUN]

Hello Mike and welcome!
I will make a strange assumption - the macro runs normally and even clears the two specified ranges, but it does this on the wrong sheet where you expect - so it seems to you that nothing is happening.

[MineralMike]

There is only one sheet. There is one tab at the bottom labeled Sheet1.

This is a really simple spreadsheet, so i don’t see a reason the macro
won’t run.

— Mike

[Villeroy]

Clear C5:C7 and C11:C29 manually.
menu:File>Template>Save… [Shift+F11]
When you need a new budget, just open a new template [Ctrl+Shift+N]
P.S. and this way you will not overwrite existing budgets.

[Villeroy]

I suspect, you don’t know what the (7) in your code means.
https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet_1_1CellFlags.html

Sub InitNewBudget
With com.sun.star.sheet.CellFlags
  myFlag = .VALUE + .DATETIME + .STRING
End With
sheet = ThisComponent.CurrentController.ActiveSheet

'Clear "previous actuals."

    target = sheet.getCellRangeByName("C5:C7")
	target.clearContents(myFlag)
    target = sheet.getCellRangeByName("C11:C29")
	target.clearContents(myFlag)

End sub

Formulas will not be cleared with this flag

[MineralMike]

I discovered my error in the macro – a couple of things weren’t DIMed.

The macro now runs correctly every time. The problem is solved.

— Mike

[Villeroy]

What a waste of time. If Dim was the problem, you would have seen the following error message.

BASIC runtime error.
Variable not defined.

Why didn’t you tell us about the error?

[MineralMike]

There was no error message. Sometimes the macro ran, sometimes it
didn’t, with no error message. When it didn’t run, the spreadsheet cells
were not changed.

I added/modified these lines to the macro to make it work:

==========
dim sheet as object
dim dispatcher as object
Dim source As Object
Dim target As Object

sheet = ThisComponent.CurrentController.ActiveSheet
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)

I apologize if my ignorance offended anyone. I’ve been programming in
seven languages for 50+ years, but am not familiar with Libre Office macros.

— Mike