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.

SPECIAL! Get all the OzGrid Add-ins, together and Save! $54.95

INDEX

What's New at OzGrid.com| New/Updated Pages | Microsoft Excel tips

Microsoft Excel VBA tips | Helpful Information | 50% Off Special!

Check Out These Great Links:

• Global Software SearchSearch a database of thousands!
• OzGrid Excel Add-in Collection All the OzGrid add-ins together with Free file size reducer.
• Over 150 Excel Add-ins and Software! Possibly the largest collections in the world and getting bigger each week!
• Excel Duplication Manager. New feature added! with Free file size reducer.
• OzGrid Excel Plus Add-in. Eight Add-ins in one! with Free file size reducer.
   

What's new at OzGrid.com [Top]

Hi All,

Well another year has almost passed, how time flies when you are having fun! This will be the last newsletter from OzGrid for the year 2002, so we would like to take the opportunity to say Merry Christmas and a Happy New Year!

There have been some big changes happening with OzGrid over the last few months and one of the biggest is our selling of third party Software and Excel Add-ins. We now have a selection of over 200 (and growing almost daily) of business related software and a HUGE collection of Excel Add-ins. All these can be found via this page:   Business Software & Excel Add-ins You should also find that most of these products now have screen shots and a Money Back Guarantee, so your purchase is risk free. All purchases are via Secure Sites that use SSL technology.

You may re-call last month we also told you about our Global Software Search that searches thewww.Regnow.com database of thousands for software. Well, we have now placed another Large List page on here so you can browse a list of software fromwww.sellshareware.com. While this is not as large as the Regnow database, it does contain some very popular software indeed. All software is sold with no mark-up what-so-ever, OzGrid simply gets a small commission for most sales. It is via this, and ourBusiness Software & Excel Add-ins that enables us to offer all our Newsletter readers the new 50% Perpetual Special each month. As time goes buy, and our buying power increases, you can sure OzGrid will be trying to squeeze out even more than a 50% discount!

50% Off Perpetual Special...... [Top]

This months half price special is from John Drummond of Business Functions. If you haven't already, you can go to John's site and read about these fantastic functions. Don't confuse these Excel functions with Custom Functions (User Defined Functions) as these are the real thing (just like Excel's built in functions) and are written in C++. What this means to you is, they will NOT slow down Excel at all, like Custom Functions written in VBA can do.

There is just simply to much to detail here (over 350 speciality Functions). There are functions that will suit almost any profession. So visit John's site and read all about them.

How To Get Them For Half Price!

If you wish to take advantage of this once only offer, send an email to [email protected] telling us whether you want the Standard or Professional package and we will send you an invoice with payment instructions. Upon receipt of payment, you will be sent the 'full use activation code' for the time limited trial version. Please. As with any OzGrid "50% off perpetual Special" the sale ends on the 20th of the same month the newsletter is sent, i.e. December 20 2002 or after 200 purchases, whichever comes first.

Check Out These New/Updated Pages: [Top]

Free Excel Downloads |Global Software Search |Excel Productivity Add-ins |Charting Tools and Software

Trading Tools | Analysis, Forecasting and Estimating |Financial Software and Add-ins

Coming Soon. Enhanced Excel Solvers and VBA Encryption Software.

Hard drive maintenance from Ask The Computer Lady

Keeping your hard drive in good condition is an important part of keeping your computer running smoothly. There are several things you can do for your hard drive, the first two are checking the disk for errors and defragmenting.

1. Click on "Start" and then on "My Computer".
2. In the "My Computer" window, right click on your hard drive and click on "Properties", then on the "Tools" tab.
3. Next to "Error Checking" click the button that says "Check Now", check off the options you want and click "Start". This will scan the physical surface of your drive for places that can't hold data and mark them as
   bad. If it finds bad sectors, it will attempt to rescue the data out of that section and save it to a good sector.
4. When you have finished the error checking, Click the "Defragment Now" button in the next section. This will restore files that have been fragmented into smaller sections into whole files again.

Do this once every month or so, and you will keep things running smoothly.

Written by Elizabeth Boston

Sign up today for her free newsletter, Ask The Computer Lady, by sending
an e-mail to [email protected]
http://askTCL.com

Training Specials!

Microsoft Excel tips[Top]

This month for our Excel tips we look at part 2 of 2 on "Nesting Functions". If you missed last months issue, proceed here tocatch part 1

Ok, last month we looked at just what Nesting Functions means (using the result of one function as the argument for another) and worked through some basic examples. One of the hardest parts of nesting functions is knowing where to place the argument separators (often a comma) and the parenthesis (brackets). Fortunately we can have Excel do all this for us by using the Function Wizard or Paste Function.

Let's assume we have times being imported into Excel and we need to convert these to decimal times. The BIG problem is, they are imported like: 12h45, 1h15 10h, 8h30, 30m etc Not very Excel friendly like this! The function we will use to convert these to decimal time, i.e 12.75 , 1.15, 10, 8.5, 0.5 etc is this monster:

=IF(H8="","",IF(H8="24h",24,IF(ISERROR(FIND("m",H8)),TIMEVALUE(LEFT(H8,FIND("h",H8)-1)&":"&SUBSTITUTE(SUBSTITUTE(H8,LEFT(H8,FIND("h",H8)),""),"h",""))*24,SUBSTITUTE(H8,"m","")*1/60)))

For those of you that have not left the room screaming :o) here it is written in English.

1. If Cell H8 equals "" (empty, or empty text) return "" (appear blank).
2. If Cell H8 equals "24h" then return the number 24.
3. If the Find Function (looking for the letter "m" in cell H8) returns an error (there is no "m" in cell H8) then  the use the TIMEVALUE Function (converts a text time to an Excel Serial number) by joining together the first 1 or 2 characters (determined by FIND("h",H8)-1)with ":" (time separator) and cell H8 with the "h" removed (via the first SUBSTITUTE function replacing "h" with "") and the first 1 or 2 characters (determined also by FIND("h",H8)-1)  being replaced also with "" and muliplying the result by 24. Multiplying by 24 serves converts the Time Serial value (returned by TIMEVALUE) to a whole number for our decimal time hour portion. See Excel Date and Times for the logic behind this.
4. If Cell H8 does contain the letter "m" simply replace the letter "m" with "" and multiply by 1 (this converts a text number to a true number) and divide by 60. Again, see Excel Date and Times for the logic behind this.

Now isn't that easy (yeah right!). Let's use the life saving Paste Function to write this. While this will seem very long winded, it will show you many ways to use the Paste Function so that Excel places in all parenthesis and argument separators. The point of this exercise is only to get you familiar with using the Paste Function to nest functions.

1. In Cell A1 put12h45 and in Cell A2 put 10h and in A3 put 30m
2. Now select cellB1 and go to Insert>Function (or push Shift+F3).
3. "Logical" from the Categories then IF from the available functions and click Ok.
4. In Logical_test type: A1=""
5. In Value_if_true type: ""
6. Click into theValue_if_false box and now click on small drop arrow (Formula picker) to the left of the Formula bar (the Name Box is normally here) and select "More Functions" then again select the IF function.
7. In the Logical_test type: A1="24h"
8. In the In Value_if_true type: 24
9. Click into theValue_if_false box and now click on Formula picker and select "More Functions" then again select the IF function. If you see the IF function as part of the list, simply click it without going to "More Functions" You can do this for any function if it is part of the list.
10. Click in the Logical_test and then again click on Formula picker and select "More Functions" then select Information from the Categories then ISERROR  from the available functions.
11. Click in the Value box and then again click on Formula picker and select "More Functions" then select Text from the Categories then FIND from the available functions.
12. In Find_text type: "m" then click in the Within_Text box and type A1
13. Now, in the Formula bar click on the third IF Function. This will take us back to the IF function dialog.
14. Click in the Value_if_true box and again click on Formula picker and select "More Functions" then select Date & Time from the Categories then TIMEVALUE  from the available functions.
15. Click into Text_time and again click on Formula picker and select "More Functions" then select Text from the Categories then LEFT  from the available functions.
16. Click in Text and type A1. Now for some cheating:o) from the Formula bar highlight: FIND("m",A1) and Copy (Ctrl+C). Now still in the formula bar click on LEFT then click into Num_chars and then paste (Ctrl+P) and type -1 at the very end. Your  Num_chars should read: FIND("h",A1)-1
17. Now click back into the Formula bar, immediantley after )-1 and type  &":"&
18. Again click onFormula picker and select "More Functions" then select Text from the Categories then SUBSTITUTE  from the available functions.
19. Click in the Text box and repeat step 18. In the Text box for this second SUBSTITUTE Function type: A1, then (more cheating) copy LEFT(A1,FIND("h",A1)-1) From the Formula bar and then click back on the second SUBSTITUTE Function paste into the Old_text box. Add one more closing bracket, so it looks like: LEFT(A1,FIND("h",A1))
20. In New_text type: ""
21. In the Formula bar click on the firstSUBSTITUTE function then in Old_text type "h" and in New_text type "" Hang in there guys, we are nearly done!
22. In the formula bar, immediately after"h","")) type *24
23. Now again from the Formula bar, go all the way back and click on the third IF
24. In the Value_if_false put: SUBSTITUTE(A1,"m","")*1/60 You should be able to copy most of this from existing parts.
25. Click OK and you should have your deeply nested function.

As you become more comfortable using the Paste Function, you will find you will simply copy and modify many different parts of your formula. If you now copy this formula down to cell B3, you should get the results: 12.75, 10 and 0.5 respectively.

There is know escaping the fact that, the first time you write this it will take a bit of time. But once written it can simply be copied to wherever needed. You will also find that 'nutting' out these sort of problems will only take 5 minutes or so once you are confident.

If you can master using the Paste Function in this way, and you are keen, you will soon be able to write mega formulae in Excel.

 Super Excel Special! Only $7.50Click here

Would like over1200 VBA examples?

Microsoft Excel VBA tips [Top]

This month we will look at part 3 of 3 on How to use VBA in Excel Efficiently.  For those of you who may have missed part one and two of this, you can read them online (as you can for any past issues) in our newsletter archives section. They are  issues 18 and 19.

You may recall last month we spoke of using the Select Case Statement in such a way that the most likely case is the first one and the least likely the last. While you may think "not worth the effort" remember this can speed up the execution of the Select Case by 600%. The Select Case is a very handy and a much better alternative to lot's of IF statements, but if you are dealing with low numbers (say 1 to 50) you can make you code even faster and easier to read and modify. How? We use the Choose Function. If you are not familiar with the Choose Function, read the text below taken from the Excel help.

Choose Function

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Remarks

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and choice-3 = "three", Choose returns "three". This capability is particularly useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one. For this reason, you should watch for undesirable side effects. For example, if you use the MsgBox function as part of an expression in all the choices, a message box will be displayed for each choice as it is evaluated, even though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number before being evaluated.

Now, armed with information we could use the Choose in many ways, the only limit is our own lateral thinking. Let's assume we need to Loop 10 times and at each loop we need to insert a value into a specified cell. While all these cells are in the same column, the rows will differ each time and they will not follow any logical order. Most would use a Select Case Statement like below:

Sub DoTheLoop()
Dim i As Integer

    For i = 1 To 10
        Select Case i
            Case 1
                Range("D6") = i
            Case 2
                Range("D5") = i
            Case 3
                Range("D15") = i
            Case 4
                Range("D10") = i
            Case 5
                Range("D16") = i
            Case 6
                Range("D21") = i
            Case 7
                Range("D1") = i
            Case 8
                Range("D17") = i
            Case 9
                Range("D13") = i
            Case 10
                Range("D2") = i
        End Select
    Next i
End Sub

This will certainly do the job and is a much better method than using IF statements, but the Choose is even better. Try this.

Sub DoTheLoopBetter()
Dim i As Integer
Dim iRow As Integer

    For i = 1 To 10
        iRow = Choose(i, 6, 5, 15, 10, 16, 21, 1, 17, 13, 2)
        Cells(iRow, 4) = i
    Next i
End Sub

Why is it better? Simply because there is no superfluous evaluations needed like with the Select Case. If you run the Select Case Statement in Debug mode (place mouse insertion point anywhere in the code an keep pushing F8) you will see that the code, at each loop, checks all cases until one is True. This means by the 10th loop it checks 9 Cases before it finds a match.

The Choose Function is also more compact, however never confuse shorter compact code with efficient. In fact, the longer code can often be the most efficient.

For those of you that have been subscribed for some time, you will be well aware of my dislike for loops. Many people take this as meaning I never use them. This is certainly not true, I use them frequently. However, I favour greatly the For Each Loop (which requires looping through a Collection one Object at a time) over most other loops. My reasons, well firstly For Each Loops are faster than the other loops and secondly, most Collections do not have very many Objects within them. The BIG exception to this is a For Each Loop through a Range or Cells Collection! Just remember that Excel has 16777216 of these per sheet. Also, when working with cells, Excel has a very rich environment of built in features that will so often do the same task thousands of times faster. I really cannot stress this enough, always consider these Methods , either in place of the loop, or to greatly narrow down the number of loops.

1. Find Method
2. SpecialCells Method
3. AutoFilter Method
4. AdvancedFilter Method

There are more, but these alone can be of huge use, once you learn to 'step outside the box' that is :o)

Last month we looked at how switching off Calculations during code execution can speed up our code, a lot! You may also recall we passed the Calculation State to a Variable, before switching to manual. The code was.

Sub PutBackToNormal()
    Dim xlCalc As XlCalculation

        XlCalc = Application.Calculation
           Application.Calculation = xlCalculationManual

    '<Your Code>

           Application.Calculation = xlCalc

End Sub

What I would like you to take note of is the Dim xlCalc As XlCalculation The same principle can be used to eliminate lots of If Statements and/or Select Case Statements. The less evaluation your code needs to do the better! For example, suppose you needed some code that creates x number of Line shapes (from the Drawing Toolbar) and the Arrow head style must be the same as an existing Line Shape. You could of course use multiple If Else Statements to determine the existing Line Shape arrow head, but there is no need.

Dim xlArrHead As XlArrowHeadStyle

xlArrHead = Sheet1.Shapes("Line 2").Line.BeginArrowheadStyle

will do this for us simply by dimensioning xlArrHead As XlArrowHeadStyle. It is this principle that can save using many If Statements etc in out code.

That's all for this month. Once again a very merry Christmas and a safe an happy New Year from OzGrid.

 Super Excel Special! Only $7.50Click here

 - until next month - keep Excelling!

Kind regards

Dave Hawley

[email protected]

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

• OzGrid Excel Plus
• Formula Ref Changer
• Sheet Index Creator
• Duplication Manager

Helpful Information [Top]

Readthis issue and past issues online here: http://www.ozgrid.com/News/Archive.htm

Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation