Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
Hello,
As a novice, I would like to know if someone can tell me what the guidelines/best programming practices are on when one should use a "Function" versus a "Subroutine". It appears to me that both structures are completely interchangeable. Is there any speed, memory overhead, or other performance issues? Perhaps someone can offer a short discussion, or point me to web site? Thanks for the help in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
Mars,
The only difference between a Function procedure and a Sub procedure is that function procedure can return to its caller a value as the result. For example, Result = SomeFunction (X, Y, Z) A Sub procedure cannot return a result. Beyond that, functions and subs are interchangeable. Neither has a speed or memory advantage over the other. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mars" wrote in message ... Hello, As a novice, I would like to know if someone can tell me what the guidelines/best programming practices are on when one should use a "Function" versus a "Subroutine". It appears to me that both structures are completely interchangeable. Is there any speed, memory overhead, or other performance issues? Perhaps someone can offer a short discussion, or point me to web site? Thanks for the help in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
Hi
No differences when it comes to performance as far as I know. Functions perform "calculations" and return "results". You can use their non-macro power in Excel cells or call them from Subs or other functions. Subs perform "actions", return nothing, and call functions when needed. However, you might want a Sub to go check on something and return a result, or simply report its own success, for decisions. If so rewrite it to a function, like the function F1 he Sub Test() If F1 < 0 Then Call S2 Else Call S3 End If End Sub Function F1() As Long Range("C14").Select On Error Resume Next F1 = Val(ActiveCell.Formula) End Function Sub S2() MsgBox "Values man" End Sub Sub S3() MsgBox "Nothin" End Sub -- HTH. Best wishes Harald Followup to newsgroup only please. "mars" wrote in message ... Hello, As a novice, I would like to know if someone can tell me what the guidelines/best programming practices are on when one should use a "Function" versus a "Subroutine". It appears to me that both structures are completely interchangeable. Is there any speed, memory overhead, or other performance issues? Perhaps someone can offer a short discussion, or point me to web site? Thanks for the help in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
Functions cannot change their environment (alter cells other than the
one containing the formula, change cell format, etc) If you need a value (or an array of values) back, then use a function. If you want to create charts, modify worksheets, etc., then use a sub. Jerry mars wrote: Hello, As a novice, I would like to know if someone can tell me what the guidelines/best programming practices are on when one should use a "Function" versus a "Subroutine". It appears to me that both structures are completely interchangeable. Is there any speed, memory overhead, or other performance issues? Perhaps someone can offer a short discussion, or point me to web site? Thanks for the help in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
Functions cannot change their environment (alter cells other than the
Just for complete accuracy, it should be said that this is true only if the function is called from a worksheet cell. No procedure, either Function or Sub, can change the environment if called from a worksheet cell. Functions called from Sub procedures can certainly change their environment so long as code execution was not initiated from a worksheet cell. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jerry W. Lewis" wrote in message ... Functions cannot change their environment (alter cells other than the one containing the formula, change cell format, etc) If you need a value (or an array of values) back, then use a function. If you want to create charts, modify worksheets, etc., then use a sub. Jerry mars wrote: Hello, As a novice, I would like to know if someone can tell me what the guidelines/best programming practices are on when one should use a "Function" versus a "Subroutine". It appears to me that both structures are completely interchangeable. Is there any speed, memory overhead, or other performance issues? Perhaps someone can offer a short discussion, or point me to web site? Thanks for the help in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
Hi Jerry
That's true only when called from cells: Sub Test() Dim L As Long Dim M As Long For L = 1 To 12 Cells(L, 1).Value = L Next Cells((Rnd() * 12 + 1), 1).Select MsgBox "Funcion call:", , _ "Row " & ActiveCell.Row M = CelVal MsgBox "Was " & M & _ " -but I lost it to a function" End Sub Function CelVal() As Long ActiveCell.EntireColumn.Font.Bold = _ Not (ActiveCell.Font.Bold) CelVal = ActiveCell.Value ActiveCell.EntireRow.Delete End Function Best wishes Harald Followup to newsgroup only please. "Jerry W. Lewis" wrote in message ... Functions cannot change their environment (alter cells other than the one containing the formula, change cell format, etc) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Vs. Sub
There are a few scenarios where your procedure must be a sub:
• to allow a user to run the procedure as a macro i.e. Tools, Macros, Macro: only public subs in standard modules without Option Private Module will appear in the macro list; • to assign a procedure to a control (Forms toolbox), shape, hyperlink, etc; • events provided by the application (Workbook_Open, Worksheet_Change, etc) are always subs and can't be changed to functions. My personal preference is to use functions for all but my top level procedures, using a Boolean = True return value by default. The calling procedure can choose to ignore a return value, effectively treating it as a sub. Also worth pointing out are the other sub procedure types available to you: Property Get, and Property Let (Property Set for objects). "mars" wrote in message ... Hello, As a novice, I would like to know if someone can tell me what the guidelines/best programming practices are on when one should use a "Function" versus a "Subroutine". It appears to me that both structures are completely interchangeable. Is there any speed, memory overhead, or other performance issues? Perhaps someone can offer a short discussion, or point me to web site? Thanks for the help in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |