Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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. -- Regards Frank Kabel Frankfurt, Germany Hari wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
"Frank Kabel" wrote in message
... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
Bob Phillips wrote:
"Frank Kabel" wrote in message ... 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. Hi bob ack but I thought this would just be too much for the OP. But of course you're right :-) Frank |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
"Frank Kabel" wrote ...
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 -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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) "Jamie Collins" wrote in message om... "Frank Kabel" wrote ... 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 -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
--
HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Hari" wrote in message ... 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. Careful, we'll have the copyright lawyers onto you<vbg 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
"Hari" wrote in message ... 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!) Regards, Hari India |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm " |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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" wrote in message ... 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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm " |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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. --- David McRitchie "Hari" wrote Thanx a lot Frank, David, Nigel and Bob for answering my queries. I will try to digest the info you 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..) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
"David McRitchie" wrote ...
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. -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Jamie Collins" wrote in message om... "David McRitchie" wrote ... 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. -- |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro, Module, function, sub and This workbook and sheets ?
"David McRitchie" wrote ...
I think you are making up your own definition for macro. True. But perhaps my definition will prove more useful <g. 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Copy Sheets to new workbook | Excel Discussion (Misc queries) | |||
Need macro to consolidate all sheets in workbook | Excel Discussion (Misc queries) | |||
Macro to create new workbook and sheets | Excel Discussion (Misc queries) | |||
VBA Code To have a macro repeat on all sheets in a workbook | Excel Worksheet Functions | |||
How to automate a macro to run on all sheets in a workbook | Excel Programming |