Daily posts of Excel tips…and other stuff
Archive for the ‘VBA Code Library’ Category.
Remember DOS? Back in the day, we had to limit our file names to eight characters plus a three digit extension (8.3) and we liked it. Today I needed to add Google Chrome to my startup batch file. Normally I can convert long Windows paths to short DOS paths in my head, [...]
Here’s a UDF that’s been done about a million times before. So why write my own? Oh, I don’t know. It’s faster to write it than to find one on the internet and modify it I suppose.
Public Function JoinRange(rInput As Range, _
Optional sDelim As String = "", _
Optional [...]
I run on a wide screen 22″ monitor (1680 x 1050). More than once I’ve delivered a prototype to the customer, and they’ve needed to scroll to find all the buttons, because their monitor was smaller than mine.
Here is a procedure for resizing the Excel Application to a specified dimension (measured in pixels).
Now I can [...]
I use a New Project workbook whenever I’m starting, wait for it, a new project. The idea is that I always have the same stuff in my projects, so why set it up from scratch. At first, it contained the minimum stuff - stuff that was guaranteed to be in every project. [...]
I had a worksheet table with blank rows separating the groups.
I needed to add another column - a formula - but wanted to retain the blank rows for formatting tidiness.
The table after adding the formula column:
Notice the formula produces zeros for the blank rows. I could just select each cell (D3, D5, D8, D10) and [...]
Here is one for the Code Library.
Somehow I end up misplacing this bit of code. So every time I need to do it, I end up re-writing it.
Perhaps I’ll save someone the same frustration along the way.
This code snippet will loop through each file in your folder (and subfolders).
For each workbook opened, it will unprotect [...]
From time to time, I am asked to audit a spreadsheet.
One of the common mistakes I see is an inconsistent formula inside a group of cells.
For example:
- A1 has the formula =G1 * 0.105
- A1 has been formula auto-filled to A1:E5
- Then some time later, cell A3 has been changed to =G3 * 0.107
So the [...]
This article describes a bug recently discovered by Ron de Bruin and which has also
been reported here.
The Application.InputBox function is very useful to get a range from
the user. Unfortunately, this function exposes a bug in Excel (all current
versions!). If the sheet on which a (range of) cell(s) is selected contains
conditional formatting using the : "Formula [...]
