VBA to computeAverage keeps getting Runtime Error 449

Creating a macro - Writing a Script - Using the API

VBA to computeAverage keeps getting Runtime Error 449

Postby hello.help » Fri May 05, 2017 3:04 am

Below I have noted the code to determine the average of a certain number of rows of data, however everytime I try to run it I keep getting Runtime Error 449 - "argument not optional" for the FOR argument.
Not sure what the problem is. :(
Please Help! :D

Code: Select all   Expand viewCollapse view
'This function computes the average
Function ComputeAverage(firstRow As Long, _
                        lastRow As Long) As Double                       
Dim currentRow As Long
Dim sum As Double
Dim count As Double
sum = 0: count = 0: currentRow = 0
   For  currentRow = firstRow To lastRow
      sum = sum + cells(currentRow,DATA_COL).value
      count = count + 1
   Next   
ComputeAverage = sum / count
ActiveCell.Value = ComputeAverage   
End Function
Last edited by Hagar Delest on Fri May 05, 2017 9:20 pm, edited 2 times in total.
Reason: add code tags
OpenOffice 4.1.3 on MacOS 10.12.4
hello.help
 
Posts: 2
Joined: Fri May 05, 2017 2:46 am

Re: VBA to computeAverage keeps getting Runtime Error 449

Postby Villeroy » Fri May 05, 2017 6:36 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24253
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: VBA to computeAverage keeps getting Runtime Error 449

Postby hello.help » Fri May 05, 2017 6:45 am

Villeroy, I saw these forums but the one closest to my problem : https://forum.openoffice.org/en/forum/v ... 5&p=415649

I've taken out the extra dim for the first and last row but I am still getting this error for some reason, and I'm not sure why it isn't working :(
Can you explain why this might be happening if you know?
OpenOffice 4.1.3 on MacOS 10.12.4
hello.help
 
Posts: 2
Joined: Fri May 05, 2017 2:46 am

Re: VBA to computeAverage keeps getting Runtime Error 449

Postby Zizi64 » Fri May 05, 2017 6:53 am

I am still getting this error for some reason, and I'm not sure why it isn't working :(
Can you explain why this might be happening if you know?


Your macro is written in MS VBA. But the AOO and LO has a pure basic language tool (IDE) (named StarBasic or Basic), and lots of API (Application Programming Interface) functions.
Microsoft VBA and the StarBasic+API are not compatible. (The LibreOffice has a littlebit higher compatibility /with the VBA/ than the AOO)
Use the API function (with the StarBasic or with an another supportes programming language /Python... etc./) in the AOO and LO.
Tibor Kovacs, Hungary; LibreOffice4.4.7 on Win7x64Prof.
And the portable versions: LO3.3.0-LO5.4.1 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5772
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: VBA to computeAverage keeps getting Runtime Error 449

Postby Villeroy » Fri May 05, 2017 11:54 am

=AVERAGE(OFFSET($A$1;first-1;0;last-first;1)) works with any spreadsheet program. In Excel, Gnumeric and LibreOffice Calc you may need to replace semicolons with commas.

The following Basic function is obsolete because it adds a lot of complexity and inefficient Basic code without adding any benefit. Like your VBA and the above formula it takes two integer row numbers and calculates the average between the two given rows in column A. It makes use of the FunctionAccess service of LibreOffice or OpenOffice.
Code: Select all   Expand viewCollapse view
Function AVG(startRow,rowCount) AS Double
    Dim fa, sh, rg
      fa = createUnoService("com.sun.star.sheet.FunctionAccess")
      sh = ThisComponent.CurrentController.getActiveSheet()
      rg = sh.getCellRangeByPosition(0,startRow -1,0,endRow -1)
      AVG = fa.callFunction("AVERAGE", Array(rg))
End Function


If I recall correctly...
Code: Select all   Expand viewCollapse view
rg = ActiveSheet.Range("A1").Offset(startrow -1, 0).Resize(endrow - startrow, 1)
AVG = Application.Average(rg)

would be the corresponding function in VBA. Application.Average calls the built-in Average function.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24253
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests