MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

calling a sub routine

  Asked By: Abbie    Date: Nov 28    Category: MS Office    Views: 1486

i have a workbook that contains two cells named distance and height,
contained in these cells are two numbers (doubles). In another cell
I have a formula which calls a function boomLength(radius, height)
with the following code contained in it:


Public Function boomLength(radius As Double, height As Double) As

boomLength = (radius ^ 2 + height ^ 2) ^ 0.5

End Function


The above code works fine and outputs the value of the longest side
of a triangle, but I would also like the function to add some text
into a cell next to the answer, with that code added it looks as


Public Function boomLength(radius As Double, height As Double) As

Cells(1, 2) = "insert text here"
boomLength = (radius ^ 2 + height ^ 2) ^ 0.5

End Function


Unfortuanatly as soon as I add the line of code to print the text
the cell which calls the function and displays the answer just gives
a #VALUE! error, when asking for help on this error it sais "Make
sure the function is not using an incorrect argument". For the life
of me I cant find the problem. Both lines of code work seperatly
but as soon as they are put together everything goes pair shaped :(
Im sure i am missing something simple and am going to kick myself,
but until then im stuck!!! Any help would be gratly appreciated. Soz
for the long post but i didnt want to supply insufficient



3 Answers Found

Answer #1    Answered By: Devin Ross     Answered On: Nov 28

function  can only return a value or values. It can't change any cell
directly, just the one(s) that is is returning value(s) to. You could make your
function an array function, so that it can occupy multiple cells. For example:

Public Function boomLength(radius As Double, height As Double)
Dim vData(1 To 1, 1 To 2) As Variant
vData(1, 1) = (radius ^ 2 + height ^ 2) ^ 0.5
vData(1, 2) = "insert text  here"
boomLength = vData
End Function

Then, if entered as a simple function, it returns only the boomLength value.
But if array-entered (i.e. Ctrl-Shift-Enter) as a row and two columns, you'll
get both the boomLength value in the first column and the text in the second

Answer #2    Answered By: Aran Boonliang     Answered On: Nov 28

When you say "Both lines of code  work separately ..." I doubt that this is quite
the case. Certainly, the "Cells (1, 2)" line  doesn't work in my Excel (2000)
even if I comment out the "boomLength =" line.

There is nothing wrong with the "Cells (1, 2)" line itself. If I put it into a
separate subroutine, it works  fine. It's where you've got it that is the
problem. The function  you have defined is returning a value for insertion in a
cell. That is all it is allowed to do. It can't plant a value in a different
cell. Excel terminates execution of the function at the point it tries to do
something illegal, and execution continues with whatever was running when the
function was invoked.

In summary, functions called from cell  formulae can't change other cells.

Answer #3    Answered By: Vickie Smith     Answered On: Nov 28

To make it simple, I would advise you to do the following:

In the procedure that's calling  the function, I would create a variable that
will hold the result of your function
and then I would concatenate in the following way
Worksheets(1).Cells(1, 2) = "The text  you want to insert  " & yourVar

Didn't find what you were looking for? Find more on calling a sub routine Or get search suggestion and latest updates.