Function to find average of data in a column

Creating a macro - Writing a Script - Using the API

Function to find average of data in a column

Postby axljerry123 » Fri Apr 21, 2017 4:53 am

Hi

I'd like to know how to create a function average of a section of data in column A.
For example, a call of  function ComputeAverage(4,10) would find the average of the cells from A4 to A10

This is the code I've written:
Function ComputeAverage(firstRow As Long, _
lastRow As Long) As Double

Dim i as Long
For i = 2 to 23
firstRow = ActiveSheet.Cells(i,1) + firstRow
Next i
Dim j as Long
For j = 2 to 23
lastRow = ActiveSheet.Cells(j,1) + lastRow
Next j
ComputeAverage = (firstRow+lastRow)/(j-i)
End Function

This code doesn't work...So can somebody help me?
OpenOffice 4.1.3
axljerry123
 
Posts: 2
Joined: Tue Apr 18, 2017 2:16 pm

Re: Function to find average of data in a column

Postby Zizi64 » Fri Apr 21, 2017 6:26 am

Code: Select all   Expand viewCollapse view
Function ComputeAverage(firstRow As Long, lastRow As Long) As Double

Dim i as Long
   For i = 2 to 23
      firstRow = ActiveSheet.Cells(i,1) + firstRow
   Next i
Dim j as Long
   For j = 2 to 23
      lastRow = ActiveSheet.Cells(j,1) + lastRow
   Next j
ComputeAverage = (firstRow+lastRow)/(j-i)

End Function

This code seems as a MS VBA code snippet.
The StarBasic+API functions are not compatibble with the MS VBA.
You must rewrite your MS VBA macrso functions based on the API functions of the AOO/LO.


And why you used two "For..." cycles to compute the average value of ONE one dimensional cell range?
Why you used constans parameters for the FOR statement, when you need compute a value between two variable row numbers?
Maybe I misunderstood it: you want compute the average of the values in one column between the rows given by the input parameters?
And why you need a macro for computing a simple average value? There is a cell function named AVERAGE() for this task.

Code: Select all   Expand viewCollapse view
Function ComputeAverage(firstRow As Long, lastRow As Long) As Double

Dim i as Long
Dim MySum az Double
Dim oDoc as object
Dim oSheet as object
Dim oCell as object

   MySum = 0
   For i = FirstRow to LastRow
      oCell = "put here the API functions to get the actual cell based on the row numbers and the actual sheet, and the actual document..."
      MySum = MySum + oCell.Value
   Next i

ComputeAverage = MySum/(LastRow-FirstRow)

End Function


You MUST study the API functions for control the documents, rows, cells by your macro code. See the free books: Andrew Pitonyak's books will give you excellent descriptions and examples. And you can find similar examples in this Forum too.
https://forum.openoffice.org/en/forum/v ... f=5&t=7915
http://www.pitonyak.org/oo.php
https://forum.openoffice.org/en/forum/v ... 20&t=88415
Last edited by Zizi64 on Fri Apr 21, 2017 5:35 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LibreOffice4.4.7 - Portable versions: LO3.3.0-LO5.3.3 and AOO4.1.3; on Win7x64Prof.
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: 5131
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Function to find average of data in a column

Postby Villeroy » Fri Apr 21, 2017 2:49 pm

Your class mate was here already: https://forum.openoffice.org/en/forum/v ... 20&t=88415 and he was not satisfied with my working average function in 4 lines.

If you want somebody to do your VB(A) homework, then you are on the wrong forum for two reasons:
1) This office suite has a limited capability to interprete/translate VBA. I would not say that it supports support VBA actually.
2) We are not here to do anybody else's homework.
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: 23733
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