Macro, Module, function, sub and This workbook and sheets ?

[Hari]

Hi,

For the last couple of days I have been indiscriminately pinching codes from
NG and pasting it in to This workbook or by inserting new modules without
understanding which should be used for what.

I am a newbie and want to learn VBA with excel.

Couple of questions based on this:-

1. What is the difference between Sub, Function, Module and code.

2. If I get a code ( from NG) where should I add it . Should I add it "This
workbook" or a new module. If possible please tell me in what circumstances
one should be adding the code in which of the above 2

3. What is the difference between Public, private with reference to both sub
and function.

4. When do we add codes to "Sheet1" rather than workbook.

5. Based on info from newsgroups I learned that a Macro/Module ( I believe
macro is recorded and module is anything which is written, please correct me
if am wrong) could be called. Can we call both a macro and function from
another function/macro

6. Can a module in sheet call a module in another shet/workbook or from
module 1.

Please guide me to the right source for learing these bsic concepts if
possible.

Regards,
Hari
India

 

[Frank Kabel]

Hi
some starting points:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for macros in general.

ThisWorkbook and the worksheet modules are used for event procedures.
See:
http://www.mvps.org/dmcritchie/excel/event.htm
http://www.cpearson.com/excel/events.htm

Now to your questions:
1. See the first link
2. Normal macros are added to a standard module. Only event procedures
go to the other modules.
3. Public / private: Public functions/variables can be accessed from
the outside of this module, private functions/subs not
4. See event procedures
5. Modules store macros whether they are recorded or manually written.
And yes you can call both subs and functions (with respect to
public/private)
6. Yes. You have to reference the workbook for this.

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi,

For the last couple of days I have been indiscriminately pinching codes from
NG and pasting it in to This workbook or by inserting new modules without
understanding which should be used for what.

I am a newbie and want to learn VBA with excel.

Couple of questions based on this:-

1. What is the difference between Sub, Function, Module and code.

Subs and Functions are code procedures that will do something. A sub will
perform its actions, and exit quietly, whereas a Function will do it stuff,
but is usually used to return a result. For instance a sub is invoked like
this

runMySub
or
Call runMySub

whereas typically a function is called like this

myResult = runMyFunction

Just to confuse the matter, whilst a fuynction can return a result, it
doesn't have to, so it is possible just to use functions and never use subs.

A module is a con tainer within the VBE for holding the subs and functions,
and global variables, etc.

2. If I get a code ( from NG) where should I add it . Should I add it "This
workbook" or a new module. If possible please tell me in what circumstances
one should be adding the code in which of the above 2

That depends, workbook event code should go in the ThisWorkbook code module,
worksheet event code goes in the particular sheet code module, userform code
goes in the form class module, and most other code goes in a standard code
module. All can hold Subs or functions.

3. What is the difference between Public, private with reference to both sub
and function.

A public sub/function can be invoked from another module, a private one
cannot.

4. When do we add codes to "Sheet1" rather than workbook.

Explained above.

5. Based on info from newsgroups I learned that a Macro/Module ( I believe
macro is recorded and module is anything which is written, please correct me
if am wrong) could be called. Can we call both a macro and function from
another function/macro

Absolutely wrong. A macro is a generic term for a code procedure, that is a
sub or a function. When you record a macro it will defaul;t to a sub. As I
said above, a module is a container for subs and functions. A recorded macro
will also be assigned to a module.

You can call a sub or afunction from another sub or function, or even one
or more of both types.

6. Can a module in sheet call a module in another shet/workbook or from
module 1.

Modules can't call anything. See above.

 

[Nigel]

Hi,

For the last couple of days I have been indiscriminately pinching codes from
NG and pasting it in to This workbook or by inserting new modules without
understanding which should be used for what.

I am a newbie and want to learn VBA with excel.

Couple of questions based on this:-

1. What is the difference between Sub, Function, Module and code.

Sub is a procedure of collection of code that can be executed from within a
module, workbook, or worksheet
Function is a special from of sub that returns variables, and is most often
(but not exclusively) on a worksheet to provide functions not in the
standard Excel functions list.
Module is one lace to store Subs and Functions
Code is a group of VBA instructions, making up a Sub or Function

2. If I get a code ( from NG) where should I add it . Should I add it "This
workbook" or a new module. If possible please tell me in what circumstances
one should be adding the code in which of the above 2

The place depends on the purpose of the code on the one hand and the project
design on the other. In most cases code would be placed in modules, but if
the code is triggered by a workbook, worksheet or userform event then it is
placed in the the respective area.

3. What is the difference between Public, private with reference to both sub
and function.

In general, think of Public as refering to variables that are available to
all codes, and Private to the codes residing in the same place.

4. When do we add codes to "Sheet1" rather than workbook.

See note above ref 2. If the code is specfic for a worksheet it will need
to be placed the sheet it is triggered from, not just "Sheet1"

5. Based on info from newsgroups I learned that a Macro/Module ( I believe
macro is recorded and module is anything which is written, please correct me
if am wrong) could be called. Can we call both a macro and function from
another function/macro

Macro is a general term for code (more commonly called a Program), most
often meaning a Sub. Wherever it is stored and however it is created -
written or recorded it makes no difference.
Yes, in general you can call a function/macro (Sub) from another Sub but not
from a function.

6. Can a module in sheet call a module in another shet/workbook or from
module 1. - Yes

Please guide me to the right source for learing these bsic concepts if
possible.

Michael Kofler - A definitive guide to Excel VBA - Apress ISBN 1-59059-103-8
or any of the other introductory and references for Excel VBA, be aware the
different versions of Excel have different features and functions. Mostly
they are forward compatible, in other words code written in an older version
will work in a newer version (not always!)

 

[Bob Phillips]

Hi
some starting points:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
for macros in general.

ThisWorkbook and the worksheet modules are used for event procedures.
See:
http://www.mvps.org/dmcritchie/excel/event.htm
http://www.cpearson.com/excel/events.htm

2. Normal macros are added to a standard module. Only event procedures
go to the other modules.

Correct in spirt Frank, but not in fact. You can add a normal macro
(whatever that may be?) to a worksheet or workbook module. I often add
procedures that are specific to event procedures in that module. And then
there are form and cl;ass modules which will have non-event procedures.

 

[David McRitchie]

Hi Hari,
For item #1 and #3 Chip Pearson has a page on the
the difference between a Macro and a Function, it should
clear up some confusions. (probably also #5)
Macros And Functions (Functions as Opposed to Macros)
http://www.cpearson.com/excel/differen.htm

I think you will get a pretty good idea of some of the topics
by looking through some Excel and VBA tutorials.
http://www.mvps.org/dmcritchie/excel/tutorials.htm

Show FORMULA or FORMAT of another cell
http://www.mvps.org/dmcritchie/excel/formula.htm

GetFormula was my first use of coding, a similar function
GetFormat was created later, I use them both a lot. And there
is a table of some formats so you can get an idea of how to modify
a format to something that might work better for you.

Installing a macro used to be part of this but are now in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm

Main purpose is for changing the letter case of cells to
Proper (title) case, lower case, upper case. Things like
Sentence Case, and Small Capitals can be found in the
related area at end. But I think more important than the
macros is that it ...

shows a bit better how to write a macro to
run faster without using arrays or other trickier techniques.
Use of SpecialCells, Intersect, and turning off screen updating
and calculation. Along with some hints at distinguishing what is a
better macro and something just dashed out to show a specific
example possible from recording a macro. It will lead you to
other pages including -- Slow Response (slowresp.htm)

Worksheets in VBA Coding and in Worksheet Formulas
http://www.mvps.org/dmcritchie/excel/sheets.htm

has some snippets of code for working with worksheets.

Coding for the Visual Basic Editor - Chip Pearson
http://www.cpearson.com/excel/vbe.htm

Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

These two are getting more attention lately: (more fun)
Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

Since mostly builtin Worksheet Formulas are used, it also
gives a pretty good overall view of many WS functions.
But you're more interested in macros right now.

Event Macros
http://www.mvps.org/dmcritchie/excel/event.htm
Event macros are installed differently.
Shortcut for ThisWorkbook is right click on logo to left
of the formula bar. Shortcut to install worksheet event macro
is right click on the worksheet tab.


"

 

[Hari]

Hi

Thanx a lot Frank, David, Nigel and Bob for answering my queries.

Im will try to digest the info u have given and will try to put it to good
use.( Hope to come to a stage where I am able to answer other's queries..)

Thanx again

Regards,
Hari
India

 

[David McRitchie]

Hi Hari,
You're welcome, you've probably already helped a lot of others
by asking some basic questions but not the same questions that
get asked over and over again.

 

[Frank Kabel]

Correct in spirt Frank, but not in fact. You can add a normal macro
(whatever that may be?) to a worksheet or workbook module. I often add
procedures that are specific to event procedures in that module. And
then there are form and cl;ass modules which will have non-event
procedures.

Hi bob
ack but I thought this would just be too much for the OP. But of course
you're right

Frank

 

[Jamie Collins]

...

Hi Hari,
For item #1 and #3 Chip Pearson has a page on the
the difference between a Macro and a Function, it should
clear up some confusions. (probably also #5)
Macros And Functions (Functions as Opposed to Macros)
http://www.cpearson.com/excel/differen.htm

The suggestion seems to be that 'Sub' is synonymous with 'Macro'. I'd
make the distinction that a macro must appear in the 'Macro' dialog
(Tools, Macro, Macro) i.e. must be a parameterless public Sub in a
public module, being a standard module with no 'Option Private Module'
statement or an object module (e.g. ThisWorkbook code module).

Jamie.

--

 

[Jamie Collins]

...

ThisWorkbook and the worksheet modules are used for event procedures.
See:
http://www.mvps.org/dmcritchie/excel/event.htm
http://www.cpearson.com/excel/events.htm

They may also be used for custom workbook- and worksheet-level
properties and methods. I know Chip and others prefer to reserve them
for events but for me they better fit the OOP approach. I never use
public variables so a public property in the ThisWorkbook is useful
for implementing a global. And I will only put code in a standard
module if there is no alternative e.g. need to call a procedure using
its address in memory.

Jamie

--

 

[Bob Phillips]

Same as I said, so I must be in agreement with Jamie<vbg>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Hi Jamie,
I think you are making up your own definition for macro.

The HELP includes lots of uses of the word macro that that
do not require that the name appear in the macro dialog box.

You have very specifically excluded a public subroutine with a parameter
as they will not appear in the macro dialog box, but you can type the name
into the macro dialog box and run it from the spreadsheet like
any other macro appearing in the dialog box.

From HELP:
The Run method returns whatever the called macro returns.
Objects passed as arguments to the macro are converted to
values (by applying the Value property to the object). This
means that you cannot pass objects to macros by using the Run method.

 

[Jamie Collins]

...

I think you are making up your own definition for macro.

You have very specifically excluded a public subroutine with a parameter
as they will not appear in the macro dialog box, but you can type the name
into the macro dialog box and run it from the spreadsheet like
any other macro appearing in the dialog box.

I tried this:

1. Create a new blank workbook.
2. Open the VBE.
3. Insert a standard .bas module.
4. Add the following code:

Public Sub Test(ByVal Arg1 As Integer)
MsgBox CStr(Arg1)
End Sub

5. Close the VBE.
6. Show the Macro dialog (Tools, Macro, Macro).
7. Note that the list of macros is empty.
8. In the Macro name textbox, type:

Test(1)

9. Hit the Run button.
10. Message appears: 'Reference is not valid.'

I tried a few variations on this theme, e.g. change the parameter to
ByRef, calling the code without parentheses, using a cell reference
(why have I only just spotted it is a RefEdit control rather than a
textbox?) etc, but I can't get the code to run from the macro dialog.

Am I missing something?

Cheers,
Jamie.

--

 

[David McRitchie]

Hi Jamie,
Your example rewritten
You will not fine Test in the macro dialog box,
but you can run Test and you can run Test2 from the macro dialog box.
You cannot include a parameter from the macro dialog box,
you can only use that parameter from another macro.

Public Sub Test(Optional ByVal Arg1 As Integer)
MsgBox CStr(Arg1) & " --- so what"
End Sub
Sub Test2()
Test (4444)
End Sub

You will see an example of this in
http://www.mvps.org/dmcritchie/excel/proper.htm

Sub Proper_case()
'-- This macro is invoked by you -- i.e. from Macro Dialog (Alt+F8)
proper_case_inner 'The macro you invoke from a menu is Proper_Case
end sub
Sub Proper_Case_Inner(Optional mySelection As String)

 

[Jamie Collins]

...

Hi Jamie,
Your example rewritten
You will not fine Test in the macro dialog box,
but you can run Test and you can run Test2 from the macro dialog box.
You cannot include a parameter from the macro dialog box,
you can only use that parameter from another macro.

Public Sub Test(Optional ByVal Arg1 As Integer)
MsgBox CStr(Arg1) & " --- so what"
End Sub
Sub Test2()
Test (4444)
End Sub

David,

I don't see much difference between

"can have optional parameters, provided they are not used"

and

"must be parameterless"



Jamie.

--

 

[David McRitchie]

It was kind of hard to follow your quotes, out of context.
But I already pointed out that you CAN use a macro that
has optional parameters from the macro dialog box even
though you do not see the macro in the macro dialog box

You quotes are out of context, so I it is hard to follow what you are
trying to say. You original statement stated that a macro had
to be in the macro dialog box to be a macro and that is incorrect.

The second was from you, and is incorrect -- you can use macros
from the Macro Dialog box even if you can't see it -- does not say
that you can use all macros from any project, just that you can use
macros from the macro dialog box even though they don't appear there.

The suggestion seems to be that 'Sub' is synonymous with 'Macro'. I'd
make the distinction that a macro must appear in the 'Macro' dialog
(Tools, Macro, Macro) i.e. ___must be a parameterless___ public Sub in a
public module, being a standard module with no 'Option Private Module'
statement or an object module (e.g. ThisWorkbook code module).
--

 

[Jamie Collins]

...

It was kind of hard to follow your quotes

They do look like quotes, don't they? I was paraphrasing and in a
light-hearted way at that (note the smilie). Sorry for any confusion.

Jamie.

--