using pre defined functions in your function
I want to use excels average function, but in a little bit of a different way, so I made a module with all required subs to clean up the incoming range. The first 4 lines in the function work fine. For some reason it will not let me use the built in average function, but I have used other functions in this module such as day() and char(). Help please.
Function adamMonthlyAverage(ByVal dataRange As String)
Dim beggining As String
Dim endding As String
beggining = getTopCell(dataRange)
endding = getBottomCell(dataRange)
adamMonthlyAverage = Average(beggining & ":" & endding)
End Function
| Attachment | Size |
|---|---|
| example data sheet.xlsx | 11.97 KB |
I think this should do the
I think this should do the job:
Function stringedAverage(top As Range, bottom As Range) As Double
Dim startAddress As String
startAddress = top.Address
Dim bottomAddress As String
bottomAddress = bottom.Address
Dim retval As Double
retval = Evaluate("AVERAGE(" + startAddress + ":" + bottomAddress + ")")
stringedAverage = retval
End Function
The only gotch is that is is the worksheet not VBA version of the AVERAGE function, so the results may not be the same as worksheetfunction.average.
While debugging this I noticed that it is not dynamic, in fact it has behaviour I have not encountered before. Fill a range with =RAND() input the function and hit F9 a couple of times and see what happens.
This turns out to be a real
This turns out to be a real pain in the arse, Excel/VBA being to clever, in a pointless attempt to optimize the function VBA looks at top.address and bottom.address and only recalcs the function if these have changed.
The following fools the calc tree to force a recalculation of the function:
Function stringedAverage2(top As Range, bottom As Range) As Double
Dim startAddress As String
startAddress = top.Address
Dim v1 As Variant
v1 = top.Value
Dim bottomAddress As String
bottomAddress = bottom.Address
Dim v2 As Variant
v2 = bottom.Value
Dim retval As Double
retval = Evaluate("AVERAGE(" + startAddress + ":" + bottomAddress + ")")
stringedAverage2 = retval
End Function
R: using pre defined functions in your function
Adam,
Try this:
adamMonthlyAverage = Application.WorksheetFunction.Average(...)
Adding Application.WorksheetFunction tells VBA that this is an Excel worksheet function and not a function you have defined for yourself.
still does not work
I have tried it with cellRange being a Range data type and also a string for an incoming argument. I still get #value for a result in the calling cell in excel. Why can I use the day() function just by calling day(...), but for average it will not work?
Function adamMonthlyAverage(ByVal cellRange As Range) or string, I have tried both.
adamMonthlyAverage = Application.WorksheetFunction.Average(cellRange)
End Function
RE: Still does not work
Hi,
Your function:
Function adamMonthlyAverage(ByVal cellRange As Range)
adamMonthlyAverage = Application.WorksheetFunction.Average(cellRange)
End Function
works when I call it in that way:
= adamMonthlyAverage(A1:A10)
and return #VALUE! if I call it in that way:
= adamMonthlyAverage("A1:A10")
I cannot reproduce your problem in another way. Can you attach an example file?
Best regards.
attach a file
Where do you attach a file at to this forum. I am new to it and can't find the button or place.
I should clarify a bit here. I am not using the formula in the typical since as you have above. I am analyzing data for the past 10 years of data such as water temp, water conductivity, water turbitity... and the data happens every 2 to 4 minutes all day long, 365 days a year for 10 years, so I want to have a daily max, daily min, and daily average. The problem is that the amount of data per day varies from 1500 records all the way to 4000 records perday.
So to use your example, I want to put A1:a10 in say cell B2,
and reference it like this average(b2).
that way I can change the contents of b2 at the beggining of a new month, and not have to change all of the corresponding formulas.
Some solutions
To attach a file:
1) Log In;
2) Go to your forum topic -> Edit tab;
3) Into File attachments section attach your file (browse your file and then press Attach button);
4) Save your topic.
You have two options:
1) Change your function to:
Function adamMonthlyAverage(ByVal RngAddress As String)
Dim oRng As Range
Set oRng = Range(RngAddress)
adamMonthlyAverage = Application.WorksheetFunction.Average(oRng)
End Function
and call it in that way:
= adamMonthlyAverage(B2)
This will work for the example where you change your daily average range in cell B2 (A1:A10, later to A1:A11 and so on).
2) This is better solution. I suggest you to create named range. Read the following comment which I post in another topic if you have interest how to create dynamic named range:
Use dynamic ranges
For your case, create named range which refers to your daily average range. For example:
Name: DailyAverage
Refers to: = Sheet1!$A:$A
Now, the function will be:
Function adamMonthlyAverage2(ByVal cellRange As Range)
adamMonthlyAverage2 = Application.WorksheetFunction.Average(cellRange)
End Function
and call it in that way:
= adamMonthlyAverage2(DailyAverage)
In that way you no need to change anything when you get a new value in your daily average range.
I hope my explanation is clear enough, because my English is not my best skill.
Best regards.
I dont get it
So I guess my computer does not work the same way yours does as the below code returns a #value. coming in the user gives the function c5, and in cell c5 is the range of data to workwith, in this case cells E5:E18
Function adamMonthlyAverage2(ByVal cellRange As Range)
adamMonthlyAverage2 = Application.WorksheetFunction.Average(cellRange)
End Function
I originally had this code, but it executes like 15 times for some reaon, which takes up a lot of processor time. I have to wait a good 30 sec for it to finish, and I have a computer that is less than a year old. I even tried opening up a new excel file, making a new module that only has these two functions, and my two getTopCell() and getBottomCell() functions to elliminate all the other code to make sure that was not the reason it takes a while to run.
Function adamMonthlyAverage(ByVal cellRange As String) As Double
Dim startAddress As String
Dim bottomAddress As String
Dim value As Double
startAddress = getTopCell(cellRange)
bottomAddress = getBottomCell(cellRange)
value = Evaluate("AVERAGE(" + startAddress + ":" + bottomAddress + ")")
adamMonthlyAverage = value
End Function
So, I looked at the name range thing and looked into some tutorials on it, but it makes no sence. what does dynamic ranges do for me?
also: Everytime I switch between windows, say to excel to internet explorer and back to excel, it looses the values in the calculated cells. Same with closeing it and reopening it. what do I do to fix this?
Hi, Adam, The problem is not
Hi, Adam,
The problem is not with your computer. You just mixed my two suggestions :) . You cannot pass String to Average function. And for that reason I suggested to you two ways to workaround this limitation. Sorry for the duplication but I will repeat once again.
1) The first function was:
Function adamMonthlyAverage(ByVal RngAddress As String)
Dim oRng As Range
Set oRng = Range(RngAddress)
adamMonthlyAverage = Application.WorksheetFunction.Average(oRng)
End Function
Now, you call it from some cell (C5 in your example), which contain a string address (E5:E18 in your example), in this way:
= adamMonthlyAverage(C5)
I'm absolutely sure that this works.
2) Firstly, I mentioned to you dynamic named range just for information if you have some interest of them. In your case I suggested to you just named range, because if you define the whole column, the Average function get only the numeric values in the range and work on them. But now, after I see your example file, I'm more confused than before of which ranges you want to calculate. For that reason I suggest one more solution which uses my second function but call it in different way. The second function was:
Function adamMonthlyAverage2(ByVal cellRange As Range)
adamMonthlyAverage2 = Application.WorksheetFunction.Average(cellRange)
End Function
Call it from cell C5, which contain string address E5:E18, in this way:
= adamMonthlyAverage2(INDIRECT(C5))
The INDIRECT function will convert the string value E5:E18 to the actual range and that it will be passed properly to Average function. I'm sure that this works fine too.
Now, if you want some additional help for your other problems, please attach file with some code, because I don't know how your functions getTopCell() and getBottomCell() actually works and whether have some problem with them. And also, please explain in more details which ranges you want to calculate because in your example file the data are in columns B to J but the addresses above them are different...
I hope that this time I was clearer than before.
Best regards.
error
So, I am passing the range into the function, and this works on all my other functions. Now it returns a #value error in excel and I don't know why.