This is an experiment in writing VBA code without using Hungarian notation.
I hated every minute of it. I hate reading it right now. I’ve struggled to pinpoint why it displeases me so, but I have a theory. It's hard to tell the difference between keywords and variables.
Full article: The great Hungarian debate
A little while ago, I made a blog post on why I think we no longer need Hungarian Notation.
This morning, the esteemed Excel expert, Dick Kusleika of Daily Dose of Excel, wrote a response post to my rant against Hungarian Notation in The great Hungarian debate. I started writing my response in the comments. But I'm terrible at brevity. So I'm going to post my response here.
Full article: A VBA coding manifesto
If you select more than one sheet in a workbook, and start typing or formatting, that data or formatting will be entered in all the selected sheets, not just the active sheet. That's a great feature – if you want to type on all the sheets, or add formatting, or whatever. But it's not so great if you don't notice that the sheets are grouped, and accidentally work on all the sheets.
It's easy to forget that you grouped a few sheets – you interrupt your work for a quick phone call, or grab another cup of coffee, and poof! That memory is gone, and the worksheet damage can begin.
A solution to this problem is to use a small VBA macro that warns you when worksheets are grouped.
Full article: Warning for grouped sheets
How do you know your workbook is accurate? This question is important because it's our responsibility to ensure that data flows properly through the workbook, with numbers that tie out, and with reports that are internally consistent.
One way to help ensure workbook accuracy is by creating a worksheet within the workbook dedicated to this task, and one that will help you perform your review more quickly. In my workbooks, I call this error check worksheet ErrorCk.
Full article: Not available
This article provides a high level overview of Modular Spreadsheet Development principles.
Modular Spreadsheet Development makes it possible to build spreadsheets exponentially faster while reducing the risk of errors and making spreadsheets much easier to understand.
This concept is not completely new, but I'm writing this article because the spreadsheet modelling world would be a much better place if it was more commonly adopted.
Full article: Modular spreadsheet development – A thought revolution
On the face of things, precision seems to always be preferable. For instance, 12.324 is more precise that 12.3. There are times, however, when precision is Not desirable, and may even not make much sense.
Formatting in Excel will make your numbers appear to be rounded. The trouble with relying on formatting is that Excel retains the precision in the background, and any calculations will be based on that precision.
Sometimes precision is the enemy. In such cases, use
Full article: Round like a circle
Excel keeps track of the last cell you used during your current session, and uses it to determine the used range of a sheet. The last cell is the one you get to when you press Ctrl+End.
This article describes some VBA functions to get the:
- First used cell.
- Last used cell.
- Actual used range.
Full article: Get the actual used range in a spreadsheet
One of the repeating problems I've noticed recently are formulas that are not written efficiently. Efficiency means writing formulas that are quick to write, easy to update, and intuitive.
This article describes some techniques for writing efficient formulas, including:
- Absolute vs Relative references.
- Named ranges.
- Excel tables.
- Column number and row number arguments.
Full article: Writing Excel formulas efficiently
The Table feature of Excel remains one of my favorites. This post explores one very specific aspect of the feature, the total row.
Once the data is stored in a table, we can use the table's name in our formulas. Since tables auto-expand to include new rows, new transactions are automatically included in our formulas. This auto-expansion property helps us build more reliable workbooks.
Another special property is the total row. The total row can be toggled on, or off, at any time to show, or hide, table totals. There are a few interesting points to note about the total row, so, let's dig into the details.
Full article: Table total row