MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Passing ranges to VB routines

Posted by Bill L on January 30, 2001 12:19 PM

I want to do some complicated sums on a range and
then put the answers back into another set of cells as referenced by the call to the VB routine
eg =TestFunction2(A1:A3,B1:B3).

But I just can't find a syntax that works to put the results back in the range passed to the routine.

Here are my simple test routines, TestFunction1 works perfectly, but every possible version of TestFunction2 I try just comes back with #Value

Function TestFunction1(A1 As Range, A2 As Range)
Afirst = A1.Cells(1, 1).Value+5
End Function

Function TestFunction2(A1 As Range, A2 As Range)
Afirst = A1.Cells(1, 1).Value
A2.Cells(1, 1).Value = Afirst * Afirst + 3
End Function

This must be simple but I have just run into the sand trying different ways.

Thanks for any help

Posted by Tim Francis-Wright on January 30, 2001 5:12 PM

Your problem is in this line:
A2.Cells(1, 1).Value = Afirst * Afirst + 3

In a function, you can't change any cells in
the worksheet. You could do this:
Testfunction2 = Afirst * Afirst + 3
but you can't change the cells in A2 to do it.

Hope this helps.