Tag Archives: VBA

A Custom Tab RibbonX Template Which Handles State Loss

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

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?

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

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

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

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

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

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