Daily posts of Excel tips…and other stuff
Archive for the ‘VBA Code Library’ Category.
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 [...]
I have an array of user defined types. The type has three elements and I need to sort on all three. Surprisingly, I've never had to sort an array of udt's before. Here's how I did it:
Type MyInfo
lType As Long
sName As String
dStart As Date
End Type
Sub Start()
[...]
Sometimes I need to analyse data (row-by-row) in a worksheet with a lot of columns. Sometimes I then want to see information from a cell in a column way off to the far right together with a couple of columns on the left side of the sheet. Of course you can split the window, [...]
Nigel wants a hyperlink to another sheet based on the sheet's Codename, rather than what's displayed on the tab. The tab can be changed by the user is the issue here, I guess. As part of that, this is a function I came up with to do the conversion. I didn't really [...]
