The main use for VBA is to automate common tasks in Office programs. In this exercise we will create a 'Favorite Date Format' button to go onto the Toolbar in Excel, the procedure being the same in all MS Office programs.
Notice that we use the Macro Recorder to create the macro. I cannot emphasise that this is the EASIEST way to create VBA code or to initiate a VBA project!
|
STEP ONE: Preparation
Ensure that the "Personal.xls" is unhidden by using the 'Window | Unhide' menu command.
STEP TWO: Start the Macro Recorder
- Use the 'Tools | Macro | Record New Macro' menu item to open the 'Record Macro' dialog box.
- Set the macro name as "favdateFormat", store the macro in the "Personal Macro Workbook", and if you wish, assign a keyboard shortcut. Click 'OK' to close.
You will see a depressed recorder button indicated by a small dark square either on the toolbar or as a floating dialog, depending on your Excel setup.
STEP THREE: Record the Macro
- Use the 'Format | Cells' menu item to open the 'Format Cells' dialog box.
- In the 'Number' tab, select "Date" in the 'Category' list box, then select your favorite date format from the selection shown. Click 'OK' to close.
Click the stop button on the Macro Recorder controls dialog. The small dark square should change to a small dark filled circle. If you looked at the code created for the 'favdateFormat' macro in the VB Editor, it should look something like ...
Sub favdateFormat()
'some comments here
Selection.NumberFormat = "dd-mm-yy"
End Sub
STEP FOUR: Assign a new button to a toolbar
- Use the 'View | Toolbars | Customize' menu item to open the 'Customize' dialog.
- Select the 'Macros' item from the 'Categories' tab, then select the "Custom Button" item from the 'Commands' tab and drag and drop it to where you want it placed on a toolbar.
- Now, while the 'Customize' dialog is still open, right click on the new button, and select the last menu item, 'Assign Macro'. In the 'Assign Macro' dialog, select the "favdateFormat" macro from the list of macro names, and after ensuring that the "All Open Workbook" item is selected in the 'Macros in' listbox, click on the 'OK' button to exit.
- Finally, right click on the new button again and select the 'Change Button Image' menu item. Select the hour glass Icon from the displayed Icons.
- Close the 'Customize' dialog box.
FINISHED! Now test the button to see if it works Ok!
EXERCISE for HOMEWORK
Create another button to go onto the Toolbar that automates one of your common tasks, such as a series of formating with changed font type, size, color and alignment.
|