In creating a table header that explains what the row and column values represent, a common approach is to use a cell with text separated by a diagonal line. This, first for me, video tutorial explains how.
For those who prefer a text explanation:
Format the cell “border” to add a diagonal from the left-top to the right-bottom (it’s one of the line choices in the Format Cells dialog box | Borders tab).
Then, type several spaces the literal Hours ALT+ENTER to create a newline in the text and then the literal Days.
Adjust the number of spaces before the literal Hours to get the desired effect.
An alternative, which is simple albeit crude, is to type Days space \ space Hours. {grin}
I needed to copy a chart to a picture, but I wanted it to be an enhanced metafile (EMF) which is kind of like a vector graphic picture format. EMF graphics scale well when the page resizes.
A user would select the chart, run the macro and a dialog would ask them where to save the picture to – pretty simple, but handy!
It uses the clipboard to do the conversion.
Have you ever experienced the dreaded “Circular reference warning” popping up when you opened an Excel file or entered a formula?
Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll tried to demystify that message in a new article:
Whereas I am no fan of using circular references, they can be beneficial to your model and really solve the problem you are trying to solve. So here is some advice on how to properly work with them.
When you put parentheses around an object, VBA evaluates that object and, absent a property, returns the default property. The first Add might error or might not work as expected. Here’s another example:
Sub test2()
Dim dValue AsDouble
dValue = 1.1
Debug.Print (dValue)
Debug.Print dValue
EndSub
In this case the parentheses don’t do any harm, but they’re still unnecessary. And finally:
Sub test3()
Dim lValue AsLong
lValue = CLng(1.1)
EndSub
In this case there is no space before the open parenthesis, so it’s not just preferred, but necessary.
Can you think of a case where an open parenthesis preceded by a space is required or preferred?
There are instances when there are data series plotted on both the primary and secondary axes. For example, suppose we want to plot the two series A and B in Figure 1, with the elements in column B as the x-axis values. The A series will be a column cart on the primary axis and the B series will be a line chart on the secondary axis.
I complete a timesheet every 14 days. I got tired of doing math in my head, so around August 13, 2010 I put a stop to it. Here’s what the date cell on my timesheet looks like now.
F2 to edit the cell, “+14″ and enter. It’s nowhere near too long as formulas go, but it’s starting to bother me. Time to consolidate. Select the 14s.
Press Control+= (F9 works too, but my muscle memory is control and equal sign)
Enter. Next pay period, my timesheet will look like this
I want to put a SQLite3 file in a shared Dropbox folder and run an Excel app with that as the backend database. I’m using the SQLite ODBC Driver and some VBA to drive a simple userform.
My theory is that the file access will be so short and infrequent that I won’t have any file locking problems.
Who wants to test it out? You need a Dropbox account, the aforementioned driver, and a copy of the workbook below. Oh, and Excel if you didn’t already figure that one out.
Install the driver. Leave a comment or send an email to dkusleika@gmail.com with your Dropbox info and I’ll share a folder with you. Then open the workbook and start adding and modifying records in the userform. Ideally, I’d like to coordinate a five minute period where a few of us agree to hammer on it and try to break it.
