Tag Archives: VBA

A Custom Tab RibbonX Template Which Handles State Loss

Posted on September 7, 2013 by

The first few times you want to customise the Excel ribbon you’ll find yourself scouring the internet and cobbling together bits and pieces from different sites to get the result you want. For this post I’ve created a template in … Continue reading →

Posted in Microsoft Excel | Tagged , , , , , , , | 2 Comments

Count Distinct Or Unique Values – VBA UDF

Posted on June 26, 2013 by

A handful of posts ago I looked at some FREQUENCY() formulae which could give a count of distinct or unique values in a given column. The formulae became quite complicated and slow – particularly when dealing with mixed data types – … Continue reading →

Posted in Microsoft Excel, Microsoft Office | Tagged , , , , , , , , , , , , , , , , , , , | 6 Comments

VBA: What Type Are Your Constants?

Posted on June 11, 2013 by

In the previous post we saw a common mistake when declaring VBA variables and used the Locals window to show that variables are Variant types unless you specify otherwise by using: As Type in the declaration, eg. Dim strHello As String A type … Continue reading →

Posted in Microsoft Excel, Microsoft Office | Tagged , , , , , , , , | 3 Comments

A Common Mistake When Declaring Variables In VBA

Posted on June 8, 2013 by

For today’s post I’d like you to imagine that you’re an up and coming VBA programmer who is starting to feel at home with the IDE and language. You’ve read about some VBA best practices which has made you aware of the … Continue reading →

Posted in .Net, Microsoft Excel, Microsoft Office, VB .Net | Tagged , , , , , , | 10 Comments

Phantom Breakpoints

Posted on March 10, 2013 by

I had a horrific Excel 2003 crash today. When I restarted Excel and opened any workbooks – including locked xla’s – which had been open during the crash, I found that they were riddled with phantom breakpoints. I’ve seen the occasional phantom breakpoint before, but … Continue reading →

Posted in Microsoft Excel, Microsoft Office | Tagged , , | 4 Comments

Determine the Office Edition in VBA

Posted on February 2, 2013 by

A few years ago a user asked how to use VBA to determine the edition of the active session of Office. I’m not sure why this would be useful (perhaps to show as information on a userform?) but I duly obliged … Continue reading →

Posted in Microsoft Excel, Microsoft Office | Tagged , , , , , | 3 Comments

Deleting Rows With VBA

Posted on November 29, 2012 by

A few years ago I posted a series of VBA samples on a forum which outlined different ways to delete rows in an efficient manner. The series proved to be very popular and, since that forum is now in decline, I’m going to resurrect those notes on here. The … Continue reading →

Posted in Microsoft Excel, Microsoft Office | Tagged , , , , , , , , , , , | 15 Comments

Clearing Excel tables

Posted on September 1, 2012 by

The VBA macro recorder is a really wonderful tool, especially when you want to get an insight into an unfamiliar part of the Excel object model. However, it’s notorious for producing inefficient code – and clearing a table is a … Continue reading →

Posted in Microsoft Excel, Microsoft Office | Tagged , , , | 32 Comments