Tuesday, 13 March 2007Friday, 23 February 2007

OzGrid's Free Excel Newsletter

This months Excel newsletter look at File ( Workbook ) size increase and what can be done to reduce it. We also look at phantom links that can occur when Workbooks are no longer linked and have been deleted. In the VBA section we look at the correct way to reference Excel Worksheets, Ranges and Object collections.

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

Order Training Online

Hire OzGrid For Excel/VBA

Paid Advertisement

What's new at OzGrid.com

Hi all,

Well another very busy month has passed since the last newsletter with lots happening at OzGrid.  Firstly, my wife and silent partner Raina has now become a not-so-silent partner and has taken on an expanded role at OzGrid.  With the ever-increasing workload it had become evident that I am not (much to my horror) superman and that I needed help in the day to day running of things.  Raina has now cut her hours back as a lecturer at our local College of Tertiary Education and is taking a more active role in the business by not only running the bookwork side of things, but over the next few months she will be doing more of the day-to-day running of the business.  The idea is to free me up to concentrate on programming, consulting and answering all questions arising from students undertaking our training courses.  

In line with this our real-time CREDIT CARD facility is finally up and running and working extremely well.  After much frustration with our local Australian bank, we finally decided to give them the flick and use a UK based company by the name of World Pay.  There is no comparison with the way World Pay handle their business compared to the way our local bank handles their business.  After five months of negotiations with our bank, we still had no credit card facility on our website.  But, after only 4 weeks of negotiation with World Pay we have now had our Credit Card facility up and running for nearly a week with no hitches so far!!!!!  We hope that this facility will be of benefit to our customers, and so far we have received some great feedback.  Guess it pays to shop around - even if it is outside your own country!!!

To launch the Credit Card facility on our website, we have now altered the prices for our courses.  They are:

• 1 course for US$150
• 2 courses for US$250
• 3 courses for US$325
• Additional courses for US$75

As an additional feature to our website, you can now click here to see our course outlines in detail.

Microsoft Excel tips

For this months Excel tips I thought I would cover what seems to be an ever increasing problem, File size blow-out and phantom links. As a developer of Excel I find myself encountering these problems quite frequently! Over the years I have set up some 'way and means' to deal with these 2 problems and would like to share them with you

Unusual File Size Increase

Unfortunately it is not too uncommon to have an Excel Workbook increase in file size for no apparent reason. Below are some methods to overcome this problem, that should be performed in the order stated! Please note OzGrid accepts no responsibility for any loss of data. This means save a copy of your Workbook first. After each step Save and check the File size by going to File>Properties/General If it has reduced the file size you shouldn't need to go any further!

1. Avoid saving as multiple versions. Save as "Microsoft Excel Workbook (*.xls). You can make this the default by going to Tools>Options/Transition and selecting Microsoft Excel Workbook from the Save Excel files as Combobox. This alone can reduce file size by 50%



2. Do the following to ALL Worksheets. Select any single cell then push F5, click Special and check the "Blanks" option and click Ok. Now go to Edit>Clear>All. Manually go to the last used row in the Worksheet (do not use Edit>Go to-Special "Last Cell"). Select the entire row beneath, then holding down the Ctrl+Shift key push the Down Arrow. This should select all unused rows. Now go to Edit>Clear>All. Do the same for the Columns. Now Save.



3. Right click on any sheet name tab and select "Select all sheets". Now right click again and select "Move or copy" Check the Create a copy option then choose "(New Book)" and click Ok. Save this new Workbook as any name and check it's file size via File>Properties/General. If no dramatic difference delete one sheet at a time and Save, each time check it's file size via File>Properties/General and see if there has been a disproportional reduction in file size. If there has, the Worksheet may have been corrupt, go back to the the original Workbook and copy the entire contents of the possibly corrupt Worksheet. Come back to the new Workbook, insert a new Worksheet and paste the data onto it. If this does not help you may need to delete a few rows at a time (Saving each time) until you find the offending row(s).



