add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Variables
When you run a macro, you will want to save textual data or the results of calculations for later use.
Variables are cells in the computer’s memory that are opened with their definitions when the macro begins to run and erased from memory when the macro is finished. The types of variables are created to increase memory efficiency. To save whole numbers, less memory is required than to save character text of variable length.
Common variable types – examples
Range
Size in Memory
Name of Variable Type
From number -32767 to 32767
2 Bytes
Integer
-2 billion to 2 billion
4 Bytes
Long
-3.4 billion to 1.4 billion
4 Bytes
Single
Text with any number of characters
String
Date from 1/1/100 to 31/12/9999
8 Bytes
Date
Variable by data type
Variant
True or False
2 Bytes
Boolean
Variable Declaration
In order to force yourself to work with variables, insert the words Option Explicit into the top row of every module you open (highly recommended).
1. In VBE, select Tools, Options, Editor.
2. Select the Require Variable Declaration box, and click OK.
Insert the variable declaration at the beginning of the macro. Each variable should be defined with this syntax:
For example, save the name of the workbook as a variable. You can use all character components to save the variable. The variable name is actually an address in memory.
Display a message to the user
Your program can communicate messages to the user. This communication is achieved through a Message box, also known as a MsgBox.
Displaying the results of calculations on variables in MsgBox
Example 1: Integer variables
Cells A1 and A2 contain whole numbers which, when multiplied, result in a product less than 32767.
The result of the calculation is displayed in MsgBox.
Example 2: String Variables
In MsgBox, display the file’s name and path, and the address of a cell in the sheet.
In MsgBox, the letters vbCr insert a new line into the box, and the & symbol is used to concatenate parts of the message.
Example 3: Inserting text into MsgBox
To see the structure of the MsgBox syntax, select MsgBox in the module and press F1. See the examples in Help.
Events
In many events during your work, you will certainly want a macro to run automatically without your interference, such as a macro that is run when a workbook is opened or closed.
The sheet module ThisWorkbook should be used as storage for such events. Events that are stored in this sheet are loaded into Excel’s memory when a workbook is opened, and they are run according to the type of event without your involvement while you work in the workbook.
Select the ThisWorkbook module. Above the module sheet, you will see two drop-down lists. Open the list on the left, and select Workbook. Open the list on the right and you will see a list of events.
When an event from the list is selected, the event’s framework is typed in the module.
The most common event is Workbook_Open. In the figure (see Chapter 10, Information), notice that Path has been inserted into Excel’s title bar (the blue bar), and macro (menu) creates a menu and inserts it into Excel’s menu.
Another very important event is Workbook_BeforeClose. While the workbook is being closed, this event automatically saves the workbook and deletes Path from the title bar.
You can use events for many other reasons. For example, an event called Workbook_BeforePrint inserts a line of code that handles the insertion of vital information into the top or bottom margins of every page that is printed in the workbook.
Conditional Statements – IF
The IF formula is one of the most useful formulas in Excel.
When you write a program in VBA, the use of IF is similar. The use of IF allows you to perform checks when you start a macro, continue running the program, or prevent the program from running until you have completed making the required corrections.
Syntax of IF statements
See the example below. The logical check line begins with IF and ends with THEN. If the result is positive, it is performed. If it is not, then Else. At the end, End If.
Using IF to prevent a macro from running
Attach an icon to a macro. The macro should run commands for printing a report from a specific sheet.
In order to prevent printing from a sheet that is not specified, use IF to check whether the name of the active sheet is the sheet that will be printed; you do not want to print unnecessarily pages from another sheet.
Notice the words Exit Sub. Assuming there are no lines of code after IF, you can stop running the macro at that point if the name of the sheet is incorrect.
Loops
A large number of the tasks you can perform with a macro will be performed by loops.
Loops allow you to repeat the lines of code several times. The numbers of cycles are set at the entrance to the loop, whether by using a loop named Do While, or with a loop named For, in which you can set the number of times from the beginning.
Do While loop
Example: In the cells of Sheet 1, enter the list of sheets in the workbook.
For loop
In this loop, the names of the sheets in the workbook are also entered into the cells of the sheet. In a loop of this type, you use less lines of code. It is also easy to use.
It is comfortable to use the For loop. You can run the loop forwards or backwards. To have the loop run backwards, add Step-1 and reverse the from and to.
For Each loop
As opposed to the first two loops, this loop searches a range of cells and performs whatever is required even when you do not have previous information about the number of events in which data will be changed or formatted.
In cells that are not empty, color the fonts within the cells red and make them bold.
Planning Your Work Efficiently with Macros and Modules
Saving macros by subject in modules
If you save macros in various modules, you can maintain them and reduce the amount of examination required.
Do not use one module for a large number of macros and functions. Save a small number by subject. To jump from macro to macro, open the drop-down list on the right, above the module.
Your work in Excel includes a number of related workbooks
You increase the work for the users in your organization or another when data is retrieved from a number of workbooks. Concentrate all the macro commands in a single workbook, completely devoid of data. Maintaining macros and handling bugs is easier. If you discover a bug in one of the lines or you are required to make a change or an addition, your repair, change or addition will not lock out other users. You can easily change the workbook containing the macros without fear of corrupting the data.
It is recommended to save macros in xla type files.
Using xla Add-in files to store macros and functions
Excel uses a number of file types. One of them is the Add-in file, with an xla extension, as opposed to the xls extension used for regular Excel files.
Add-in files are hidden. They are different than the Personal.xls file. You can unhide Personal.xls with Window, Unhide. You cannot unhide an Add-in from the Excel user interface. You cannot select or activate worksheets in an Add-in file.
Why create Add-in files?
There are a number of important reasons to save xls type files as Add-in files.
© When Excel is started, the file opens and activates a macro, such as a macro to add an icon to the toolbars or to the Excel menus.
© You can use a password to save the macros and protect them from being viewed or changed.
© Customized functions can be stored in an Add-in file. You can use the Function wizard to activate the functions. Functions stored in an Add-in file are not prefixed with the name of the workbook.
© Saving macros in modules for performing complex tasks makes it easy to maintain them, update them, and add lines of code.
How do I create an Add-in file?
From the Excel menu, select File, Save As. In Save As type, select Microsoft Excel Add-in,and click Save
I created an Add-in file, and I want to convert it to a regular xls file
Select ThisWorkbook module.
Click the Properties icon.
Change IsAddIn to False.
See figure.
How do I hide the modules of an Add-in file and protect them?
1. In VBE, from Tools, select VBA Project, Properties.
2. Select the Protection tab.
3. Select the box Lock project for viewing.
4. Type a password in the first box, and then in the second one to confirm it.
5. Click OK.
6. Save the file.
How do I install an Add-in file in the
Add-in list?
The Add-in list is actually a list of shortcuts.
You add an Add-in file just like you add a shortcut, with the path of the file on the hard disk or the network.
1. From Tools, select Add-ins.
2. Click Browse.
3. In the Browse dialog box, locate and select the Add-in in the folder in which the file is saved.
4. Click OK.
5. Select the box next to the name of the Add-in.
6. Click OK.
Tips, Useful Commands and Answers to Commonly Asked Questions
How do I insert the SUM formula?
A seemingly simple problem is summing data. The problem is how to insert a SUM formula with a relative or absolute reference to sum numeric data in a data range.
Summing with an absolute reference
Define two variables and insert them into the first and last cell address in the cell range being summed. Use the & sign to concatenate the formula text with the cell addresses you saved as variables.
Alternatively, you can reduce saving the sum address to a single variable by using the word Address.
Summing with a relative reference
Insert the lines of code to delete the & sign from the above macro.
How do I prevent the Workbook_Open macro from running?
Press Shift while the workbook is open.
How do I freeze the window and sheets and keep them from jumping all over the place when the macro is running?
At the beginning of the macro, insert the line:
At the end, before End Sub, unfreeze the screen:
Using this technique will speed up your macros dramatically.
How do I prevent Excel alerts from being displayed?
At the beginning of the macro, insert the line:
How do I run a macro on a protected sheet?
In a protected sheet, changes are not allowed. This includes changing the formatting and/or pasting data. Assuming the sheet is not password-protected, run a command to clear the password:
To restore protection to the sheet:
You can run a macro on a protected sheet. Running the following command protects the cells of the sheet and their formatting from being changed by the user, but allows the macro to run without the need to remove the protection.
This setting is not saved with the workbook. You must add user interface only protection for each Excel session.
When inserting the macro into the module, I have a problem with lines of code being split up, so the macro does not work
Use an underscore to split long lines of code and create continuity. Be sure to type a space before the underscore.
How can I run a macro on a specific date or time?
To run a macro 5 minutes from now:
To run a macro at a preset time:
How do I prevent the Update Links message from being displayed?
At the end of the line of code that opens the Excel workbook, enter UpdateLinks:=0.
How do I paste a number format from an Excel sheet directly into the lines of code?
Type
From the Format Cells dialog box, choose Number, Custom. From the Type box, copy the format you created by using Ctrl+C, and press Ctrl+V between the quotation marks in the lines of code.
Result: