Tuesday, 03 November 2009Friday, 23 February 2007

OzGrid's Free Excel Newsletter

Thismonths Excel newsletter look at File ( Workbook ) size increase and what can bedone to reduce it. We also look at phantom links that can occur when Workbooksare no longer linked and have been deleted. In the VBA section we look at thecorrect way to reference Excel Worksheets, Ranges and Object collections.

MicrosoftExcel and VBA for Excel Training Information (auto response)

OrderTraining Online

HireOzGrid For Excel/VBA

PaidAdvertisement

What's new at OzGrid.com

Hi all,

Wellanother very busy month has passed since the last newsletter with lots happeningat OzGrid.  Firstly, my wife and silent partner Raina has now become anot-so-silent partner and has taken on an expanded role at OzGrid.  Withthe ever-increasing workload it had become evident that I am not (much to myhorror) superman and that I needed help in the day to day running ofthings.  Raina has now cut her hours back as a lecturer at our localCollege of Tertiary Education and is taking a more active role in the businessby not only running the bookwork side of things, but over the next few monthsshe will be doing more of the day-to-day running of the business.  The ideais to free me up to concentrate on programming, consulting and answering allquestions arising from students undertaking our training courses.  

Inline with this our real-time CREDIT CARD facility is finally up and running andworking extremely well.  After much frustration with our local Australianbank, we finally decided to give them the flick and use a UK based company bythe name of World Pay.  There is no comparison with the way World Payhandle their business compared to the way our local bank handles theirbusiness.  After five months of negotiations with our bank, we still had nocredit card facility on our website.  But, after only 4 weeks ofnegotiation with World Pay we have now had our Credit Card facility up andrunning for nearly a week with no hitches so far!!!!!  We hope that thisfacility will be of benefit to our customers, and so far we have received somegreat feedback.  Guess it pays to shop around - even if it is outside yourown country!!!

Tolaunch the Credit Card facility on our website, we have now altered the pricesfor 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 anadditional feature to our website, you can now click here tosee our course outlines in detail.

Microsoft Excel tips

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

Unusual File Size Increase

Unfortunatelyit is not too uncommon to have an Excel Workbook increase in file size for noapparent reason. Below are some methods to overcome this problem, that should beperformed in the order stated! Please note OzGrid accepts no responsibility forany loss of data. This means save a copy of your Workbook first. Aftereach step Save and check the File size by going to File>Properties/GeneralIf 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 followthe 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  Notethe 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 undercontrol it may also be a good time to give your PC a birthday and clean up allthose space hogging Temp files etc. I have found an excellent page on the WWWthat 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 toanybody!

PhantomLinks

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

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

Ithought this month we would take a look at the most frequently encounteredObjects in Excel, these are the Worksheet and the Range. While many of you maybesaying "I already know about these" it amazes me just how many times Isee these used incorrectly.

Worksheet

The Worksheet is without doubt the mostfrequently used Sheet in Excel, possibly followed by the Chart sheet. Before weStart 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 sheettypes, eg Worksheets and Chart sheets, while Worksheets only refers toWorksheets!

Possibly the most frequent error I see is the manner inwhich Excels Worksheets are referred to. The most common method is using thesheets tab name, eg Sheets("Sheet1") or Worksheets("Sheet1")Both of these methods are fine just so long as the tab name is not changedwithout your knowledge. Even if you do know about the change you still need torevisit your code and change it accordingly. Another method is using it's Indexnumber (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 thesheets 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 namechanging 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 inthe "Properties Window" of the Sheet, it's the one at the topnext to "(Name)". Lets say you need to change the cell value ofcell 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 theworst method as they know no different. Not only does the CodeName meanour sheet can be re-named at any time and/or moved, but by using the CodeNameand typing the . (Dot) you are presented with a list of all the sheets Propertiesand 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 notalready 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 tosee 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 sharethe same name it will call it Sheet<NextNumber> if the sheet alreadyexists. We then check to see the name of the ActiveSheet (sheet just added) anddelete it if the name was not applied.

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

Range

Perhaps the number 1 rule with ranges is name them! Thisis so very important in Excel VBA as it's the only sure way to ensure you areworking 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 anyexisting named range "MyRange" without any warning. To check first youcan 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 commonborders) so you could use:

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

Another very common problem is justhow do I find the end of a particular range that will change all the time. Theanswer is very simply, ie come up from the very last cell in the column and stopat the first cell containing data.

Sub FindLastCellInRange()
    MsgBox Range("A65536").End(xlUp)
End SubIf you wish to include all cells inthe 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 inour range we may want to add a SUM function to it so we have a total and thenend of our range, here how:

SubSumMyRange()
    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 needto select or a cell to work with it lets finish this chapter on ranges off witha fail safe method of going to a particular cell, and this is via the GoToMethod. The big advantage to this method is that you never have to be on thecorrect sheet to for it to work.

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

Note the setting of the Scrollargument to True, this places the cell nicely in the top left of thescreen 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

[email protected]

HelpfulInformation

OrderTraining Online

HireOzGrid For Excel/VBA