VBA to computeAverage keeps getting Runtime Error 449

[hello.help]

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!

Code: Select all

'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

[Villeroy]

viewtopic.php?f=20&t=88415&p=415649
viewtopic.php?f=20&t=88378&p=415432
viewtopic.php?f=20&t=88428&p=415734
viewtopic.php?f=20&t=88378&p=415439

[hello.help]

Villeroy, I saw these forums but the one closest to my problem : viewtopic.php?f=20&t=88415&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?

[Zizi64]

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.

[Villeroy]

=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

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

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.