ALL YOUR EXCEL NEEDS
|FREE Excel STUFF|
NEW! Free Questions, Newsletter ONLY. You need a username and password from the Excel Help forum. If you do not have one, join here for free.
FREE DOWNLOADSSmartVBA | SmartVB6 | CodeVBA | CodeVB6 | Code Generator Pro | TraderXL Pro Package | Catalog | MORE..
See Also: Excel Workbook Size Increase/Excel Phantom Links
Microsoft Excel accepts a number of optional switches that you can use to control how the program starts. This article lists the switches and provides a description of each switch.
SwitchesThe following table lists the switches that you can use to control how Excel starts. Switches are not case sensitive: /O is the same as /o. In some scenarios, you can use more than one switch at a time. If you use more than one switch at a time, you must separate the switches with spaces -- for example: /o /s
Switch Function ---------------------------------------------------------------------- /e, /embedded Forces Excel to start without displaying the startup screen and creating a new workbook (Book1.xls). Example: /e or /embedded /m Forces Excel to create a new workbook that contains a single macro sheet. Example: /m /o Forces Excel to re-register itself. Specifically, the following key is rewritten in the registry: Excel 97: HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel Excel 2000: HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel Excel 2002: HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel Excel 2003: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel NOTE: If this key contains values that are not valid, they are not corrected by using this switch. This switch only replaces missing values. Example: /o /p <folder> Forces Excel to use the path that you specify as the active path instead of the default path. Example: /p "C:\Windows" /r <file name> Forces Excel to open the specified file in read-only mode. Example: /r "C:\My Documents\Test.xls" /s, /safemode Forces Excel to bypass all files in the Application Data\Microsoft\Xlstart folder, the default XLStart folder located in the directory where Excel or Office is installed, and the alternate startup file location specified on the General tab of the Excel Options dialog box. It also forces Excel to bypass the toolbar file (Excel.xlb or <username>.xlb). You see "Safe Mode" in the Excel title bar. Use this switch when you want to start Excel in safe mode. Example: /s or /safemode
/t Forces Excel to use the specified file as a template for the default workbook. /regserver Forces Excel to register itself and then quit. Use this switch when you want Excel to rewrite all its registry keys and reassociate itself with Excel files, such as workbooks, charts, and others. Example: /regserver /unregserver Forces Excel to unregister itself and then quit. Example: /unregserver
How to Add a Switch to the Microsoft Excel ShortcutTo add a switch to the Microsoft Excel shortcut:
How to Run Excel One Time with a Switch by Using the Command LineIf you want to run Excel with a switch occasionally, you can add the switch to the command line. To do this:
Excel VBA Macro Codes
SEE ALSO: Excel 4.0 EVALUATE Function
As you may, or may not know, we can use standard Excel Worksheet Functions in VBA by preceding the Function name with WorksheetFunction or Application. E.g. the example below will sum the range A1:A10.
Sub SumUp() MsgBox WorksheetFunction.Sum(Sheet1.Range("A1:A10")) End Sub Sub SumUp2() MsgBox Application.Sum(Sheet1.Range("A1:A10")) End Sub
However, IF there ANY Formulae Errors in the range used, it will result in a RunTime Error so you may want to replace all error cells with zero, or at least confirm the range has no formula errors with the SpecialCells Method, like below;
Sub ReplaceErrors() On Error Resume Next With Sheet1.Range("A1:A10") .SpecialCells(xlCellTypeFormulas, xlErrors) = 0 MsgBox WorksheetFunction.Sum(.Cells) End With On Error GoTo 0 End Sub Sub CheckForErrors() Dim rErrCheck As Range On Error Resume Next With Sheet1.Range("A1:A10") Set rErrCheck = .SpecialCells(xlCellTypeFormulas, xlErrors) If Not rErrCheck Is Nothing Then MsgBox "Please fix formula errors in selected cells" Application.Goto .SpecialCells(xlCellTypeFormulas, xlErrors) Else MsgBox WorksheetFunction.Sum(.Cells) End If End With On Error GoTo 0 End Sub
With the Evaluate Method, we must still check for, or fix, formula errors in the range we Evaluate with an Excel Formula, but there is less typing and we can simply copy Formulas from the formula bar. The VBA Macros below shows some uses of the Evaluate Method, BUT contain no error checks. Don't forget to add them like above.
Sub EvaluateSum() MsgBox Evaluate("SUM(1,2)") MsgBox Evaluate("SUM(Sheet1!A1:A10)") End Sub Sub EvaluateVlookup() MsgBox Evaluate("VLOOKUP(Sheet2!A1,Sheet1!B1:C10,2,FALSE)") End Sub
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft