OzGrid Excel Add-in Collection. All the OzGrid Add-ins bundled together SPECIAL!
OzGrid's Excel Newsletter
You are more than welcome to pass on this newsletter to as many people as you wish.
To no longer receive our newsletter, send an email with the words "action: Unsubscribe" in the body of the email, or click here.
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here:
OzGrid Excel Add-in Collection. All the OzGrid Add-ins bundled together SPECIAL!What's new - Half Price Special - Excel Section - Excel VBA Section - Helpful Info
Hi all,
Well, March was certainly a month full of happenings. We have just had a small family break to celebrate Dave's 40th birthday, we certainly needed it as our lives are pretty hectic!!
Our Excel Question and Answer Forum is ever-increasing and we are gaining new members every day. If you haven't been there already, do so and check the Q/A board, or just shoot the breeze if you prefer. Registration costs nothing and only requires a Username, Password and email address. You can also get a big discount on our Add-ins when you register. A huge number of our newsletter readers have already joined the Forum, and we are hoping that soon everyone will use this very rich source of free help and information.
Software Sales
Software sales in March were steady, we only noticed a slight dip the week the war started. Many other software developers that we are in contact with have experienced significant decreases since the onset of war, so we are glad to be relatively unaffected. We also hope, of course, that things improve for them quickly. We also very much wish total success and peace for the Iraqi civilians, and the US, UK, and Australian troops.
As most of our buyers are looking for an Excel Add-in, we have made a page where you can easily browse only the Excel Add-ins by name. To see this pageClick Here
New Prices
Just a reminder of the new pricing structure of our own OzGrid Add-Ins. Discounts will be offered for more than one licence of a particular product. The discounts are:
2 - 4 Licences - 20% discount
5 - 9 Licences - 30% discount
10+ Licences - 50% discount
So - enjoy the month of April - and keep Excelling!!!!!
27 Excel Add-ins, 3 Downloadable Books on VBA, a 75% Saving The Analysis Add-ins Collection
Super Special! Buy theAnalysis Collection and get the entireOzGrid Add-ins Collection for Free! Just send your purchase confirmation to[email protected]Offer Ends 20 April 2003
This months half price special is for ourSmart Tools range which includes: ExcelSmartTools, Excel Smart Tools Auditor and ForecastingTools. Read about, and/or download these great Excel Add-ins here.
Remember the special only lasts 10 days, from the 10th April 2003 and will end on the 20th April 2003. To take up this 10 day offer, send an email to[email protected] before April 20th 2003 and we will send you an invoice and then the Add-ins upon receipt of payment. You can pay online via our secure site, or via the PayPal secure site. Our PayPal email account is [email protected], be sure to include the exact name of the product.
Super Special! Buy theAnalysis Collection and get the entireOzGrid Add-ins Collection for Free! Just send your purchase confirmation to[email protected] Offer ends 20 April 2003
This month we will take a detailed look at Custom Number Formats. Don't confuse the term "Number" as meaning custom formats only applying to numeric data as it can also apply to text.
It is very important to understand how cell formats are seen by Excel. Excel see a cells format as having four Sections. These are, from left to right Positive numbers, Negative Numbers, Zero Values and Text values. Each of these Sections are separated by a semi colon (;). If you create a custom number format you do not have to specify all four sections. By this I mean, if you included only two sections, the first section would be used for both positive numbers and zero values, while the second section would be used for negative numbers. If you only used one section, all number types would use that one format. Text is only affected by custom formats when we use all four sections, the text would use the last section.
It is also very important to understand that the formatting of a cells value does not effect its underlying true value. To show this we can type any number into cell A1, then go to Format>Cells-Number-Custom and, using any format as a starting point, type in "Hello", with the quotation marks, now click OK. Now, while the cell displays the word Hello, it's true value can be seen by selecting the cell and looking in the Formula bar, or by pressing F2. If you were to reference this cell in a formula, e.g. =A1+20 the result cell would also take on the custom format. If we were to reference cell A1 and many other cells that have any standard Excel format, e.g. =SUM(A1:A10) our result cell would still take on the custom format of cell A1. This is an educated guess by Excel that you want the result cell formatted the same as the referenced cell(s). If the referenced cells contain more than one type of format, any custom format will take precedence. So, you must always remember that Excel uses a cells true value and not it's displayed value. This can catch out the unwary if you are calculating cells that are formatted for no, or few, decimal places. For example, enter 1.4 in A1 and 1.4 in A2, format both these cells to show zero decimal places and then place=A1+A2 and the result is 3. Excel does have an option called Precision as displayed, found under Tools>Options-Calculation, but you should be aware that this option will permanently change stored values in cells from full precision (15 digits) to whatever format, including decimal places, is displayed. In other words, once it's been checked and given the OK, there is no turning back.
The default format for any cell is "General". If we enter a number into a cell, Excel will often guess the number format that is most appropriate. For example, if you type in 10%, Excel will format the cell as a percentage. Most often Excel gets this correct, but sometimes we need to change this. One point I will make on this is, when using Format>Cells, resist the temptation of forcing a left, right or centre horizontal format! By default numbers are right aligned and text left aligned. If you leave this alone, you can tell at a glance whether a cell is text or numeric, as in the case of my earlier example where cell A1 appears to hold text, when in fact, it holds a number.
Ok, getting back to the all important Sections that a formatted cell contains. Within these Sections we are able to use Formatting Codes. It is these codes that force Excel to make our data appear how we would like. Let's use a simple Example. Suppose you would like any negative number to appear inside parenthesis, and all numbers, positive, negative or zero, to show two decimal places. The Custom Format we could use is: 0.00_ ;(-0.00). If you also wanted negatives to be red, use: 0.00_ ;[Red](-0.00) Note the use of the square brackets in the Section for negative numbers. This is the Formatting Code that tells Excel to make the number red.
There are many different Formatting Codes that can be used within Sections of a Custom Format. The tables below show these. The Table is from Microsoft�
| Number Code | Description |
| General | General number format. |
| 0 (zero) | Digit placeholder. This code pads the value with zeros to fill the format. |
| # | Digit placeholder. This code does not display extra zeros. |
| ? | Digit placeholder. This code leaves a space for insignificant zeros but does not display them. |
| . (period) | Decimal number. |
| % | Percentage. Microsoft Excel multiplies by 100 and adds the % character. |
| , (comma) | Thousands separator. A comma followed by a placeholder scales the number by a thousand. |
| E+ E- e+ e- | Scientific notation. |
| Text Code | Description |
| $ - + / ( ) : space | These characters are displayed in the number. To display any other character, enclose the character in quotation marks or precede it with a backslash. |
| \character | This code displays the character you specify. Note Typing !, ^, &, ', ~, {, }, =, <, or > automatically places a backslash in front of the character. |
| "text" | This code displays text. |
| * | This code repeats the next character in the format to fill the column width. Note Only one asterisk per section of a format is allowed. |
| _ (underscore) | This code skips the width of the next character. This code is commonly used as "_)" (without the quotation marks) to leave space for a closing parenthesis in a positive number format when the negative number format includes parentheses. This allows the values to line up at the decimal point. |
| @ | Text placeholder. |
| Date Code | Description |
| m | Month as a number without leading zeros (1-12) |
| mm | Month as a number with leading zeros (01-12) |
| mmm | Month as an abbreviation (Jan - Dec) |
| mmmm | Unabbreviated Month (January - December) |
| d | Day without leading zeros (1-31) |
| dd | Day with leading zeros (01-31) |
| ddd | Week day as an abbreviation (Sun - Sat) |
| dddd | Unabbreviated week day (Sunday - Saturday) |
| yy | Year as a two-digit number (for example, 96) |
| yyyy | Year as a four-digit number (for example, 1996) |
| Time Code | Description |
| h | Hours as a number without leading zeros (0-23) |
| hh | Hours as a number with leading zeros (00-23) |
| m | Minutes as a number without leading zeros (0-59) |
| mm | Minutes as a number with leading zeros (00-59) |
| s | Seconds as a number without leading zeros (0-59) |
| ss | Seconds as a number with leading zeros (00-59) |
| AM/PM am/pm | Time based on the twelve-hour clock |
| Miscellaneous Code | Description |
| [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n] | These codes display the characters in the specified colors. Note n is a value from 1 to 56 and refers to the nth color in the color palette. |
| [Condition value] | Condition may be <, >, =, >=, <=, <> and value may be any number. Note A number format may contain up to two conditions. |
If you do a lot of Custom Formatting you might find it useful to print these tables.
Lets look at the last Formatting Codes, the Comparison Operators. Assume we want our custom number format: 0.00_ ;[Red](-0.00) to only display negative numbers as red font in bracketsIF the number is less than -100. For this we could use: 0.00_ ;[Red][<-100](-0.00);0.00 It is the Formatting Codes: [Red][<-100](-0.00) placed in the Section for negative numbers that make this possible.
One format that is often asked for is to display dollar values as words. For this we can use the Custom Format of: 0 "Dollars and" .00 "Cents". This format will force a number entered as 55.25 will be displayed as 55 Dollars and .25 Cents. If you wish to actually convert numbers to dollars and cents, see these two Custom Functions from Microsoft.
Let's look at one more Custom Format where we wish to display the words Low, Average or High along with the number entered. For this we could use: [<11]"Low"* 0;[>20]"High"* 0;"Average"* 0 Note the use of the Formatting Code * This code repeats the next character in the format to fill the column width Meaning all our Low, Average or High text will be forced to the right, while the number will be forced to the left.
Microsoft� have a helpful Workbook you can Download that shows many of the abilities of Custom Formats.
This month we will take a look at some of the Excel Workbook Events, e.g. Workbook_Open, Workbook_BeforeClose etc. Prior to Excel 97 about the only event we could use was the open and close event by naming a standard Procedure Auto_Open or Auto_Close. From Excel 97 onwards Microsoft introduced Events for both the Workbook and Sheet. Both the Workbook and the Sheet are Objects and as such have Events. They also have their ownPrivate Modules where we can place code within one of the many Events available. The Private Module for the Workbook Object is called "ThisWorkbook", while the Private Module for a Sheet is called Sheet1, Sheet2 etc.
Workbook_Open
The Workbook_Open Event is probably the most frequently used Event for the Workbook Object. It is the first event that is fired whenever a Workbook is opened. The Event is fired no matter which method is used to open the Workbook. The exception to this is:
The reasons for having code run automatically whenever a Workbook is opened can (and does) vary far and wide. However, the method used is the same in all cases, that is, the code MUST be within the Private Module of the Workbook Object (ThisWorkbook). The quickest way to get to Excels Workbook Events is to right click on the sheet picture (top left next to "File") and select "View Code". Then choose an event from the "Procedure" drop down list box. For Excel 2000+ you will need to select "Workbook" from the "Object" drop down list box first.
By default, the Workbook Event that Excel will present for you is the Workbook_Open.
Private Sub Workbook_Open()
End Sub
It is within here, we either place the code we want to run, or place a Run or Call (see Excel help) Statement. The code below will show the user a message box displaying today's date each time the Workbook is opened.
Private Sub Workbook_Open()
MsgBox "Hello, today's date is " & Format(Date, "ddd d mmm yyyy")
End Sub
Workbook_BeforeClose (Cancel As Boolean)
As the name suggests any code within the Workbook_BeforeClose will run immediately before the Workbook is closed. You should also note the Cancel As Boolean inside parenthesis. This is an argument that returns or accepts (read/write) a value of True or False. The value that is returned (read) is totally reliant on what the user selects at the "Do you wish to save changes" prompt. If they click Yes or No, the Cancel returns False, if the user clicks Cancel (do not close) it will return True. Likewise, if we set this (write) to True within our code, the Workbook will not close.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iReply As Integer
iReply = MsgBox("Have you entered today's data.", vbQuestion + vbYesNo)
Cancel = (iReply = vbNo)
End Sub
This code will display a message as soon as the user closes the Workbook. They will first see our message and then, if they click Yes the expression (iReply = vbNo) will return the Boolean False to the Cancel argument. The Workbook will then continue to close as normal. If the user clicks No, the Boolean True will be returned to the Cancel argument and the Workbook will not close. Note the absence of any superfluous IF Statement to Cancel the closing.
Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
This Event is fired immediately before the Workbook is saved. It is the action of saving that fires the event, but the workbook will not save until any code within it has run. Like the Workbook_BeforeClose event it takes a Cancel argument. It also however takes another argument, SaveAsUI, also a Boolean. The word SaveAsUI stands for Save As User Interface and returns True if the Save as dialog box is displayed. One common reason to read this argument is to prevent a user from saving a Workbook as another name and/or location. You should note that the argument is passed ByVal (by value). *This is one method of passing the value of an argument to a procedure. This allows the procedure to access a copy of the variable. As a result, the variable's actual value can't be changed by the procedure to which it is passed.
*From Excels help.
This is very important in the case of the BeforeSave event as it means we cannot pass the value True or False to this argument and hence Show or Cancel the Save As User Interface. If we did want to prevent the Save As User Interface from being displayed, we could use some code as shown below.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim iReply As Integer
If SaveAsUI = True Then
iReply = MsgBox("Sorry, you are not allowed to save this Workbook as another name. " _
& "Do you wish to save this workbook.", vbQuestion + vbOKCancel)
Cancel = (iReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub
In this case we read the Boolean value of SaveAsUI and if it's True we display our message box. We then ask the user if they would like to save the Workbook, as they cannot use Save as. If they click Cancel on the message box, the Boolean True is passed to theCancel argument via the expression(iReply = vbCancel) and this stops the Save as and Save from taking place. If they clickOK, the Save as and Save is not cancelled (yet).The code then hits the line If Cancel = False Then Me.Save and saves the Workbook, if the user chooses to click OK on our message box. It then reachesCancel=True which Cancels the Save as and Save operation.
Note the use of the Keyword Me in the code. When we use Me in the Private Module of the Workbook Object, it refers to the Workbook Object. Very much the same as ThisWorkbook.
Next month we will look into more Events. Until then, keep Excelling!
30% OffAdvertized Price! email [email protected] to get the discount.
You are more than welcome to pass on this newsletter to as many people as you wish.
To no longer receive our newsletter, send an email with the words "action: Unsubscribe" in the body of the email, or click here.
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here:
