I thought I would blog some handy personal macros I use day to day. This is the first and I will do others.
This one simply is a macro to select all formulas (so I called it SelectAllFormulas) on the worksheet using the SpecialCells method. Nothing hard in this and I used the macro recorder to get the basic code to do the job quickly. The idea is to have these kind of handy tools accessible for your daily work. Some people put these in their Personal.xlsm hidden workbook which is great if you only work from one PC and some attach them to their QAT which is great if you only have a few. Others have lots of them and want them accessible from the Ribbon. Much easier said than done. It used to be so easy and practical for everyday users in Excel 2003 before the Ribbon but oh well time to let it go and overall the Ribbon is great. On this topic briefly, I use Ron de Bruin’s method to load my Personal Macros to the Ribbon which is based on John Walkenbach’s fantastic menu maker VBA code from that Excel 2003 era which together gives a dropdown on the Ribbon in modern Excel. Works great but it’s a little time consuming to maintain and can fall over if the macro is in error. Perhaps this could be a future blog topic. By the way, Ron de Bruin’s Excel Automation site is a fantastic resource to spend hours in. Sorry I know, I digress.
Anyway, I use this tool SelectAllFormulas macro to quickly eyeball the worksheet and check formulas are where I expect them to be and not in the middle of data or if there are formulas clobbered by data through my tinkering. Or I use it to review a client’s worksheets and understand what’s data and where the formulas are. I have similar little macros to select protected cells, unprotected cells and conditional formats to quickly see where these are on a worksheet and if all looks OK.
Where this got a little tricky was when I wanted to make the macro more robust and handle the situation where you use it on a sheet with no formulas. The SpecialCells will return a VBA error (it’s run time error number 1004 which is seared into my brain) if it encounters a Nothing result. Now normally error trapping should be straightforward and the code upon error is diverted to an error handler and a message like “Hey there are no formulas on this sheet” tells you what happened and the code then stops with Exit Sub. There were lots of blog entries out there showing happy solutions for people doing this with On Error Resume Next or On Error Goto ErrHandler or if the range = nothing and so on. But none of this worked for me. I tried it all. If the line ‘Set r = ActiveCell.SpecialCells(xlCellTypeFormulas)’ returned an error nothing went past it. So after a lot of denial and frustration I decided there was something happening here no one was raising in blogs or something particular to my own PC’s Excel environment . Perhaps it’s an error in Excel from Office 365 only. In any case I was not going to solve this with Error Handling on .SpecialCells.
So Plan B is I have a two step process with the extra step being the check that there are no formulas on the worksheet by NOT using the SpecialCells method. I came up with testing the CurrentRegion with .HasFormula method then the second step is to then use .SpecialCells. It worked.
The moral of the story is in Excel there is nearly always a work around!
[vb]
Sub SelectAllFormulas()
Dim r As Range
Set r = ActiveCell.CurrentRegion
‘ TheExcelFactor.com
‘The first test here is because On Error Resume Next or Goto etc doesn’t work with ativecell.SpecialCells(xlCellTypeFormulas)
‘if there are no formulas on Worksheet. All blogs say error trapping eg. on error resume next or on error goto or
‘testing for r = Nothing works geat but I tried it all and it doesn’t. Maybe an Excel Office 365 only issue. So no worries a 2 step
‘process testing using range.Hasformula for CurrentRegion before using .SpecialCells method works well.
If r.HasFormula = False Then
GoTo NoFxErr
Else: Set r = ActiveCell.SpecialCells(xlCellTypeFormulas)
End If
r.Select
Set r = Nothing
Exit Sub
NoFxErr:
MsgBox “There are no formulas to select in the Worksheet’s CurrentRegion”, vbOKOnly, “Please Note…”
Set r = Nothing
End Sub
[/vb]