MS-Excel Tips
revised 29 Aug 2009
Copyright © 2003–2008 by Stan Brown, Oak Road Systems
Contents:
(September 2005)
Use Windows Explorer or My Computer to search your hard drive for the Officeav.dll and Avgoff2k.dll files. Be sure to include hidden and system files in the search options. If you find either (or both), unregister it (or them) by clicking on Start | Run, and in the Open box, enter:
regsvr32 /u "c:\{path}\officeav.dll"
regsvr32 /u "c:\{path}\avgoff2k.dll"
source: http://www.techtalkz.com/microsoft-office/45720-word-excel-powerpoint-requesting-virus-scan.html
(December 2007)
Example: Suppose you have a year number in cell AA17 and you want “Projections for (year)” in the footer. Insert this within a VBA macro:
ActiveSheet.PageSetup.LeftFooter = _
"Projections for " & range("AA17").text
It’s possible to specify formatting as well:
With ActiveSheet.PageSetup
RightHeader = "&""Book Antiqua,Regular""&14" & range("A1").value
End With
If you insert the code into the Workbook_BeforePrint event of ThisWorkbook, it will update the header automatically each time you print. Otherwise, run the macro manually before printing.
You can also put the following in a cell and use it as a persistent header row:
="Projections for " & AA17
source: newsgroup article “Cell value in header?” by David McRitchie (eGW4xn$wBHA.2128@tkmsftngp07, 2002-03-04), who promises more information on headers and footers on his web page
more information: XL: Inserting and Formatting Text in Headers and Footers (Q142136) gives formatting codes and sample Visual Basic macros
(updated September 2005)
Overview: generate pairs of independent normally distributed random numbers and then do a Fisher transform on them. An extra step is necessary because Excel’s =NORMSINV() gives very wrong answers for inputs ≤10-6 or ≥1–10-6.
=IF($A1>1E-06 AND $A1<0.999999, NORMSINV($A1), (IF($A1>0.999999,+5.2,-5.2)))
where the 5.2 represents higher sigma values
= B1 * sqrt((1 + ρ)/2) + D1 * sqrt((1 − ρ)/2)
= B1 * sqrt((1 + ρ)/2) − D1 * sqrt((1 − ρ)/2)
source: newsgroup articles “simulating r=0.50 with excel” by Jan Holvoet (3e3e9b73$0$390$afc38c87@sisyphus.news.be.easynet.net, 2003-02-03) and David Heiser (v43hj4pf7s6v62@corp.supernews.com, 2003-02-05)
(September 2003)
Type = and the name of the function and the opening parenthesis, then press Ctrl-Shift-A. The names of the arguments will be revealed.
You can also pres Ctrl-A to bring up a dialog box with arguments, similar to the Insert → Function dialog.
source: newsgroup article “Re: UDF’s, XLA’s and #NAME!” by Dave Peterson (47617477.E9C98735@verizonXSPAM.net, 2007-12-13)
(December 2007)
Regardless of your default format, if you type something like 1/15 in an unformatted cell Excel renders it as 15-Jan. If you copy the following code into the ThisWorkbook module you will get your desired format:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.NumberFormat = "d-mmm" Then
Target.NumberFormat = "mm/dd/yyyy"
End If
End Sub
source: newsgroup article “Default date format for dates entered without a year” by Jim Rech (eTFvH9JVIHA.1188@TK2MSFTNGP04.phx.gbl, 2008-01-11)
(August 2009)
Chip Pearson’s page has a complete list, and ASAP Utilities has a differently-formatted page that also includes keyboard shortcuts in the VB editor.
(December 2007; revised October 2008)
(November 2008)
Excel 2007 likes to take over, but if you run this command-line command
"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /o
then double-clicking an .xls file will start Excel 2003. You can still run Excel 2007 for any file by opening it first.
source: email from Bill Wood, 2008-09-11
(November 2008)