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 ? …
Code:
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 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.
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("com.sun.star.sheet.FunctionAccess").CallFunction("Average", Array(ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A1:A2"))))
End Sub
Sub Worksheet_Function2()
'Range(“A3”).Value = “=AVERAGE(A1:A2)”
ThisComponent.getSheets().getByIndex(0).getCellRangeByName("A3").setFormula("=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("com.sun.star.sheet.FunctionAccess")
oTargetCell.setValue(oFunctionAccess.CallFunction("Average", Array(oSourceRange)))
End Sub
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?
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: 7.3.6.2 / 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
and
Version: 7.4.2.3 (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
HI,
7.3.7.2
Here is my Screen Shot
Please could you check the macros in safe mode (Menu / Help / Restart in Safe Mode…)?
In safe mode, before opening a document, allow macros to run:
Menu / Tools / Options / LibreOffice / Security / Macro Security / Medium.
