Solved: Understanding Functions vs. Subs

[YellowLabPro]

I am reading VBA for Dummies by John W. If anyone has this book and can help draw these two ideas together and explain this concept a little bit more? :dunno

Pg. 64-67
From what I have read, Functions need to be run from either a Sub-Procedure, Function Procedure or in a Worksheet formula.
Pg. 64 The Function CubeRoot is written out. I recognize that this cannot be run stand alone.
Function CubeRoot(number)
CubeRoot = number ^ (1 / 3)

End Function

Pg. 66 The Sub:

Sub Cube()
Dim Num As Long
Num = InputBox("Enter a positive number")
MsgBox "The Cube Root is " & Num ^ (1 / 3)


End Sub


I placed both of these Procedures in the same Module w/ the Function first and the Sub second:

Option Explicit
Function CubeRoot(number)
CubeRoot = number ^ (1 / 3)

End Function
Sub Cube()
Dim Num As Long
Num = InputBox("Enter a positive number")
MsgBox "The Cube Root is " & Num ^ (1 / 3)


End Sub


What I am unclear on what is the Function's role and the association to the Sub, is if the Function is necessary? I do not see any reference to the Function in the Sub. If I remove the Function from the module, the Sub still runs and performs the Function, finding the cube root in the MsgBox.

[YellowLabPro]

To Add to my original post:
Pg. 72 the Function by itself is asked to be run. Upon running it, the Maros Box opens, looking for a Sub, but this one for the Function by itself is not listed, and so from I can tell, cannot be run as a stand alone.


Function CubeRoot(number)
CubeRoot = number ^ (1 / 3)

End Function

[OBP]

The major difference between subs and functions is that subs are used to automatically carry out any action that you can do manually, copy, paste move, open and close workbooks, switch sheets, etc as well as calculations.
Functions are used to return a value from some calculations to either to a sub or a cell.

[mdmackillop]

Hi YLP
Here's a sample.
Oncve youve created your functions, the same code can be called from different subs or used by the spreadsheet as shown.
Regards
MD


Option Explicit

Function CubeRoot(Number As Double)
CubeRoot = Number ^ (1 / 3)
End Function

Sub Cube()
Dim Num As Double
Num = InputBox("Enter a positive number")
MsgBox "The Cube Root of " & Num & " is " & CubeRoot(Num)
End Sub

[YellowLabPro]

Hi OBP,
Thanks, I understand the difference. I am trying to figure out how they work together. For instance, in JW's book, the next section is Calling a function from a worksheet formula.
For brevity sake I will just type here what the book is instructing to do:

Activate a worksheet in the same workbook tht holds the CubeRoot Function definition. Enter the following formula in any cell:
=CubeRoot(1728)

Upon doing this, I receve a #Name Error in the Excel workbook. I know I am missing something, just not sure what.

thanks,
YLP

[YellowLabPro]

Hi MD,
So by your example, then we are creating our own function, CubeRoot is not a function of Excel or VBA, it this correct?

[mdmackillop]

You could use a native function within your own function in conjunction with other manipulation.

[YellowLabPro]

MD,
I think I see the logic behind functions now.
Now how about me calling the cuberoot function from w/in Excel.
I posted this one just a little earlier, I get the #Name? error.
Can the worksheet call the function?
I might not understand this line from the book-


Activate a worksheet in the same workbook tht holds the CubeRoot Function definition. Enter the following formula in any cell:
=CubeRoot(1728)


I put =cuberoot(1728) in C14

[lucas]

If you put the function:

Function CubeRoot(number)
CubeRoot = number ^ (1 / 3)

End Function

in a plain module and then enter the formula:

=cuberoot(1728)

in a cell and hit enter you should get 12
Hope I understand the question Yelp

[mdmackillop]

Function CubeRoot must be save in a standard module within the same workbook. If you were to save it in Personal, then you would enter =Personal.xls!CubeRoot(1728) in a cell.

[mdmackillop]

Yelp
:laugh2:

[Cyberdude]

Function CubeRoot must be save in a standard module within the same workbook. If you were to save it in Personal, then you would enter =Personal.xls!CubeRoot(1728) in a cell. Malcolm, I reference functions stored in Personal.xls all the time without a qualifying prefix. If it were in any other workbook, then, yes, I would have to qualify the reference.

No one mentioned an exceedingly important point about functions. If you want to reference a VBA function from a worksheet, then that function cannot change any cell on the worksheet. Excel is quite rigid about enforcing that rule. However, that restriction does not apply to referencing the function from another macro.

Also, when referencing a function on a worksheet, its name MUST be followed by a set of parentheses ... ( ) , even if there are no arguments. I'm always forgetting that.

[Cyberdude]

Hey, yelp, I forgot to ask you if you ever solved the #NAME? error? I'm curious about that. Usually that means Excel can't find the function or you misspelled its name. If you had violated the can't-write-on-anything rule, you get a #VALUE error.

[mdmackillop]

I reference functions stored in Personal.xls all the time without a qualifying prefix.
Hi Sid,
I can't get this to work without the reference.

http://office.microsoft.com/en-gb/assistance/HA011117011033.aspx#Using

Making your custom functions available anywhere

To use a custom function, the workbook containing the module in which you create the function must be open. If that workbook is not open, you get a #NAME? error when you try to use the function. Even if the workbook is open, if you use the function in a different workbook, you must precede the function name with the name of the workbook in which the function resides. For example, if you create a function called Discount in a workbook called Personal.xls, and you call that function from another workbook, you must write =personal.xls!Discount(), not simply =Discount().

[Cyberdude]

OK, I read your reference and it just occurred to me that I have Personal.xls checked in the References list. That's why my functions are always found.
So let me urge everyone who has a Personal.xls to include it in your Reference list.
Sorry about the put down, Mal!

[YellowLabPro]

Thanks everyone for your responses.
MD, I down loaded your file and it appears I have mine setup correctly after comparing it to yours, just not so many other cool addtional features. I am including it here, can you or someone else have a look and see if you can spot the problem?
I am referencing the function in C14.

Thanks,
YLP....

[mdmackillop]

Hi Yelp,
Don't use a function/sub name as a module name. Change the name of the CubeRoot module and things should be OK
Regards
MD

[YellowLabPro]

Thanks MD,
That worked.... So that is an interesting twist. I have not read anything anywhere to date indicating this parameter.

And I did get your first post w/ Yelp.... I just did not have a good response.... :clap:


YLP

[mdmackillop]

It's not something I recall seing either. I used Insert/Function/User Defined which showed the full address of the Function which gave me the clue.

[YellowLabPro]

Where did you perform the Insert/Function/User Defined?

[mdmackillop]

I used it to insert a function in an empty cell, but I can only get it wo work on your last sample. The function I get is
='VBA Code Lesson_Examples Dummy.xls'!CubeRoot3.CubeRoot3(18)

[YellowLabPro]

Hi MD,
Your last error checking solution is beyond me currently. I am content w/ just you helping me to resolve my post.

Thanks,

YLP

[mdmackillop]

The Insert/Function is from the main Excel menu, not a VBA solution