When running macro it gives error “basic runtime error, object variable not set"

michaely · November 12, 2020, 11:04pm

ive created this macro to delete cells in columns B to N and rows 2 to 999, but when I try to run it it gives that error – any tips on how to fix it?

(Edit: activated screenshot -AK)

Zizi64 · November 13, 2020, 5:42am

Your attached code is a VBA command. The (MS Excel VBA) and the (LO StarBasic + LO API) are not compatible.

The LO can run some VBA codes, but not all of them. The Excel can not run any LO API commands.
You must use the compatibility directives if you want to use VBA codes in the LO.
But it is better to rewrite your macros based on the API and one of the supported programming languages.

API: Application Programming Interface.

Ratslinger · November 13, 2020, 3:30am

Hello,

Sub my_delete
    Dim oActiveSheet As Object
    Dim oCellRangeByName As Object
Rem Get Active Sheet
    oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
Rem Get Cell Range
    oCellRangeByName = oActiveSheet.getCellRangeByName("B2:N999")
Rem Clear wanted contents - 1 = clear values - see links
    oCellRangeByName.clearContents(1)
End sub

For clearing contents, see:

XSheetOperation Interface Reference

and

com::sun::sheet::CellFlags

michaely · November 13, 2020, 11:38am

Thanks for reply, I ran that but nothing happened https://gfycat.com/VainMindlessJohndory I’m trying to get it to delete all the text you can see in that sheet (except the very top pinned row)

Ratslinger · November 13, 2020, 3:21pm

Hello,

Nothing happened because the macro is set to clear values and the remarks noted. The answer also pointed to other values for clearContents such as 4 for Strings which it appears you have.

Try with the different value.

Ratslinger · November 13, 2020, 5:58pm

@michaely,

Please note, values can be combined for deleting multiple types such as:

oCellRangeByName.clearContents(5)

will clear Values and Strings.