Passing a range from worksheet to VBA function

[R Beck]

Hi all -

I have the following function:

Function regression(x, power())
For i = Lbound(power) to Ubound(power)
regression = regression + x^i + power(i)
Next
End Function

and wish to use the following in Excel

=regression(A1, B1:B3)

But I can't seem to get it to work. What am I missing?

Thanks!
Ryan

 

[jason]

Hi all -

I have the following function:

Function regression(x, power())
  For i = Lbound(power) to Ubound(power)
     regression = regression + x^i + power(i)
  Next
End Function

and wish to use the following in Excel

 =regression(A1, B1:B3)

But I can't seem to get it to work.  What am I missing?

Thanks!
Ryan

power() as variant
should do it.

 

[Jacob Skaria]

Function has to return something....(Refer the below for the structure of a
function)

Function regression(rng1 As Range, rng2 As Range) As Variant
Dim intCount As Integer
For Each cell In rng2
intCount = intCount + 1
regression = regression + rng1 ^ intCount + cell
Next
End Function

If this post helps click Yes

 

[Ryan H]

First thing, you need to Function to return something and your need to use
the For...Next Loop structure when dealing with Ranges. Try this below:


Function Regression(x As Range, Power As Range) As Double

Dim c As Range
Dim Exp As Long

For Each c In Power
Exp = Exp + 1
Regression = Regression + x.Value ^ Exp + c.Value
Next c

End Function

Hope this helps! If so, click "YES" below.

 

[Matthew Herbert]

Ryan,

As a follow on to Jacob's post, are you using this function for yourself or
are you deploying it to others? If you are using it for yourself, then you
know exactly how it works and there won't be any issues with using the
function. If others plan on using it, then you need to consider a few things:

(1) Ensure that the rng1 parameter is only one cell (or adjust the function
to grab only one cell from rng1).
(2) Will the rng2 parameter have more than 32,767 cells in it? If so, then
you need to change the counter to a Long data type.
(3) Will the function need to be called similar to the following: res =
regression(1, Array(3, 4, 5))? If so, you'll need to alter your code to
handle this situation.
(4) Use error messages to force the user to provide the data how the
function needs it, e.g. the returned result could be sting text or the
built-in Excel function errors.

These are only a few thoughts I had when I first read your post in
conjunction with Jacob's reply.

Best,

Matthew Herbert

 

[R Beck]

Thanks all - I got it to work, thanks to your help!

And yes - this is just for me....

Appreciate the quick response!

Ryan