OzGrid Excel Newsletter
Contained at the bottom of eachnewsletter is Helpful Information on how to get the most from ournewsletters.
Should you wish to no longer receive ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere .
Youare more than welcome to pass this on to as many people as you wish.
What's New at OzGrid.com| New/Updated Pages | Microsoft Excel tips
Microsoft Excel VBA tips | Helpful Information
Check Out These Great Links:
OzGrid Excel Add-in Collection All the OzGrid add-ins together with Free file size reducer.
Over 100 Excel Add-ins and Software! It's one of the largest collections in the world.
Need Add-ins or Software for Word? Try AMF Software
Excel and Excel VBA Book store . Nothing but Excel!
Excel Time, Wage and Pay Workbook Template! Ideal for up 50 employees. Free trial download!
What's new at OzGrid.com [Top] Hi All,
Welcome to the Wonderful World of OzGrid for the month of October. As usual, lots has happened in the last month. One of the big areas we are moving into is Excel add-ins and software , not only from OzGrid, but other sites from around the world. We have put this altogether in categoriesright here . The page is quite large (over 100 links) so it may take a minute to Download.
Secondly, we have decided to maintain the special prices of our Training courses for the next couple of months. The reason for this is that they are going so well, that with the increase in people taking up our Training Packages, we are able to maintain these reduced prices for the moment. In addition to this, Excel VBA Level 1 and VBA UserForms now includes Time Saving Solutions which you can read about below:
Time Saving Microsoft Excel Solutions
Time Saving Microsoft Excel Solutions is a downloadable book in Windows help file format that contains hundreds of solutions to Microsoft Excel problems. You do not need to purchase and it does not expire - it is freeware. Download and use whenever you have the time or need. It is a great way to improve your Excel skills and find time saving Excel solutions.
No special software is needed to use - just download, run ,and open Excel. Access from the Excel Tools menu by clicking on the new menu item. Time savings Excel solutions are always just a click away - you even save time by avoiding fruitless Internet searches!
To order Online or Email training Go Here. All Online courses can then be accessed by going to ExcelTraining and clicking the Log In button. Email lessons will be sent within 48 hours.
Reduce Cost Training
We have also decided to give our clients more choice with regards to our Training courses, so we are offeringDownload Training in all levels. All these courses offer the exact same content, including Time Saving Solutions . The reason this method is at a reduced cost is that it does not include answers to questions.
Check Out These New/Updated Pages: [Top]
This month I thought we would look into one of Excel's very handy features, Subtotals. If you have never used this feature before, don't worry as we will Start from the beginning and they are very easy to use. After this I thought we would look at Custom Formats
The Subtotal feature in Excel can be found under Data on the main menu. Its purpose is to add rows and insert one of the Subtotal Functions (there are 11 of them) at each change of data in one column of a table. The number 1 rule which we cannot avoid is, our data MUST be sorted by the Columns we wish our Subtotals to apply.
Let's say we have a Table of data in the range A1:C500. This Table is used by a car lease company that leases cars to various clients. Each time a car is leased, details are entered into the table. In the interest of keeping things simple, lets assume, Column A contains the Car Makes, Column B the Client and Column C the Clients Lease Cost. We wish to add Subtotals to find out which Car Make is earning the most income via the Clients Lease Cost. Here is how we would do this.
The choices here are explained below:
At Each Change In: Select a label from the At each change in box to specify the column that contains the items or groups by which you want to subtotal values in other columns. If you want to subtotal grouped items, sort the list using this column as the primary sort column before you add subtotals.
Use Function: Select the summary function you want to use to subtotal your values. See Excel help for help and examples on each of these.
Add Subtotals To: Select one or more check boxes under Add subtotal to to specify the columns that contain values you want to subtotal. Subtotals for these columns will be based on differences in items in the column you selected in the At each change in box.
Replace Current Subtotals: Replaces all subtotals in the list with the new subtotals. Only applicable when we already have Subtotals applied.
Page Breaks Between Groups: Inserts page breaks automatically after each group of subtotalled data. Applicable to Printing only
Summary Below Data: Inserts the subtotal and grand total rows below the detail data.
Your Table will now have <CarMake> Total in a row below each change in car make, the column we sorted by. If you click in Column C on the same row as <CarMake> Total and look in the Formula bar, you will see a Function like: =SUBTOTAL(9,C2:C4) It is the number 9 that tells Excel to Sum as apposed to one of the other 10 Functions.
To the left of the table and the Row headings, will be what is know as an Outline. This allows us to view our table at 3 different levels.
You should also note that there are - & + signs at the bottom of each Subtotal group. These allow us to Expand/Collapse each individual Subtotal Group.
You cannot get much easier than that, can you? If you wish, you can add another Subtotal to the table and leave the first one in place. If you do this you must deselectReplace Current Subtotals. You can keep doing this as many times as you feel is needed.
The idea of Subtotals is not so much to put them on and leave them on, but rather to quickly put them on when needed, then remove. The flaw that I find with Subtotals is, while it nicely bolds the <CarMake> Total it does not bold the actual numeric Total. About the fastest way I know of do do this (other than a macro) is the select the column, push F5, click Special, then check Formulas, then OK, then Bold the selection.
Custom Formatsallow us to display the content of any cell in a format which may not exist as part of Excels built in Number Formats. One thing we must be aware of when formatting any cell, is the the format will not change its true underlying value. Lets see what I mean. In cells A1:A10 type the number 1:10 now in cell A11 place: =SUM(A1:A10). Now select only cells A1:A5 and go to Format>Cells-Number-Custom. Using any format as a Starting point type exactly "Number" (with quotation marks) in the Type: box and click OK. Cells A1:A5 will now appear to house the text "Number". In fact, they still have a true value the same as they did before we formatted them differently. If you re-enter =SUM(A1:A10) cell A10 will also take on the same Custom format, this is Excel taking an educated guess that we want our result cell formatted the same as the Custom Formatted cell. Change this back to the format needed.
Now we have that out the way, lets see how we can make use of Custom Formats. One of the biggest needs for a Custom Format is when we try to add cells containing Times and the sum of these hours adds up to greater than 24 Hours. If you have not encountered this before, enter the hours20:00:00 and 6:00:00 in cells C1 and C2. Now in CellC3 put =SUM(C1:C2) The result will not be 26:00:00 (unless using XP) but rather 2:00:00. This is because Excel stores time as fractions of 1 (1 being 24 hours) and when it reaches 24 hours (1) it rolls over to the next day and Start s again! To stop this from happening we use the Custom Format of [h]:mm:ss See many more tips and tricks onDates and Times here .
How about not showing Zero values in certain cells! Use a Custom format of: General;-General; You can, if you wish, simply replace General with the format you want, eg 0.00;-0.00; This is very handy when you only want certain cells not to display zeros as apposed to all, via Tools>Options-View-Zero Values
It is by following the same principle above that we design our own Custom Formats to display/Hide our data that meets a given criteria. The entire topic is rather lengthy, but Microsoft have an Excellent web page on this very topichere . Or if you prefer, you can download the Workbook example from this pagehere , but I highly recommend reading the web page first. The page is not well known so read and understanding it will put you 10 steps ahead of the office Excel guru!
Microsoft Excel VBA tips [Top]
This month, in the VBA section, I thought we would begin a three part series on how to use VBA in Excel efficiently. This will cover things like, yes you guessed it, those horribly slow loops! Better TRUE/FALSE evaluation and much more.
For those of you that have been getting my newsletter for some time, will no doubt be aware of my opinion on using Loops in Excel VBA. They are often totally unnecessary as Excel has many built in features that will operate many hundreds of times faster than a loop. Loops are generally best suited to loop through Object Collections, unless the Collection is Cells (16777216 of these!). My favoured method of skipping loops is the Find Method, which I have demonstrated many times in past issues . The Find Method should also be used in place of many of Excels Lookup Worksheet Functions when in VBA, as it is faster and far more flexible. You can look left, right, up or down with ease. You can make it case sensitive, look in entire cells, find the nth occurrence, look in formulas or values and even Cell Comments.
Another very fast alternate method to loops, is to use Excel's Worksheet Functions directly on the sheet in question. For example, suppose Column A on the Active Sheet has 25000 rows of imported text. The problem is the text has extra spaces, Tabs and other strange characters that need to be removed. This is where many would go for a loop and run the appropriate function on each cell, effective but very sloooooow! Instead try the method below.
Sub CleanIt()
Dim rRange As Range
'Set range to data cells only
Set rRange = Range("A1", Range("A65536").End(xlUp))
'Insert spare Column.
With rRange 'With Range Object
'Add a column for functions
.EntireColumn.Insert
'Insert the Clean and Trim Function as relative
.Offset(0, -1).FormulaR1C1 = "=CLEAN(TRIM(RC[1]))"
'Covert to values only
.Offset(0, -1) = .Offset(0, -1).Value
'Delete the original data
.EntireColumn.Delete
End With
End Sub
You should find this at least 10 times faster than any loop! The same principle can be applied to any cells housing data that needs modification. Sadly though, most coders will always use a loop. The ones that seem to do this the most are the coders that know and use VB, then move into Excel. They don't realize that when in Excel, we can skip a lot of very slow VBA code and use Excels built in features. It saves re-inventing the wheel, which in many cases ends up being a Wagon Wheel in the form of a loop :o) In the world of programming, VBA is one of the slower codes! This is normally due to 'not knowing any better'
Modules
Another often overlooked easy one, is to organize your modules logically. By this I mean place all related Procedures into the same Module. The reason for this is, VB code loads Procedures from Modules into memory, only when a Procedure in that Module is called. So placing related code in the same Modules prevents Excel loading several Modules in memory during execution of one Procedure that Runs orCalls other Procedures.
Constants
Make good use of*Constants . if you are going to be using the same text or number many times over, make it a Constant. Not only does this speed up code, it also means all modifications take place in one spot.
*A named item that retains a constant value throughout the execution of a program. A constant can be a string or numeric literal, another constant, or any combination that includes arithmetic or logical operators except Is and exponentiation. Each host application can define its own set of constants. Additional constants can be defined by the user with the Const statement. You can use constants anywhere in your code in place of actual values.
IIf
Avoid using the IIf Function
Syntax
IIf(expr, truepart, falsepart)
People often think this method is better because the statement goes on one line as apposed to an If with an Else Statement. The problem is that the IIf will always evaluates both the truepart and falsepart, even though it returns only one of them. Not the case with the If Function!
ME
When coding in Private Modules, eg UserForms, Sheet Events or Workbook Events (ThisWorkbook) use the Me Keyword. The Me always refers to the Object that the Private Module is for. So if you used Me in the Workbook Open event, it would refer to the Workbook housing the code.
Variables
Make good use of Variables and declare them correctly. If you use Variables at the Project Level, then place in some code to destroy them where appropriate to release memory. If you don't, Excel will bank up memory for these. Many times it is best to use Procedure or Module level variables and pass values ByVal rather than ByRef, see "Passing Arguments Efficiently" in the Excel VBE help for details on this.
With
Use the With Statement when working with Objects. As a rule of thumb, I use 'With' if I will be accessing more than 2 Properties or running more than 2 Methods on the one Object. You can note this in theCleanIt Procedure above.
Dots
Cut down on the Dots used to reference Properties or Methods of an Object whenever possible. This is because each "Dot" requires VB to make multiple calls. For example:
Range("A1").Offset(1,5).Value=100
Would be more efficient written as:
Range("A1")(2, 6) = 100
Note that when using this method 1,1 would refer to the same cell, so we must add 1 to both the Row an Column. Note also, that I often do not use this method in examples, else I get flooded with emails questions :o)
Well that should be enough for most of you VBA coders out there to digest. Watch out for next months issue where we will cover part 2 of how to use VBA in Excel efficiently
- until next month - keep Excelling!
Kind regards
Dave Hawley
Youare more than welcome to pass this on to as many people as you wish.
SPECIAL! Get all the OzGrid Add-ins , together and Save! 
$54.95
Readthis issue and past issues online here: http://www.ozgrid.com/News/Archive.htm
Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation
