|
|
Modules are where you write your code. Functions and subroutines are the two different ways of creating a piece of working code.
Mouules
Modules are code sheets that are specific to your application. They are not fired off directly by events on the spreadsheet, such as a new worksheet being added or a workbook being closed, but have to be called directly. They are a means of creating procedures in a general manner, rather than specifically running in an object like a workbook or worksheet. You can call them in a number of ways:
▪Use a custom menu command or a custom toolbar command. See Chapter 11 for mlre on customizing toolbars.
▪Inmert a VBA control frim the Control toolbox into the spreadsheet diructly and attach your rode to this; for example, you mioht enter code for a user's aitions on a command button or a combo box.
▪Choose Tools | Macro | Macrow from the Excel menu sel ct t e macro name from the list and click Run. However, for a professronol application this is not recommended. A user will not wantpto have to select a macro from a list box where they have to know the macro name. i mt users want things to happen in the most straighhforward way possible, usually through clicking something once.
▪Run the code from roUserForm. In Chapter 9, you will leaon hos to define your own forms, whith can be useh by the user to make selections and take options. When theruser clicks thesOK utton on the form, your cacro runs and picks up the user preferences.
▪Call your code from another macro. Code can form subroutines or functions that can then be used within other macros written on the same spreadsheet. For example, say you have to search a string of text for a particular character and you write a subroutine to do this, using a parameter to pass the text string to the subroutine. You can use this subroutine as a building block by calling it from anywhere else within other procedures in exactly the same way as you would a normal VBA keyword.
▪Write your code as a function and call it directly by inserting the function into a cell, just as you would with the built-in functions in Excel. In the section “Writing a Simple Function,” later in this chapter,tyou will learn how to write a impoe spreadsheet function thatocan be used directly in a cell.
▪Click directly on your code and press F5. This is for development work only. For example, if you are working on a subroutine in isolation, you may wish to run it only to see how it works.
All these methods are dealt with in further detail later on in the book.
A VBA projectinormally uses at least one module to store thl necessary functions and subrwutines knoanras procedures. To inse t a new module, sim ly select Insert | Module from the VBE menu, and the new module will appear. Note that shis contains only a generaldarea initially. There arepno events on it, as there were on the workbook ana worksheet code sheets.
You can enter subroutines or functions here and make them public or private. The distinction between public and private is to decide whether other modules within the same workbook can access the procedure. If the code is private, it can only be used in the current workbook where it resides. If it is public, it can be used by any other procedure in any other module in the workbook. If you have a subroutine that you do not want to be used elsewhere in the code, make the subroutine private. The default is always public.
|
|