Im very new to Excel and I've found the ability to program functions very
useful and a good way to give me a break from working while still working. I
see many people here asking questions and getting answers with code that
seems to be a 'sub'. I'd love to start learning how to program them but I
dont know how to implement them into Excel and why they are more useful than
functions (other than the ability to obviously be far more complex) Oddly
enough I didn't quite see this question being asked anywhere. Thank you for
your time

Mark

When you speak of functions and subs, the only difference is that a function
can return a value. If you write a function and don't return a value, even
though you might declare it as function, it could just as well be declared as
sub.

There is no difference in what can be contained in the function.

that is all true when you are going to only call these procedures from VBA.
You can make a special type of Function called a User Defined Function. this
Function can be used in the worksheet like a built in function. In this
case, when used this way, it has certain restrictions on what type of actions
it can perform. In terms of affecting the excel environment, except for a
few anomalies, it can only return a value to the cell in which the function
is placed - just like built-in functions.

for both subs and functions that don't return values (or the retuned value
is not used), the arguments are not placed in parentheses unless you preface
it with the call function

Sub myMacro(arg1, arg2)

is called with

MyMacro arg1, arg2
or
Cal MyMacro(arg1, arg2)

likewise if I had a function
Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

could be used

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

This can not be uses as a UDF in the worksheet because it sets a value in a
cell. If I commented out that line, it could be used in a cell.

--
Regards,
Tom Ogilvy



"Abode" wrote:

Im very new to Excel and I've found the ability to program functions very
useful and a good way to give me a break from working while still working. I
see many people here asking questions and getting answers with code that
seems to be a 'sub'. I'd love to start learning how to program them but I
dont know how to implement them into Excel and why they are more useful than
functions (other than the ability to obviously be far more complex) Oddly
enough I didn't quite see this question being asked anywhere. Thank you for
your time

Mark

Awesome. Thanks for the explination. I tried entering your example in Excel
and nothing happened though. Maybe Im doing it all wrong. I entered:

Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

In a new Module. Then in Cell D1 I inserted all three

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

One at a time with the comments taken out. Is it susposed to work like
that?! If I get a small start with these subs I'd probably be able to take
them a long way. I've already made some very useful functions in VBA without
ever really learning Visual Basic.



"Tom Ogilvy" wrote:

When you speak of functions and subs, the only difference is that a function
can return a value. If you write a function and don't return a value, even
though you might declare it as function, it could just as well be declared as
sub.

There is no difference in what can be contained in the function.

that is all true when you are going to only call these procedures from VBA.
You can make a special type of Function called a User Defined Function. this
Function can be used in the worksheet like a built in function. In this
case, when used this way, it has certain restrictions on what type of actions
it can perform. In terms of affecting the excel environment, except for a
few anomalies, it can only return a value to the cell in which the function
is placed - just like built-in functions.

for both subs and functions that don't return values (or the retuned value
is not used), the arguments are not placed in parentheses unless you preface
it with the call function

Sub myMacro(arg1, arg2)

is called with

MyMacro arg1, arg2
or
Cal MyMacro(arg1, arg2)

likewise if I had a function
Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

could be used

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

This can not be uses as a UDF in the worksheet because it sets a value in a
cell. If I commented out that line, it could be used in a cell.

--
Regards,
Tom Ogilvy



"Abode" wrote:

Im very new to Excel and I've found the ability to program functions very
useful and a good way to give me a break from working while still working. I
see many people here asking questions and getting answers with code that
seems to be a 'sub'. I'd love to start learning how to program them but I
dont know how to implement them into Excel and why they are more useful than
functions (other than the ability to obviously be far more complex) Oddly
enough I didn't quite see this question being asked anywhere. Thank you for
your time

Mark

Fair enough comment. I was just trying to give a good high level overview
without getting into UDF's and such. I see a lot of people interchanging sub
and functions in ways that don't seemed to be grounded in any sort of
rational thought. IMO just because they can be used interchangably does not
mean that they should. All of that being said I come from a C programming
background where everything is a function and I still find myself at times
not practicing what I preach... :-)
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Jim provides a good philosophical view of functions and subs, but in my
opinion, it mixes the restrictions imposed by UDF's on functions in general -
which VBA/VB does not. VBA sees them both as procedures and as I said, when
used in VBA, then they are exactly the same except that a function can return
a value.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

A sub is a set of instructions used to perform an action. In theory you
should try to write your subs to do one thing. For example you could write a
sub to format a sheet of data, or the sub could protect all of the sheets in
a workbook. When your subs start doing too many things they become very
difficult to debug.

A function on the other hand returns a value. In theory a function should
avoid having side effect such as changing any of the data that it is
accessing. For example Sum is a function which returns the total of all of
the numbers that are passed into it. It does not change any of the numbers
that are passed into it as that would be an unwanted side effect.

Very often your subs will call functions in order to perform the actions
that they must do, but rarely will a function call a sub, as the sub would
produce a side effect.
--
HTH...

Jim Thomlinson


"Abode" wrote:

Im very new to Excel and I've found the ability to program functions very
useful and a good way to give me a break from working while still working. I
see many people here asking questions and getting answers with code that
seems to be a 'sub'. I'd love to start learning how to program them but I
dont know how to implement them into Excel and why they are more useful than
functions (other than the ability to obviously be far more complex) Oddly
enough I didn't quite see this question being asked anywhere. Thank you for
your time

Mark

Jim provides a good philosophical view of functions and subs, but in my
opinion, it mixes the restrictions imposed by UDF's on functions in general -
which VBA/VB does not. VBA sees them both as procedures and as I said, when
used in VBA, then they are exactly the same except that a function can return
a value.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

A sub is a set of instructions used to perform an action. In theory you
should try to write your subs to do one thing. For example you could write a
sub to format a sheet of data, or the sub could protect all of the sheets in
a workbook. When your subs start doing too many things they become very
difficult to debug.

A function on the other hand returns a value. In theory a function should
avoid having side effect such as changing any of the data that it is
accessing. For example Sum is a function which returns the total of all of
the numbers that are passed into it. It does not change any of the numbers
that are passed into it as that would be an unwanted side effect.

Very often your subs will call functions in order to perform the actions
that they must do, but rarely will a function call a sub, as the sub would
produce a side effect.
--
HTH...

Jim Thomlinson


"Abode" wrote:

Im very new to Excel and I've found the ability to program functions very
useful and a good way to give me a break from working while still working. I
see many people here asking questions and getting answers with code that
seems to be a 'sub'. I'd love to start learning how to program them but I
dont know how to implement them into Excel and why they are more useful than
functions (other than the ability to obviously be far more complex) Oddly
enough I didn't quite see this question being asked anywhere. Thank you for
your time

Mark

As I said, you can't put it in a worksheet because it doesn't conform to the
UDF restrictions. Plus I had a serious typo in my example

In a general module put in:
Function Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

What I was showing was how to use it in a VBA itself. I would have a main
Sub which performs some actions, but I might have a multiple step action that
I perform several times in that sub. Rather than reproduce the code several
times, I can put it in a function (if it will return a value or not) or in
another SUB (if it won't return a value - although that can be overcome) and
then call it from my sub.

so now put in a sub to call it (as a demonstration)

Sub MySub()
dim res, a, b
a = 10
b = 20
Worksheets("Sheet1").Activate
res = MyFunction(a,b)
msgbox "MyFunction returned: " & Res
End sub

then go to Tools=Macros and run MySub

Now here is a function that you can use in your worksheet. Put this in a
general module as well (Insert=Module in the VBE)

Function SheetNme(Optional ChkCell As Variant) As String
On Error GoTo error_handler

If IsMissing(ChkCell) Then
SheetNme = Application.Caller.Parent.Name
Exit Function
Else
SheetNme = ChkCell.Parent.Name
End If
Exit Function

error_handler:
SheetNme = "bad data"

End Function

then in the worksheet (sheet1), in one cell put in

=SheetNme()
in the next cell
=SheetNme(Sheet3!A1)

This function can be used as a UDF. Because it uses Application.Caller, it
can not be called from VBA without raising an error.

You could adjust it to allow calling from a VBA or a Worksheet

Function SheetNme1(Optional ChkCell As Variant) As String
Dim rng As Range

On Error Resume Next
Set rng = Application.Caller
On Error GoTo error_handler
If IsMissing(ChkCell) Then
If rng Is Nothing Then
SheetNme1 = ActiveSheet.Name
Else
SheetNme1 = rng.Parent.Name
End If
Exit Function
End If
SheetNme1 = ChkCell.Parent.Name

Exit Function

error_handler:
If rng Is Nothing Then
MsgBox Err.Number & vbLf & Err.Description
Else
SheetNme1 = "bad data"
End If
End Function

Testing it from the immediate window:
? sheetnme1()
Sheet1
? sheetnme(worksheets(3).Range("B9"))
Sheet2
' now in the worksheet
ActiveCell.Formula = "=SheetNme1()"
? activeCell.Text
Sheet1


Using the immediate window (in the VBE, go to View, then select immediate
window) I can simulate calling the function or sub from a sub (or function).

This is getting kind of long winded for a posting, so you might want to get
a book such as those written by John Walkenbach
http://www.j-walk.com/ss/excel on the right are links to his books and also
links to sample code and other sites.


Dave McRitchie has some likes to Tutorials (VBA are after the Excel
tutorials):
http://www.mvps.org/dmcritchie/excel/getstarted.htm


http://msdn.microsoft.com/office/und...d/default.aspx

--
Regards,
Tom Ogilvy


"Abode" wrote:

Awesome. Thanks for the explination. I tried entering your example in Excel
and nothing happened though. Maybe Im doing it all wrong. I entered:

Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

In a new Module. Then in Cell D1 I inserted all three

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

One at a time with the comments taken out. Is it susposed to work like
that?! If I get a small start with these subs I'd probably be able to take
them a long way. I've already made some very useful functions in VBA without
ever really learning Visual Basic.



"Tom Ogilvy" wrote:

When you speak of functions and subs, the only difference is that a function
can return a value. If you write a function and don't return a value, even
though you might declare it as function, it could just as well be declared as
sub.

There is no difference in what can be contained in the function.

that is all true when you are going to only call these procedures from VBA.
You can make a special type of Function called a User Defined Function. this
Function can be used in the worksheet like a built in function. In this
case, when used this way, it has certain restrictions on what type of actions
it can perform. In terms of affecting the excel environment, except for a
few anomalies, it can only return a value to the cell in which the function
is placed - just like built-in functions.

for both subs and functions that don't return values (or the retuned value
is not used), the arguments are not placed in parentheses unless you preface
it with the call function

Sub myMacro(arg1, arg2)

is called with

MyMacro arg1, arg2
or
Cal MyMacro(arg1, arg2)

likewise if I had a function
Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

could be used

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

This can not be uses as a UDF in the worksheet because it sets a value in a
cell. If I commented out that line, it could be used in a cell.

--
Regards,
Tom Ogilvy



"Abode" wrote:

Im very new to Excel and I've found the ability to program functions very
useful and a good way to give me a break from working while still working. I
see many people here asking questions and getting answers with code that
seems to be a 'sub'. I'd love to start learning how to program them but I
dont know how to implement them into Excel and why they are more useful than
functions (other than the ability to obviously be far more complex) Oddly
enough I didn't quite see this question being asked anywhere. Thank you for
your time

Mark

If you want a Function then it's not a sub. Try

Function Myfunction(Arg1, Arg2) As Double
Myfunction = Arg1 * Arg2
End Function

and in cell D1

=Myfunction(A1,B1)

Functions in Excel are found in the function wizard, User defined category.

A normal sub i more the kind of

Sub DestroySomething()
Sheets(1).Select
MsgBox "Will delete this sheet ! Ha ha ha ha", vbInformation
Sheets(1).Delete
If MsgBox("Another ?", vbYesNo + vbQuestion, "Delete more?") = _
vbYes Then Sheets(1).Delete
End Sub

Subs are found in the Tools Macro Run menu in Excel.

In real life, and especially in object oriented programming, the techniques
overlap and a sub is just as often a function returning nothing. But to
simplify: Functions think, Subs do.

HTH. Best wishes Harald

"Abode" skrev i melding
...
Awesome. Thanks for the explination. I tried entering your example in
Excel
and nothing happened though. Maybe Im doing it all wrong. I entered:

Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

In a new Module. Then in Cell D1 I inserted all three

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

One at a time with the comments taken out. Is it susposed to work like
that?! If I get a small start with these subs I'd probably be able to
take
them a long way. I've already made some very useful functions in VBA
without
ever really learning Visual Basic.



"Tom Ogilvy" wrote:

When you speak of functions and subs, the only difference is that a
function
can return a value. If you write a function and don't return a value,
even
though you might declare it as function, it could just as well be
declared as
sub.

There is no difference in what can be contained in the function.

that is all true when you are going to only call these procedures from
VBA.
You can make a special type of Function called a User Defined Function.
this
Function can be used in the worksheet like a built in function. In this
case, when used this way, it has certain restrictions on what type of
actions
it can perform. In terms of affecting the excel environment, except for
a
few anomalies, it can only return a value to the cell in which the
function
is placed - just like built-in functions.

for both subs and functions that don't return values (or the retuned
value
is not used), the arguments are not placed in parentheses unless you
preface
it with the call function

Sub myMacro(arg1, arg2)

is called with

MyMacro arg1, arg2
or
Cal MyMacro(arg1, arg2)

likewise if I had a function
Sub Myfunction(arg1, arg2)
myFunction = arg1 * arg2
cells(1,2) = arg1*arg2
End Function

could be used

MyFunction arg1, arg2 ' no value returned
Call MyFunction(arg1, arg2) ' no value retuned
res = MyFunction(arg1, arg2) ' value returned.

This can not be uses as a UDF in the worksheet because it sets a value
in a
cell. If I commented out that line, it could be used in a cell.

--
Regards,
Tom Ogilvy



"Abode" wrote:

Im very new to Excel and I've found the ability to program functions
very
useful and a good way to give me a break from working while still
working. I
see many people here asking questions and getting answers with code
that
seems to be a 'sub'. I'd love to start learning how to program them
but I
dont know how to implement them into Excel and why they are more
useful than
functions (other than the ability to obviously be far more complex)
Oddly
enough I didn't quite see this question being asked anywhere. Thank
you for
your time

Mark