Tag Archives: programming

Excel Hints and Tips Tips Visual Basic For Applications (VBA)

Using “Is Not Intersect” in VBA code

I’ve been writing some code to capture the event when a particular cell is changed in one of my Excel Worksheets and, using the power of the interpipes found a simple solution to do this.  It looks something like this in code:-

Private Sub Worksheet_Change(ByVal Target As Range)
	If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
		MsgBox "Hello"
	End If
End Sub

Whilst this works perfectly and does what I want it to do I didn’t understand why I should use ‘If Not Intersect….Is Nothing’ when it would be simpler to use ‘If Intersect(Target, Range(“A1:A10”) > 0’.

I soon found out when I changed something outside of the target range (ie A1:A10) and Excel threw an error at me.  Wondering why I had a bit of a browse on the internet and discovered the reason.  The Intersect method returns a range object, but if there is no range found it will simply return an answer of nothing, which causes the error.  Using this code ensures that, if nothing is returned, the IF statement can just end with no errors returned to Excel.  Nice.

It’s probably not the most interesting post, but I thought I’d document it in case you or I ever come across it again.  A full (and much more detailed) examination of the Intersect method in VBA can be found here.

Things I've learned today

Things I’ve learned today

  • Quite a lot about using VBA to do spreadsheety stuff, and that it can be a pain in the backside getting things to work the way you want them to.  I did finally manage to get things working the way I want, but I lost my temper quite a few times during the process.
  • A little bit about CSS, but not enough – my next big challenge I think.
  • That perseverance does pay off if you just keep on trying (kind of related to my first point)
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.