OzGrid's Free Excel Newsletter

Microsoft Excel and VBA for Excel Training Information (auto response)

The newsletter is divided into four sections:

1.    What's new at OzGrid.com

2.    Microsoft Excel tips

3.    Microsoft Excel VBA tips

4.    Helpful information

It is published on about the 10th of each month and always written by myself Dave Hawley.

You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in it's entirety

Should you wish to no longer receive our newsletter, send an email with the words "Leave Newsletter" as the subject field, or click here.

Contained at the bottom of each newsletter is Helpful Information on how to get the most from our newsletters. If you think there is something missing, please let me know.

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation

10% off all Add-ins here! http://www.mbrm.com/ just tell them that you are a subscriber to the OzGrid newsletter and you will get 10% off!


What's new at OzGrid.com

Well as this is the last Newsletter for the year 2001 and Christmas will soon be upon us, I wishto take the opportunity to wish you and your families a Merry Christmas andHappy New Year!

We have finally now got full control over both our Domains (www.MicrosoftExcelTraining.com and www.OzGrid.com) either one should now take you to our Web site! I have also set up some automatic redirect pages for the individual pages on www.MicrosoftExcelTraining.com/ However, should you come across one somewhere out there on the WWW, please let me know and I'll set one up for it.

A big thanks to all of those that sent an email with answers to my short list of questions last week. From the responses I obtained it looks like www.Google.com is the preferred search engine for OzGrid newsletter subscribers. To be honest, it's also my preferred search engine! For those of you that don't use Google, I can highly recommend it for finding what you want.

Last months offer of 20% off any training was certainly a big hit with all of those that are looking for some quality Excel training. For all of those that have paid, but wish to commence early next year I look forward to sharing my knowledge with you. Just remember I am only too happy to answer all your Excel questions!


Microsoft Excel tips

A couple of handy uses for the INDIRECT function

For this months tips, I thought we would look at how the INDIRECT function can be used in "out of the box" ways. Let's assume you have a simple SUM function being used to sum a range of numbers (A1:A100). It works just fine until a row is inserted or deleted! If you delete a row it ends up reading: =SUM($A$1:$A$99) or if you insert a row it reads: =SUM($A$1:$A$101). This is no problem of course if this what you want and expect, but it would seem (from the amount questions) it's not what is wanted! There is a very simple way this can be overcome and that is with the aid of the INDIRECT function: =SUM(INDIRECT("$A$1:$A$100")). This way Excel will read the same range no matter how many rows are added or deleted. For those who are not familiar with the INDIRECT function it allows Excel to see a text string as a range address.

Another common problem among Excel users is not being able to use the List option of the Validation feature to refer to another Worksheet. To see what I mean try this:

  1. Type the numbers 1 to 10 in A1:A10
  2. On any other Worksheet select any cell and go to Data>Validation.
  3. Select "List" from the Allow: box
  4. Click the collapse dialog button to the right of the "source:" box
  5. Click the Worksheet tab of the sheet containing your numeric list (ie. A1:A10)

Cannot be done can it! Even if you type =Sheet1!A1:A10 and then click OK, Excel tells you that you cannot refer to another Worksheet. That's what Excel thinks, but this can be overcome.

Instead of typing =Sheet1!A1:A10  type: =INDIRECT("Sheet1!A1:A10") and Excel quite happily accepts it.

Not familiar with what can be done with Validation? try here: http://www.ozgrid.com/Excel/Formulas.htm

Stop users scrolling about

Another common questions I have been receiving lately is how can I stop the average Excel user from scrolling about my worksheet, without using VBA. I say average Excel user as this method (unless combined with sheet protection) can be overcome! All you need to do is hide all unused rows and columns. The easiest way to do this is to select the entire row below you last row of data, then holding down your Ctrl and Shift key push the Down Arrow. This will select all your unused rows, now right click and select Hide. Do the same for you Columns expect obviously switch entire row, last row and Down Arrow with entire column, last column and Right Arrow.

Once you have done the above you may need to place or change some data in a cell that is not hidden. The quickest way to do this is either type the address in the Name Box (left of the formula bar) and push Enter, or push F5 and type the cell address in the Reference: box and click OK. Now just type as normal!

Quickly remove unwanted rows/columns or cells

Imagine you have a large table of data and you want to delete all rows in the table that have a blank cell in column "A".  Here's a quick method you can use:

  1. Select the entire column "A"
  2. Push F5 and click Special
  3. Check Blanks and click OK
  4. Go to Edit>Delete and make your choice.

Another very good method is to simply sort your data by the column containing the blank cells. Of course this may not be suitable in all circumstances.


Microsoft Excel VBA tips

Before we look at some VBA tips this month I would like to direct you all to a handy little Download I wrote and posted here: http://www.ozgrid.com/download/default.htm called PrintCol.zip.

This month I thought I would show you the ConvertFormula method! This method allows us to change a range of formulas from Absolute to Relative, Relative to Absolute, Relative Row to Absolute Row and so on... In fact we can change any relative or absolute aspect of any formula. The Excel help for the ConvertFormula is written as below:

Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both.


expression.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

expression   Required. An expression that returns an Application object.

Formula   Required Variant. A string that contains the formula you want to convert. This must be a valid formula, and it must begin with an equal sign.

FromReferenceStyle   Required Long. The reference style of the formula. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1.

ToReferenceStyle   Optional Variant. The reference style you want returned. Can be one of the following XLReferenceStyle constants: xlA1 or xlR1C1. If this argument is omitted, the reference style isn't changed; the formula stays in the style specified by FromReferenceStyle.

ToAbsolute   Optional Variant. Specifies the converted reference type. Can be one of the following XLReferenceType constants: xlAbsolute, xlAbsRowRelColumn, xlRelRowAbsColumn, or xlRelative. If this argument is omitted, the reference type isn't changed.

RelativeTo   Optional Variant. A Range object that contains one cell. Relative references relate to this cell.

So with the above in mind we can easily write a simple Procedure that will change our formulas for us

Sub MakeAbsoluteorRelative()
'Written by OzGrid Business Applications

Dim RdoRange As Range
Dim i As Integer
Dim Reply As String
'Ask whether Relative or Absolute
Reply = InputBox("Change formulas to?" & Chr(13) & Chr(13) _
 & "Relative row/Absolute column = 1" & Chr(13) _
 & "Absolute row/Relative column = 2" & Chr(13) _
 & "Absolute all = 3" & Chr(13) _
 & "Relative all = 4", "OzGrid Business Applications")
   'They cancelled
   If Reply = "" Then Exit Sub
    On Error Resume Next
    'Set Range variable to formula cells only
    Set RdoRange = Selection.SpecialCells(Type:=xlFormulas)
        'determine the change type
    Select Case Reply
     Case 1 'Relative row/Absolute column
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
        Next i
     Case 2 'Absolute row/Relative column
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
        Next i
     Case 3 'Absolute all
        For i = 1 To RdoRange.Areas.Count
            RdoRange.Areas(i).Formula = _
            Application.ConvertFormula _
            (Formula:=RdoRange.Areas(i).Formula, _
            FromReferenceStyle:=xlA1, _
            ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
        Next i
      Case 4 'Relative all
       For i = 1 To RdoRange.Areas.Count
           RdoRange.Areas(i).Formula = _
           Application.ConvertFormula _
           (Formula:=RdoRange.Areas(i).Formula, _
           FromReferenceStyle:=xlA1, _
           ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
       Next i
     CaseElse 'Typo
        MsgBox "Changetype not recognised!", vbCritical, _
       "OzGrid Business Applications"
 End Select
    'Clear memory
    Set RdoRange = Nothing
End Sub
The important part to note in the above Procedure is that we do not loop through all cells in the selection looking for formulas, we simply set a range variable to the SpecialCells method using xlFormulas as the Type argument. This way we are not:
  1. Looping through potentially thousands of cells we are not interested in.
  2. Forcing the user to select only formula cells.
I hope you can all find a use for this one, I know I certainly can and do!

Until next month, keep Excelling!

Kind regards

Dave Hawley

[email protected]

You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in it's entirety

Helpful Information

  • Have Excel open when reading the newsletter.
  • When copying and pasting formulas from newsletter (or Web pages) into Excel, copy the formula (Ctrl+C), select a cell then click within the formula bar (or push F2) then paste (Ctrl+V)
  • To get help on a specific formula push F1 and type the formula name then push Enter.
  • To get a reminder of a functions syntax, type = then the functions name, then push Ctrl+Shift+A
  • To default the Paste function (formula wizard) to a specific function type = then the functions name, then push Ctrl+A
  • To copy and paste any VBA code, open the Visual Basic Editor (Alt+F11), go to Insert>Module and paste in the code.
  • To run any code after copying and pasting it in, place your mouse insertion point anywhere within the code and go to Run>Run Sub/UserForm, or push F5
  • To easily access the Private module of the "ThisWorkbook" Object, while in Excel, right click on the Excel icon (top left next to File) and select "View Code".
  • To easily access the Private module of a Worksheet Object, while in Excel, right click on the Worksheet name tab and select "View Code".
  • If the VBA code is a Custom Function (i.e. begins with the word Function) after you have pasted the code into a Module, switch back to Excel (Alt+F11), go to Insert>Function... or push Shift+F3, scroll down to User Defined (under Function category:) then select the Function name from within the Function name: box.
  • To assign a shortcut key to any Macro go to Tools>Macro>Macros..., or push Alt+F8 then select the Macro name and click Options.



    Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation.