Solved

Error msg: Unable to access Worksheet function

Posted on 2002-03-26
9
322 Views
Last Modified: 2012-06-21
I am writing a function in VBA which needs to find the average of a range of cells.
the range varies, so is passed to my function by another function (which I have tested and works fine).

My function looks like this:

Function AvSqFtbyQ(quart As Integer) As Integer
'calculates the average square footage per quartile
Dim hold As Integer
Dim Rng As String

Rng = GetRangeQ(quart)
hold = Application.WorksheetFunction.Average(Rng)
AvSqFtbyQ = hold

End Function


When I test it in the Immediate Window I get error number 1004: "Unable to get the Average property of the Worksheet Function Class."

am I missing a reference library or do I need to install something?

any ideas as to what is going on here?

thanks, I know I've been asking a lot of questions lately!

Sasha

0
Comment
Question by:SBFurr
  • 6
  • 3
9 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6897404
Hi Sasha, what does the getrange exactly return? i see a string this modified one works

Function AvSqFtbyQ(quart As Integer) As Integer
'calculates the average square footage per quartile
Dim hold As Integer
Dim Rng As Range

Set Rng = GetRangeQ(quart)
hold = Application.WorksheetFunction.Average(Rng)
AvSqFtbyQ = hold

End Function

presuming your return a range from the getrange function

HTH:O)Bruintje
0
 

Author Comment

by:SBFurr
ID: 6897447
actually GetRangeQ returns a string.

I tried just typing in the value in the Immediate window,
debug.pring application.WorksheetFunction.average(C4:C14)
and got the same error #1004.

thanks
Sasha
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897471
the sring must be formatted like "C4:C14" and tehn this will do

hold = Application.WorksheetFunction.Average(Range(Rng))

HTH:O)Bruintje
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SBFurr
ID: 6897508
one more detail, how do I get the "s around it?
if I tell it
hold = Application.WorksheetFunction.Average(Range(" & Rng & "))
It thinks I'm telling it "& Rng &"

thanks,
Sasha
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897521
didn't it work? with the returned string?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897540
or try  Rng = "(" & quart & ")"
0
 
LVL 44

Accepted Solution

by:
bruintje earned 50 total points
ID: 6897591
allright a bit of rereading

getrange(X) returns a string like C10:C14

then

hold = Application.WorksheetFunction.Average(Range(Rng))

has to work as is without any formatting or does it not?

i presume this is a folloow up on your access question?

:O)Bruintje
0
 

Author Comment

by:SBFurr
ID: 6897650
why, yes it is a follow-up to my access question :)
I'm almost done with this project.

hold = Application.WorksheetFunction.Average(Range(Rng))
works, I had mistyped it.

thanks for all your help!

you might see questions on creating graphs in VBA tomorrow...

Sasha
0
 
LVL 44

Expert Comment

by:bruintje
ID: 6897758
;) looking forward to it
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question