 Search:

# 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
Double

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
follows:

---------------------------------------------------------------------

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

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
information.

Share:

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
column.

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.

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.