[Solved] Problem calling a function

Creating a macro - Writing a Script - Using the API

[Solved] Problem calling a function

Postby mrgixxer88 » Thu Apr 20, 2017 2:44 pm

Hi all, I have written a function to compute the average of a range of cells in a column. My inputs are firstRow and lastRow. I need this function to be able to do the average of any cells its pointed to not just one specific range. So far im only able to get the function to work if i actually assign an integer value to firstRow and lastRow, ie 3 and 5 respectively which nets me the correct average from the spreadsheet i am calling it in. But i need this function to work with out actually assigning specific integers to it, i know i must assign something but i am just not sure what to do to get it to work to calculate multiple different ranges that it is called to do. As it stands it will only produce the average of cell 3 to 5( i just did this to test the actual math of it and help with debugging) . I just want any pointers or pushes in the right direction, any help of this sort would be greatly appreciated as I am struggling with it. I tired attaching a jpeg file but it would not let me for some reason
Thanks alot!!

Code: Select all   Expand viewCollapse view
Function Average(first As Long, _
                        last As Long) As Double
                       
                       
                        Dim avg as Double
                        Dim sum as Double
                        Dim row as Double
                        Dim first As Long
                        Dim last As Long
                        Dim DATCOL As Long
                       
                        DATCOL = 1
                        sum = 0
                        first=3
                        last=5
                        For row = first To last
                            sum = sum + Activesheet.cells(row,DATCOL)
                        Next row
                             
                 
                        avg = sum / (last + 1 - first)
                  
                  Average = avg
   
End Function
Last edited by mrgixxer88 on Fri Apr 21, 2017 12:35 am, edited 2 times in total.
open office 4.1.3 on Windows 10
mrgixxer88
 
Posts: 5
Joined: Thu Apr 20, 2017 1:59 pm

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby Villeroy » Thu Apr 20, 2017 3:34 pm

=AVERAGE(OFFSET($A$1;first-1;0;last-first;1))
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: 23972
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby FJCC » Thu Apr 20, 2017 5:31 pm

Don't declare first and last with DIM after you have declared them AS Long in the first line of the function.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 5913
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby RusselB » Thu Apr 20, 2017 5:52 pm

OpenOffice 4.1.3 and LibreOffice 5.1.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 3862
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby mrgixxer88 » Thu Apr 20, 2017 11:01 pm

does declaring first and last as Dim stop the function operating correctly and getting it to do what I want ? it is similar to that post, except I have got this far by myself and only would like some pointers or know how , not for anyone to do it for me haha. Villeroy I need to do it as a user made function run so I don't think I can use that code but thank you for your reply.
open office 4.1.3 on Windows 10
mrgixxer88
 
Posts: 5
Joined: Thu Apr 20, 2017 1:59 pm

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby Villeroy » Thu Apr 20, 2017 11:14 pm

From scratch without office suite at hand:
Code: Select all   Expand viewCollapse view
Function AVG(startRow,endRow) AS Double
Dim fa, sh, rg
  fa = createUnoService("com.sun.star.sheet.FunctionAccess")
  sh = ThisComponent.CurrentController.getActiveSheet()
  rg = sh.getCellRangeByPosition(0,startRow,0,endRow)
  AVG = fa.callFunction("AVERAGE", Array(rg))
End Function

But why this complicated bullshit when you have a spreadsheet application at hand?
Last edited by Villeroy on Thu Apr 20, 2017 11:25 pm, edited 1 time in total.
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: 23972
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP(SOLVED)

Postby mrgixxer88 » Thu Apr 20, 2017 11:18 pm

I have never seen any of that type of code before Villeroy, I think it's a bit too advanced for me. Just got it too work following FJCC's advice of not declaring the variables again. does declaring them again confuse the function or something?
open office 4.1.3 on Windows 10
mrgixxer88
 
Posts: 5
Joined: Thu Apr 20, 2017 1:59 pm

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby Villeroy » Thu Apr 20, 2017 11:25 pm

Too sad. Just another cargo cult programmer.
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: 23972
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby mrgixxer88 » Fri Apr 21, 2017 12:33 am

cargo cult programmer Vileroy? I'm not a programmer actually not have I ever claimed to be. All I did was ask for a little a bit of help and I received it. Is it it really necessary to insult me? I had to do the function that way as I was told to do the function that way, not to use the spreadsheet functions. Thank you for your replies, I'm sorry you find me a cargo cult programmer......i don't even know what that is to be honest?
open office 4.1.3 on Windows 10
mrgixxer88
 
Posts: 5
Joined: Thu Apr 20, 2017 1:59 pm

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby FJCC » Fri Apr 21, 2017 12:33 am

Declaring the variables again does not confuse the function, it results in the values that were passed to the function being erased. The way you had it, you declared the variables first and last as parameters passed to the function then in the body of the function you declared two variables also called first and last. When those are created they are given the value zero, overwriting the values that you passed to the function.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 5913
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP [SOLVED]

Postby mrgixxer88 » Fri Apr 21, 2017 12:39 am

ah I see, I noticed that when I used break points to try and debug it I was getting zero for everything when I hovered the cursor over the items, I couldn't understand why. So when doing a function like that you should only have the variables declared in the title of the function ? Thanks for your help mate, I do appreciate it !
open office 4.1.3 on Windows 10
mrgixxer88
 
Posts: 5
Joined: Thu Apr 20, 2017 1:59 pm

Re: PROBLEM WITH FUNCTION CALLING PLEASE HELP

Postby JeJe » Fri Apr 21, 2017 12:42 am

FJCC - I think it creates a new variable, though that might be what you meant.


Code: Select all   Expand viewCollapse view
    Function Average(first As Long,last As long) As Double
                           
                            Dim first As Long
                            Dim last As string
                         msgbox vartype(last) '=8 string
                           
                            end function

Openoffice 4.1.2
Windows 8
JeJe
 
Posts: 114
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests