How to Create Calc Function in LO -BASIC Macros ? Like VBA below codes

Hi Friends,

In MS Office Excel - VBA , I have created " Worksheet Function " in Worksheet index (1) Under Two Methods… Are there any codes in " LO Calc Functions " Like these Two Methods ? …

Sub Worksheet_Function1()
Worksheets(1).Range(“A3”).Value = WorksheetFunction.Average(Range(“A1:A2”))
End Sub

Sub Worksheet_Function2()
Range(“A3”).Value = “=AVERAGE(A1:A2)”
End Sub


Have you tried? :wink:

Hi mikekaganski,

I got Syntax Error …

Also, I want - LibreOffice BASIC - Programming ( Macros ) in Calc Like Two Methods to achieve the Result - 7.5 as Average… what i got in Excel VBA…

… because you use wrong kind of quotes - not the correct straight quotes like ", but typographin ones like “”. They would also fail in MS Office.

Please provide all the information in the question, to avoid guessing what you really might want.

1 Like

HI mikekaganski,

I want LibreOffice “Star Basic” Macro Codes For the Function 1 and Function2 to Get the Answer 7.5 in the Cell “A3”. Any Idea ? Brother…


If you’re just interested in the exact equivalent, this Basic writes it a bit longer:

Sub Worksheet_Function1()
'Worksheets(1).Range(“A3”).Value = WorksheetFunction.Average(Range(“A1:A2”))
ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A3").setValue(CreateUnoService("").CallFunction("Average", Array(ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1:A2"))))
End Sub

Sub Worksheet_Function2()
'Range(“A3”).Value = “=AVERAGE(A1:A2)”
End Sub

HI JohnSun,

Thanks, This is What exactly i need and In Function1 …Its very Long Code So… By Using Variables It will come down Line by Line…for better reading code … in My Work Place ( Office ) …Thanks… Brother…

Oh, I thought it was important for you to get the solution in one line … Yes, of course, such a notation is easier to read:

Sub Worksheet_Function1()
Dim oSheets As Variant, oSheet As Variant, oTargetCell As Variant, oSourceRange As Variant, oFunctionAccess As Variant
	oSheets = ThisComponent.getSheets()
	oSheet = oSheets.getByIndex(0)
	oTargetCell = oSheet.getCellRangeByName("A3")
	oSourceRange = oSheet.getCellRangeByName("A1:A2")
	oFunctionAccess = CreateUnoService("")
	oTargetCell.setValue(oFunctionAccess.CallFunction("Average", Array(oSourceRange)))
End Sub
1 Like

Thanks John…

And addition. Your original code will work too:

Option VbaSupport 1

Sub Worksheet_Function1()
Worksheets(1).Range("A3").Value = WorksheetFunction.Average(Range("A1:A2"))
End Sub

Sub Worksheet_Function2()
Range("A3").Value = "=AVERAGE(A1:A2)"
End Sub

Hi Sokol92,

I tried this code but i got Syntax Error.
Even, I Copied Your Code… and paste it in my LO Macros IDE…again I got Error…

Let’s try together (see attachment).
Example.ods (9.4 KB)

Maybe you forgot to fill in cells A1:A2?

1 Like

HI Sokol92,

Even, I tried Your File Also and I got different Error Message on Two Sub Procedure
See the Below Screens Shots

What version of LO do you have?

I checked the macros for versions:

Version: / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.6-0ubuntu0.22.04.2
Calc: threaded


Version: (x64) / LibreOffice Community
Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf
CPU threads: 6; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded

Here is my Screen Shot


Please could you check the macros in safe mode (Menu / Help / Restart in Safe Mode…)?

Hi sokol92,

As per your comment above Run in SAFE MODE - Still, getting Error Message.

In safe mode, before opening a document, allow macros to run:
Menu / Tools / Options / LibreOffice / Security / Macro Security / Medium.