4. If you have Modules and/or UserForms in the Workbook, open the VBE (Alt+F11) and right click on each in the "Project Explorer" (Ctrl+R) and Export. Now, in a new Workbook again open the VBE and go to File>Import File.. and import all Modules and UserForms into this Workbook. Use the Move or copy method (as shown in step above) to copy all sheets into this Workbook then Save.

This may also be a good time to follow the links and read about Excel's Start up switches. In particular the switch: /regserver Run this by closing out of Excel completely, going to Start >Run then typing in:
Excel /regserver  Note the space after "Excel"

XL97: Start up Switches for Microsoft Excel 97 (Q159474)
XL2000: Start up Switches for Microsoft Excel 2000 (Q211481)

Now that you have your file size under control it may also be a good time to give your PC a birthday and clean up all those space hogging Temp files etc. I have found an excellent page on the WWW that will help you do this step-by-step Clean Your PC It's from 'Dreamboat' of The Word Expert . As I personally have followed these steps, I can recommend them to anybody!

Phantom Links

Another quite common problem with Excel is that at times your Workbook may be asking you to update links when you do not have any. Of course the very first thing to check is that there are not any links you do not know about, which steps 1 and 2 will do.

1. Unhide any hidden Worksheets first. Then right click on any sheet name tab and select "Select all sheets". Now go to Edit>Find and in the Find What: box type: [*] also select Formulas from the Look in: box and click Find Next. This will find any formulae referring to another Workbook.



2. Go to Insert>Name>Define and scroll through the list to ensure no named ranges are referring to another Workbook.



3. Go to each sheet and ensure you have no linked Pictures, Charts or Controls.



4. Go to Edit>Links and select the file name under Source file, now click Change Source... In the "Change Links" dialog locate the Active workbook, ie the one you are in now, select it and click Ok. This will force the Link to refer back to itself. If you get an error go to step 5!



5. Open a new Workbook, save it as any name. Create a real link to it from you problem Workbook. Now go to Edit>Links and select the file name (not the newly created file!) under Source file, again click the Change Source... button but this time select the newly created Workbook from the "Change Links" dialog and click Ok. Now Save the Workbook and delete the real link you created and save.



6. Last but not least, if all else fails go here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q188449 and download the: Download Dellinks.exe now

Microsoft Excel VBA tips

I thought this month we would take a look at the most frequently encountered Objects in Excel, these are the Worksheet and the Range. While many of you maybe saying "I already know about these" it amazes me just how many times I see these used incorrectly.

Worksheet

The Worksheet is without doubt the most frequently used Sheet in Excel, possibly followed by the Chart sheet. Before we Start we should clear up one thing.

• The Sheets Property returns a Sheets Collection (A Collection object is an ordered set of items that can be referred to as a unit.) that represents ALL Sheets in the Active Workbook. 
• The Worksheets Property returns a Sheets Collection that represents ALL Worksheets in the Active Workbook. 

In other words, Sheets refers to both sheet types, eg Worksheets and Chart sheets, while Worksheets only refers to Worksheets!

Possibly the most frequent error I see is the manner in which Excels Worksheets are referred to. The most common method is using the sheets tab name, eg Sheets("Sheet1") or Worksheets("Sheet1") Both of these methods are fine just so long as the tab name is not changed without your knowledge. Even if you do know about the change you still need to revisit your code and change it accordingly. Another method is using it's Index number (the order is left-to-right), eg Sheets(1) or Worksheets(1) This certainly overcomes the changing of it's tab name, but not moving of the sheets order. It amazes me just how few people use the sheets CodeName. Using the CodeName means there is no need to worry about the sheet tab name changing and/or it's Index number changing. You can see a sheets CodeName in the "Project Explorer" it's the one not in brackets, or in the "Properties Window" of the Sheet, it's the one at the top next to "(Name)". Lets say you need to change the cell value of cell A1 on any sheet, here are the 3 methods from worst to best.

Sub TabName() 'Worst
    Sheets("Sheet1").Range("A1").Value = 100
End Sub

Sub IndexNumber() 'Better
    Sheets(1).Range("A1").Value = 100
End Sub

Sub ItsCodeName() 'Best
    Sheet1.Range("A1").Value = 100
End Sub

I think most of you would agree that most people opt for the worst method as they know no different. Not only does the CodeName mean our sheet can be re-named at any time and/or moved, but by using the CodeName and typing the . (Dot) you are presented with a list of all the sheets Properties and Methods.

Perhaps one common need for many VBA programmers is the adding of Worksheets to a Workbook only if the sheets they are going to add do not already exist. There is some very good help on this in Excel, just look under "Add Method (Sheets Collection)" but it doesn't tell us how to see whether a sheet already exists, here a 2 methods that can be used.

Sub AddASheet1()

    On Error Resume Next
    Application.DisplayAlerts = False

        Sheets.Add().Name = "NewSheet"
        If ActiveSheet.Name <> "NewSheet" Then ActiveSheet.Delete

    On Error GoTo 0
    Application.DisplayAlerts = True

End SubSub AddASheet2()
Dim wshExists As Worksheet

    On Error Resume Next
        Set wshExists = Sheets("NewSheet")
        If wshExists Is Nothing Then Sheets.Add().Name = "NewSheet"
    On Error GoTo 0

End Sub

Method 1 adds the sheet, calls it "NewSheet" then checks if the name was applied. As Excel will not allow 2 sheets to share the same name it will call it Sheet<NextNumber> if the sheet already exists. We then check to see the name of the ActiveSheet (sheet just added) and delete it if the name was not applied.

Method 2 Sets a Variable dimensioned as a a Worksheet Object to a sheet called "NewSheet", then checks if the Variable was Set (an Object variable not set returns its default of Nothing) and only adds the sheet if the sheet does not exist.

Range

Perhaps the number 1 rule with ranges is name them! This is so very important in Excel VBA as it's the only sure way to ensure you are working with the correct cell. To name a range in VBA, simply use:

Sub NameThatRange()
    Sheet1.Range("A1:A10").Name = "MyRange"
End Sub

How easy is that! Just be aware that this would replace any existing named range "MyRange" without any warning. To check first you can use a method almost identical to AddASheet2()

Sub NameaNewRange()

    On Error Resume Next
        If Range("MyRange") Is Nothing Then
            Sheet1.Range("A1:A10").Name = "MyRange"
        End If
    On Error GoTo 0

End Sub

Your range that you name need not be contiguous (share common borders) so you could use:

Sub NameNonContiguousRange()
    Sheet1.Range("A1:A10, C10:C20, E5:E100").Name = "MyRange"
End Sub

Another very common problem is just how do I find the end of a particular range that will change all the time. The answer is very simply, ie come up from the very last cell in the column and stop at the first cell containing data.

Sub FindLastCellInRange()
    MsgBox Range("A65536").End(xlUp)
End SubIf you wish to include all cells in the column down to the last cell use:Sub NameMyRange()
    Range("A1", Range("A65536").End(xlUp)).Name = "MyRange"
End Sub

Once we have found the last cell in our range we may want to add a SUM function to it so we have a total and then end of our range, here how:

Sub SumMyRange()
    Range("A65536").End(xlUp).Offset(1, 0) = _
        "=Sum($A$1:" & Range("A65536").End(xlUp).Address & ")"
End Sub

There is also End(xlDown), End(xlToLeft) and End(xlToRight)

Although it is very rare that you need to select or a cell to work with it lets finish this chapter on ranges off with a fail safe method of going to a particular cell, and this is via the GoTo Method. The big advantage to this method is that you never have to be on the correct sheet to for it to work.

Sub TakeMeThere()
    Application.Goto _
    Reference:=Sheet2.Range("Q100"), Scroll:=True
End Sub

Note the setting of the Scroll argument to True, this places the cell nicely in the top left of the screen for the user, which is really the only time you need to select a cell.

OK, that's it for this month - until next month - keep Excelling!

Kind regards

Dave Hawley

DavidH@OzGrid.com

Helpful Information

Order Training Online

Hire OzGrid For Excel/VBA