Category Archives: Issues and problems

Excel Issues and problems

The equivalent numerical values of TRUE and FALSE in Excel.

FALSE is zero (0).  TRUE can be any non-zero value (even negative), although 1 is most often used.

Just in case you’re unsure.  I was for a short while today and there seems to be a lot of conflicting opinions on the web.

Excel Issues and problems

Missing menu items in Excel?

Every now and then Excel can throw a bit of a wobbly and unusual things can happen.  One of these which has happened to me a number of times is that the menu bar (File, Edit, View, etc..) can have items missing from it.  How do you get these back?  Where have they gone?  Aaarrrgh!!!

Actually it’s nothing to worry about and it can be fixed really easily.  Follow these steps:-

  1. Right click anywhere in the menu area and choose ‘Customize’ from the bottom of the huge dropdown list.
  2. From the dialog that appears, select the first tab (‘Toolbars’) and from the list make sure that you select/highlight the item called ‘Worksheet Menu Bar’.
  3. Click the ‘Reset…’ button on the right of the dialog and confirm that you wish to reset.

Et voila!  Your menu items should be reset to how they were when Excel was first installed.  One thing to bear in mind however is that if you’ve created any custom menu items you’ll have to add them again.

Excel Issues and problems

VBA: Compile error. Can’t find project or library

Another day, another problem with my spreadsheet application.  Just tried to run it on another machine in my office and this is the error I got, followed by the VBE highlighting the use of ‘Chr’ in my code.

A little bit perplexed I checked out the References (Tools>References) in the VBE and found that one was marked as ‘missing’, so unchecked this and saved, then closed the sheet.  Once I’d re-opened it things seemed to work absolutely fine.

If you do get this problem, have a look at your references box to check for missing items.

For most things to work you will need at least the following four references ticked also:-

Visual Basic For Applications
Microsoft Excel xx.x Object Library
Microsoft Office xx.x Object Library
OLE Automation

My assumption will also be that there is a similar fix if you are using Access or Word (or any other software which allows programming in VB/VBA), so I would check for missing references if you have any of the same problems in those also.

Excel Issues and problems

Excel VBA: Pagesetup properties (and problems!)

I’ve just had a complete nightmare with a Microsoft Excel application I’ve been building, especially concerning printing and changing page setup settings through VBA.  Everything seemed to work fine on my machine, but moving to another the code routines I had took ages to the point where the VBA routine which I was writing to save people time was taking almost as long to run as if an experienced Excel user decided to work manually.

This, obviously won’t do.

After quite a while digging around I didn’t really find anything that has fixed the problem, but there are certainly some little hints and tips I’ve found around the matter to speed things up:-

  1. It might be worthwhile trying a different printer driver on your machine.  For some reason Excel communicates with the driver whilst carrying out its actions.  Seems silly to me, really.
  2. Get into your code and get rid of any of the ‘pagesetup’ bits you don’t need, or ones which contain default values.  Think about the bits you’re actually changing settings-wise and delete the rest.  This can speed things up hugely.
  3. You could try creating a blank template worksheet with the correct printer settings such as orientation (landscape, portrait etc) already set up and saved within it.  You can then load this up and do your stuff with it if you’re creating a sheet from scratch – this is something I do quite often as it means I can also set up various other aesthetic bits too which would otherwise result in lots of unwieldy code.
  4. I’ve read some mention of using Excel 4 macros, but I’d suggest steering away from that if possible as it’s outdated as it is and probably won’t be supported in future versions.

I’ve only tried this out in Excel 2003.  Does the same issue exist in Excel 2007 at all?  I don’t have it installed on my machine at the moment so can’t test it.