If your Excel worksheet has a lot of Smart View formulas (e.g. more than ten thousand HsGetValue), you may find that each cell with a Smart View formula shows "Error" after you refresh the worksheet. I think Excel encountered an error during the refresh process and does not update any of the cells. The workaround is to type Ctrl+Alt+F9 which "calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation" (see Excel shortcut and function keys). This method of recalculation isn't visible in the Excel Formulas, Calculation menu.
2014-03-11
Timestamps in yyyymmdd format using Visual Basic
I often use the "yyyymmdd" format for timestamps but the Microsoft FormatDateTime supports only available operating system formats and FormatNumber doesn't support left padding integers with zeros, so here's a little bit of code to get the date format that I want:
dtNow = Now
strToday = Year(dtNow) & Right("0" & Month(dtNow), 2) & Right("0" & Day(dtNow), 2)
The dtNow stores the current date to avoid the date changing just after midnight. The Right("0" & Month(dtNow), 2) trick ensures that the output always has two digits. I think I first saw this idiom here: VBScript How can I Format Date?.
Subscribe to:
Posts (Atom)