Show FORMULA of another cell in Excel

[Top]    [GetFormula] [Install a Macro] [GetFormula Example]  [GetFormat] [Ex]  [Carpentry/Measurement]  [Format/Fill characters] [HasFormula] [Ex]  [BoldSum] [FontInfo] [FontStyle] [Select cells with formulas or constants]  [UseFormula]   [Remove all formulas from a workbook]  [Formula in MsgBox]  [Sheet Statistics]  [More Notes]  [Status Bar]  [Large/Small WS Formulas]  [GetFormulaInfo]  [AddIn (incomplete)]  [Related]  [Bottom] 

Not satisfied with the methods of showing formulas in Excel: 

• Tools --> Options --> View --> (formula on/off)
• Ctrl+`  is the equivalent shortcut (toggle on/off) -- accent grave to left of the 1,2,3 on the top row

A simple VBA User Defined Function (UDF) is the solution.  To show the formula of another cell, you can use a simple VBA function.  GetFormula was written as my first contact with an Excel newsgroup.  Most of the help came from Alan Beban, who offered me a more complicated version with offset.  I managed to simplify it by trial and error to exactly what I really wanted in a more generic formula.  It has proven very useful.

The Code for GetFormula

Function GetFormula(Cell)
   GetFormula = Cell.Formula
End Function

The following variation might look better but would not match the Formula view of Excel. 
Advantage is it shows the single quote, and it shows array formulas as array formulas.

Function GetFormulaI(cell)
   If VarType(cell) = 8 Then
    GetFormulaI = "'" & cell.formula
   Else
    GetFormulaI = cell.formula
   End If
   If cell.HasArray Then _
     GetFormulaI = "{" & cell.formula & "}"
End Function

if you ONLY want to see a formula or nothing.  My preference is for GetFormula or GetFormulaI
above but some people ask only to see an actual formula.

Function ShowFormula(cell)
    If cell.HasFormula Then ShowFormula = cell.Formula
End Function

The use of Cell.FormulaLocal in the above functions may work better for non English usage of Excel.

Placing Formulas into Cell Comments is another approach but would not recommend it as being very practical.

Obtaining intermediate results for a formula is possible but complicated see GFRV user defined function, posted by Harlan Grove 2002-02-27 misc.

Example using GetFormula   -- Additional examples under GetFormat

Example:  (Formula view)   A B C 1 =4+5 =GetFormula(A1) =GetFormula(B1) 2 =NOW() =GetFormula(A2) =GetFormula(B2)

Example:  (Data view)   A B C 1 9 =4+5 =GetFormula(A1) 2 1/16/98 22:59 =Now() =GetFormula(A2)

You can copy the =GetFormula(A1) downward to do the column. 

Notice that the GetFormula(cellname) also works on GetFormula(cellname). 

GetFormula has been very useful for me, hope it helps you as much. 

GetFormula failures:  On a protected sheet GetFormula will return #VALUE! if the cell being examined is hidden.  If a cell is hidden you cannot see the formulas on the formula bar.  If a cell is locked you cannot change the value or formula, but has no effect on GetFormula. 

Evaluating A Formula, step by step

Highlight a part of the formula in the Formula Bar and press F9.  The highlighted part of the formula is replaced by the result.  If you press Esc then the formula re-appears, but if you press Enter the formula or part formula is permanently replaced.  Charles Williams, 2001-04-20, and then mentions that... In Excel 2002 you can evaluate formulae step by step automatically.

Conditional Formatting

Conditional Formatting is considerably harder to show what you want to see.  It has a range that you can't just see, anyway the following is a start and will work best if it just has a formula, rather than "is less than" type of conditions.

Function CondFormula(myCell, Optional cond As Long = 1) As String
  'Bernie Deitrick programming 2000-02-18, modified D.McR 2001-08-07
  Application.Volatile
  CondFormula = myCell.FormatConditions(cond).Formula1
End Function

Description information for a Function

Your User Defined Functions (UDF) can be found using the Paste Function Wizard (Shift + F3).  Select "User Defined" which is near the bottom of the left-hand window and your UDF will appear on the right-hand window.

Entering a Description for the Function  Wizard (Shift+F3): in XL2000   (directions to install a macro or function are below) The parameter cell will show up in the Function Wizard Help without any intervention, so choose your parameter names carefully.  The description can be entered as follows:  Alt+F11 (VBE), F2 (View, Object Browser), Select the module on left, select the function on right, RClick, properties, fill in description.  For example the description for GetFormula reads as follows: Displays the formula used in the referenced cell.  For more information see http://www.mvps.org/dmcritchie/excel/formula.htm#getformula Laurent Longre has a method for Creating new function categories (Excel 97) in the Function Wizard, in the English subset of his main site which is in French.  You can also look at a Translator.Go.com assisted Translation to English Function names can be seen in the Paste Function Wizard (Shift+F3).  User Defined Functions are near the bottom of the left side window. Restrictions on Functions:  A Function invoked from a Worksheet cannot modify any other cell in any manner, nor can it modify the format or formula of any cell including itself.  (No such restriction of macro functions -- functions called from a macro). Can't get your function to work in XL97 and up If you installed a function and get  #NAME!  then the function cannot be found.  If you get  #VALUE1  then your operands are incorrect.  The Functions described on this page and most macros are installed in a standard module, but that is not a exactly what appears as the choice on the menu in the Visual Basic Editor (Alt+F11,  Insert,  Module) used in Excel 97 and up.

Install a Macro or User Defined Function

If you would like to begin with a simple example take a look at this first.
 Getting Started with Macros -- http://www.mvps.org/dmcritchie/excel/getstarted.htm

User Defined Functions and Macros are installed in (Normal) Modules in the same manner even though used differently.  The UDF will appear in the Function Wizard (Shift+F3), and the macros will appear in the Macros list  (Alt+F8, or Tools, macro, macros). The easiest way to install a macro is to record a macro using the macro recorder.  Regardless of your version of Excel, the macro recorder will install a macro for you.  You can add additional macros and functions to the same module.  Don't put all your macros in one module though since each module that is loaded will add to memory usage. Tools --> macro --> record macro Do some things as a repetitive task such as: Select ALL button; Edit --> GoTo --> Special --> constants --> (select text clear other flags); Tools --> macro --> stop recorder (or use square STOP button) The Select ALL button can be found at the intersection of the row and column headings. You can record your macro in your Personal Macro Workbook (in your XLSTART directory) by choice in the Store Macro In: box Loss of the square Record Stop button with the [x] can be rectified with Right-click on a Toolbar --> Customize --> Toolbars - check the Stop Recording box. Close Customize dialog.  Another way if Excel is still open is to open your latest *.XLB file (use START, Find, *.XLB). Version specific information In XL95 (Excel 7.0.A) You may install the macro in your personal workbook, or on the spreadsheet itself.  To Install the macro to your Module sheet.      Insert --> Macro --> Module       (if you don't have one) Also see  HELP --> Answer Wizard --> VBA Macros can be viewed in your macro sheets. There are no module sheets in Excel 97 (ver 8) or Excel 2000 (ver 9) - go to Tools -- Macro -- Visual Basic Editor to view macros / VBA modules Macros can be viewed using the VBA Editor (ALT+F11) then Project Explorer (Ctrl+R) from view menu.  Under Modules not Class Modules select the module that contains your code.  If you don't know you may have to use ALT+F8 then edit from the Excel spreadsheet. A hidden book cannot be edited from the ALT+F8 menu; so here are three alternatives for changing macros in hidden workbook. From the VBE (Alt+F11), get into the Object Browser (F2), select the first item in classes,  <global>, find your macro on the right in members of globals and click on it. Place the name of a macro or function into a worksheet cell and use my GoToSub macro to get to the VBE with the macro or function selected.  (see GoToSub within Build Table of Contents).  For Functions in hidden workbooks you will have to include the workbook name   i.e.  personal.xls!GetFormula Unhide the workbook, Window, unhide then select from the F8 macros menu.  You will want to rehide your personal.xls file afterwards. Common to XL95, XL97, XL98, XL2000 Files found in the XLSTART library will all be opened by Excel at start up, but unless those workbooks are hidden in Excel you will not be able to access your macros and functions transparently as if they were in your open workbook.  personal.xls is the name commonly used for a workbook in the XLSTART library for cross-application usage.  To hide your personal.xls book go to the Window menu and then hide/unhide submenu selecting personal.xls in the list below.  It is not sufficient that another workbook obscure an inactive personal.xls.  The personal.xls book must be hidden. To make your function or subroutine accessible to all of your workbooks, not just the one you added it to. Locate your XLSTART directory, My XLSTART file is on my h: drive, the default would be c: drive, use the Windows Start --> Find files --> Find Files or Folders --> change c: using dropdown to My Computer, then in the top enter XLSTART and make sure subdirectories is checked.  for instance an example might be:  c:\Program Files\Microsoft Office2000\Office\XLSTART\personal.xls but you should locate where your own XLSTART file exists. Create your personal.xls excel book in your XLSTART library. When you bring up another xls file, under Windows menu in Excel, hide your personal.xls file.  Anything in the XLSTART directory will be opened when Excel starts, but the macros in another workbook will only be available if the book is hidden, unless you include the name of the open workbook.  You don't want to put other files there because you do not want excel to open a lot of files. You may unhide your personal.xls file in order to update it, but don't forget to hide it again.  One way around this is to find the name of the macro and library and place that into a cell then use GoToSub to get to the code. One way to access Formulas from another workbook without specifying the workbookname, i.e. personal.xls!getformula(B12), would be to start in the VBE (alt+F11), select any module of the workbook that needs easy access to the formula then under Tools --> References place a check mark on your personal.xls   -- which will make the entire workbook accessible. Creating an addin is another method of making macros available Another method of providing macros and functions transparently to the user (invoke them without prefixing the subroutine with the bookname) is to create an add-in, see below. Summary of ways to invoke a macro « Alt+F8 --> Select Macro --  Run I use a toolbar button   to invoke this dialog.  ( personal.xls!MacroDialogBox) Macros can also be run by several other means including:  toolbar buttons or menus, from a shortcut key, or from a Image on the Worksheet (Command Button). with a macro to Run from the active cell To invoke a User Defined Function Same as you invoke builtin Excel functions, as a formula     =GetFormula(A1)     =personal.xls!GetFormula(A1) User Defined Functions(UDF) will not show on the macro list, but will show up in the Paste Function Wizard near the end in the table on the left, in the "User Defined Functions". Invoking Macros and Functions in another workbook from Code There are two ways to do this [Rob Bovey, misc 2001-03-24].  You can either add a reference to the VBAProject of the workbook containing the macro you want to call and then call it directly, or you can use the Excel Application object's Run method.  In either case the macro must be public (e.g. it can't be preceeded by the Private qualifier and it can't reside in a module that contains the Option Private Module directive). The workbook containing the macro must also be open.  The difference between call and run: RUN is by the application and call call via a variable; whereas, CALL is determined at compile time in this case meaning when the macro starts.  (Run method shown below).  For a simple subroutine with no arguments:     Application.Run "Book1.xls!MacroName" For a subroutine with a String argument and a Long argument:     Application.Run "Book1.xls!MacroName", "Arg1", 2 For a function with arguments that returns a Boolean value:     bReturn = Application.Run("Book1.xls!MacroName", "Arg1", 2) To invoke a macro from same workbook. Macroname Call MacroName("Arg1", 2) Module1.MyProcedure -- there may be more than one MyProcedure [MyProject.xls].[Module1].Main -- from myproject.xls ??? Additional Information on Macros MS KB directions to install a macro included with Q142140 XL: How to Add the Workbook Path to the Footer To insert a Visual Basic module into a workbook in Microsoft Excel versions 5.0 and 7.0, click the Insert menu, point to Macro, and click Module.  In Excel 97 and Excel 2000 for Windows, press ALT+F11 to start the Visual Basic Editor; then, click Module on the Insert menu.  Be careful to select Module and NOT Class Module.  Many subroutines do not have explicit DIM statements so either add them or remove the Explicit code supplied at beginning of module. Hiding macro from Macro Dialog:  Individually mark the SUB as private, or to affect all macros in the module with this statement at top   Option Private Module   You can have your macro call a Function.  A Function called from a macro can do anything that a macro can do unlike functions called from a worksheet.  Functions will not show in the macro dialog box. Installing a macro specific to a single sheet in XL97 and XL2000 The following directions extracted from a posting by Bill Manton pertain to installing a macro specific to a single sheet.  Open the visual basic editor (Alt+F11).  Locate the project explorer (the upper-left pane by default).  It should have VBA Project (Book1).  Underneath should be Microsoft Excel Objects, and under that the sheets in the workbook and a workbook object.  Double-click on the sheet in which you want the functionality, and paste the code into the main code pane. An alternative is to double-click on the sheet tab, select View Code, and paste your sheet specific mactro there. Pasting macro code into a module sheet (XL95), or into VBA Editor (XL97 & XL2000) You want to make both the Excel and the web source windows as wide as possible.  Pasted lines or any other lines turn RED if there is a syntax or other easily recognizable problem in Excel.  An underscore at the end of a line indicates a continuation.  Splitting a line without the continuation character is the most frequent syntax problem and code lines turn red as soon as continuation or other syntax errors are detected.  Actually the continuation character is really two characters, a space followed by an underscore.  Working with VBE code The VBE editor is hard to work with but does provide a useful feature for indenting/undenting code by selecting a block of code then use TAB key or Shift+TAB.  Incompatible with other editors where this would result in deletion of code. In the project library (Excel 97 and up) the default name for the first standard module added to a workbook is module1 which would be created the first time you record a macro in your current workbook.  -- It is not named class1, it is not named sheet1, nor is it named This Workbook. -- information on these can be found on my events page. How to make your function or subroutine accessible to all of your workbooks was previously discussed. Searching VBE code Use the Ctrl+F (Find) and check the "project" choice, you can search one project library (VBE code in one workbook) at a time.  Start from the first module in the project. Visual Basic HELP is available in the VBE (Alt+F1), One such item can be found with the Answer Wizard: Function, then look at the topic "List of Worksheet Functions Available to Visual Basic". Some useful information in getting started with macros can be found in the Excel HELP. F1(Help) --> Answer Wizard --> Visual Basic Look for such topics as "Run a Macro".  You can click on a topic, or proceed through the topics using the ArrownDN key.

  [Top]  [HasFormula]  [Related] 

GetFormat

 The Code for GetFormat

Function GetFormat(cell)
   GetFormat = cell.NumberFormat
End Function

Examples of GetFormat showing normal cell formatting, and GetFormula

=HYPERLINK("www.mvps.org/dmcritchie/excel/excel.htm","My Excel Pages")

One way to simplify creating custom formats is to pick the best match say under number or fractions. Then keeping the same cell selected go to custom format. Also in custom note that as you change the formatting the example will change.

To provide blanks to right of numbers use two underscores.  The first underscore indicates to copy the next character as is, while the second underscore will actually be a blank.  Notice the ?? used in fractions can also be used to align decimal points in numbers like 129.43 and 10.125

Carpentry, and measurements in Feet and Inches

This formula from a posting by Bernie Dietrich 2000-08-04 will round to the nearest 16 inch.

Conversions of Feet and Inches, decimal to 1/16th inch fractional measurements

Feet Carpentry 1.2 1' - 2 3/8" 1.3333 1' - 4" 0.66665 - 8" 1.0056 1' - 0 1/16" 283.065 283' - 0 3/4"		This formula from a posting by Bernie Dietrich 2000-08-04 will round feet and decimal feet to feet, inches with rounded 1/16 inch fractions. =IF(A1>=1,INT(A1)&"' ","") & TEXT(MOD(A1,1)*12,"- 0"&IF(ABS(MOD(A1,1)*12-ROUND(MOD(A1,1)*12,0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(A1,1)*12,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),"")) &""""

Bernie also supplied version from inches with decimal fractions to feet and inches with rounded 1/16th inch fractions.
=INT(A1/12)&"' " & TEXT(MOD(A1,12),"0"&IF(ABS(MOD(A1,12)-ROUND(MOD(A1,12),0))>1/32," 0/"&CHOOSE(ROUND(MOD(MOD(A1,12),1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8, 16),"")) & """"

and a version from inches with decimal fractions to inches only with rounded 1/16th inch fractions.
=TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/32," 0/"&CHOOSE(ROUND(MOD(A1,1)*16,0),16,8,16,4,16,8,16,2,16,8,16,4,16,8,16),"")) &""""

and a formula to take Feet and Inches back to a number of feet (Bernie Deitrick)
=VALUE(LEFT(A1,FIND("'",A1)-1)) + VALUE(MID(A1,FIND("'",A1)+1,FIND("""",A1)-FIND("'",A1)-1))/12

or a formula to take Feet and Inches back to a number of inches (based on Bernie Deitrick's previous solution)
=VALUE(LEFT(A1,FIND("'",A1)-1))*12 + VALUE(MID(A1,FIND("'",A1)+1,FIND("""",A1)-FIND("'",A1)-1))

For those doing carpentry or other things that require trying to get the most pieces cut from sheet stock (plywood) or linear stock (pipe, and 2x4s) see Sheet Layout & Lineal Layout in Wood Online Magazine software (pay for), produces a layout and a cut-sheet list of parts.  identified by Gord Dibben don't know if anyone here has used it.

Additional Worksheet Formulas created by Bernie Deitrick « for
   Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, and 1/128

Custom Formatting

For more information on formatting see Custom Number Formatting in Microsoft Excel [http://support.microsoft.com/support/excel/content/formats/default.asp]

-1	[>=5]General;[Blue]-General;[Red]General
0	[>>=5]General;[Blue]-General;[Red]General
2	[>=5]General;[Blue]-General;[Red]General
4	[>=5]General;[Blue]-General;[Red]General
5	[>>=5]General;[Blue]-General;[Red]General
6	[>=5]General;[Blue]-General;[Red]General

Format for ...   (each format is separated by a semicolon)
Positive Numbers(default); Negative Numbers(default); Zero(All other numbers); Text

  HELP --  ans wiz --  format number

For text simply use the toolbar button that looks like a T (for text) with a color block in front -- there is a pull down beside it.  There is a similar button with a paint bucket behind the block for background; and another button with a pencil behind it for shading.

Comma Separator

A B C D E 1 -1 -1.00 (1.00) ( 1.00) (-1.00) 2 -100 -100.00 (100.00) ( 100.00) (-100.00) 3 -10000 -10000.00 (10,000.00) (10 000.00) (10-000.00) 4 -100000 -100000.00 (100,000.00) (100 000.00) (100-000.00) 5 -10000000 -10000000.00 (10,000,000.00) (10000 000.00) (10000-000.00) 6           7 1 1.00 1.00  1.00  -1.00  8 100 100.00 100.00  100.00  -100.00  9 10000 10000.00 10,000.00  10 000.00  10-000.00  10 100000 100000.00 100,000.00  100 000.00  100-000.00  11 10000000 10000000.00 10,000,000.00  10000 000.00  10000-000.00  12           13 General 0.00 #,##0.00_);[Red](#,##0.00)   14       # ##0.00_);[Red](# ##0.00) 15       #-##0.00_);[Red](#-##0.00)

A B 16 33333.55 General 17 $33,333.55 $#,##0.00 18 $*******33,333.55 $**#,##0.00 19 *******$33,333.55 **$#,##0.00 20 $                    33.59  _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) 21 33.6 General

Fill Characters used in FORMAT ......

Format --> cells --> custom
     A1:      0*.          number value  1
     A1:      @*.          text value:   '1
     B1:      General      text value:  Monday
     B1:      *.dddd       number   value:  01/31/2000      (US)

HasFormula

Function HasFormula(cell)
  HasFormula = cell.HasFormula
End Function

Usage: (showing Formula, Number, Text or Other)
=IF(HasFormula(A1),"F",IF(ISNUMBER(A1),"N",IF(ISTEXT(A1),"T","O")))

A similar VBA function combining features of both of the above can be found on John Walkenbach's site “ Determining the Data Type of a Cell (tip 52)”.  Keep in mind that the returned attribute may not be mutually exclusive to other attributes.

HasFormula can be used directly with Conditional Formatting to indicate cells that have formulas because it returns True or False which is what you need for Conditional Formatting.

BoldSum, Sum the cells having Bold format attribute

Function BoldSum(rngCells As Range) As Double
    'BoldSum is recalculated when any value on sheet changes
    Application.Volatile
    Dim cell As Range
    BoldSum = 0
    On Error Resume Next
    For Each cell In rngCells
       If cell.font.Bold Then BoldSum = BoldSum + cell.Value
    Next cell
End Function

A subroutine providing more information than FontStyle immediately below can be found in FormulaBox providing font information about the first cell in a range plus more general information for the other cells in a selection, and in FontInfo providing font information for another cell.

Function FontStyle(cell)
    'Won't change value until some value on sheet changes
    Application.Volatile
    FontStyle = cell.font.fontstyle
End Function

Select cells with either formulas or constants

How to select Cells with formulas using VBA

In VBA
    cells.SpecialCells(xlCellTypeConstants).Select   'In Excel 97

    cells.SpecialCells(xlConstants).Select   'In Excel 5/95/97

Had you started with a range instead of a single cell you would have selected from the range rather than from the entire sheet.

The following will color font red for all cells with formulas in range, or for entire sheet if only a single cell is selected.

    Sub ColorFormulas()    'xl97 up use xlcelltypeformulas
      Selection.SpecialCells(xlFormulas).Font.ColorIndex = 3
    End Sub

Formulas would be xlFormulas or xlCellTypeFormulas depending on your version.  Some other cell types in D10SE697 -- SpecialCells Method (gone, see information in HELP, below).

SpecialCells Method
       F1 (HELP) --> Index --> SpecialCells Method
Returns a Range object that represents all the cells that match the specified type and value.

Syntax

expression.SpecialCells(Type, Value)

expression  Required.  An expression that returns a Range object.
 
Type  Required Long.  The cells to include.  Can be one of the following XlCellType constants.
 

Constant	Description
xlCellTypeAllFormatConditions	Cells of any format
xlCellTypeAllValidation	Cells having validation criteria
xlCellTypeBlanks	Empty cells
xlCellTypeComments	Cells containing notes
xlCellTypeConstants	Cells containing constants
xlCellTypeFormulas	Cells containing formulas
xlCellTypeLastCell	The last cell in the used range
xlCellTypeSameFormatConditions	Cells having the same format
xlCellTypeSameValidation Cells	having the same validation criteria
xlCellTypeVisible	All visible cells

Prior to XL97 the Constants did not include CellType in their naming.

How to make Cells with formulas stand out -- Manually

Remove all formulas in all worksheets

For presentation purposes.  Since this will purposely destroy all formulas in a workbook ou would only want to run this on a copy of a live workbook.

Sub Remove_All_Formulas_In_All_Sheets  'No_formula_Macro()
  'Goodnight  2000-10-27 programming -- Striping a workbook of formulas
  Dim SH As Worksheet
  For Each SH In Worksheets
    SH.Activate
    Application.ScreenUpdating = False
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
      SkipBlanks:= False, Transpose:=False
  Next
End Sub

Replace Formulas having External Links with their Values

Replacing formulas with external links with their values similar to copy, and paste special, values.  This solution by Tom Ogilvy 2001-07-27 in programming. 

Sub Tester3()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlFormulas)
  If InStr(cell.Formula, "[") Then
    cell.Formula = cell.Value
  End If
Next
End Sub

You will get an error if there are no cells with formulas on the worksheet.

UseFormula

Function UseFormula(cell)
    '-- Usage: Not recommended, see notes
    UseFormula = Application.Evaluate(cell.formula)
End Function

As previously stated I prefer to use GetFormula for documentation purposes because you know you are working with valid formulas that way.  The following attempts to make some limited checks for validity by checking that the formula begins with an equal sign, and will not display if the source cell appears to be blank.

Function UseFormula2(cell)
   'Documented in  http://www.mvps.org/dmcritchie/excel/formula.htm
   ' UseFormula Jul 20, 1998,  UseFormula2 Jun 13, 2000
   'Application.Volatile = True  -- DO NOT DO THIS
    If Trim(cell.Value) = "" Then
       UseFormula2 = ""
       Exit Function
    ElseIf Left(cell.Value, 1) = "=" Then
       UseFormula2 = Application.Evaluate(cell.Formula)
       Exit Function
    Else
       UseFormula2 = "'#bad formula"
    End If
End Function

Display Formula in a MsgBox

First Character of (345) is = CHR(0051) or Hex=x'33'
Arial 12 Bold

G28: 345
  General
G29: =D26+D27
  _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)  
G30: 188121234
  000-00-0000
G31: 7777777777
  [<=9999999]###-####;(###) ###-####

[OK]

Sub FormulaBox()
  'David McRitchie  1998-08-12 1999-08-17
  '  http://www.mvps.org/dmcritchie/excel/excel.htm
  'Place material into MsgBox    [ctrl+n]
  'Will process ranges of one or more columns
  'Application.ScreenUpdating = False
  Dim MsgBoxx As String
  MsgBoxx = "First Character of (" _
    & ActiveCell.Value & ") is " & "=CHR(" _
    & Right("0000" & Asc(ActiveCell.Value), 4) & ") or Hex=x'" _
    & Hex(Asc(ActiveCell.Value)) & "'" & Chr(10) _
    & ActiveCell.Font.Name & " " & ActiveCell.Font.Size _
    & "  " & ActiveCell.Font.FontStyle _
    & Chr(10) & Chr(10)

  For ix = 1 To Selection.Count
      'Selection.Item(ix).NoteText _ ...
      vGetFormulaI = ""
      If VarType(Selection.Item(ix)) = 8 Then
       vGetFormulaI = "'" & Selection.Item(ix).Formula
      Else
       vGetFormulaI = Selection.Item(ix).Formula
      End If
      If Selection.Item(ix).HasArray Then _
        vGetFormulaI = "{" & Selection.Item(ix).Formula & "}"

      'include below if VarType wanted -- don't include for distribution
      '   & " " & VarType(Selection.Item(ix)) _ ..
      MsgBoxx = MsgBoxx _
        & Selection.Item(ix).Address(0, 0) _
        & ": " & vGetFormulaI _
        & Chr(10) & "    " & Selection.Item(ix).NumberFormat & Chr(10)

  Next
  MsgBoxx = MsgBoxx & Chr(10) & "***" 'to verify you've  seen everything
  xyx = MsgBox(MsgBoxx, , "FormulaBox: Formula & Format & Text for " & Selection.Count & " selected cells")
  'Application.ScreenUpdating = True
End Sub

 Show formulas in cell comments

Place comment cells for cells that have a value.

Sub CommentThem()
  Dim cell As Range
  On Error Resume Next
  Selection.ClearComments
  On Error GoTo 0
  For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
     If cell.Formula <> "" Then
        cell.AddComment
        cell.Comment.Visible = False
        On Error Resume Next  'fails on invalid formula
        cell.Comment.Text Text:=cell.Address(0, 0) & _
           "  value:    " & cell.Value & Chr(10) & _
           "  format:   " & cell.NumberFormat & Chr(10) & _
           "  Formula:  " & cell.Formula
        On Error GoTo 0
     End If
  Next cell
End Sub

 Display Sheet Statistics

  Address:      $A$1:$G$13
  Last Row:     13
  Last Column:  7
  Total Cells:  91
    Formulas:   4
    Blanks:     77
    Constants:  10
  Errors:       0
  Logical:      0
  Text:         9
  Numbers:      1
[OK]

Sub SheetStats()
'Tom Ogilvy  1999-02-24 Tues   excel programming
'Note in XL97 and above xlcelltypeconstants and xlcelltypeformula
Set rng1 = ActiveSheet.UsedRange
On Error Resume Next
numConstants = rng1.SpecialCells(xlConstants).Count
If Err <> 0 Then numConstants = 0: Err = 0
numerrors = rng1.SpecialCells(xlConstants, xlErrors).Count
If Err <> 0 Then numerrors = 0: Err = 0
numLogical = rng1.SpecialCells(xlConstants, xlLogical).Count
If Err <> 0 Then numLogical = 0: Err = 0
numText = rng1.SpecialCells(xlConstants, xlTextValues).Count
If Err <> 0 Then numText = 0: Err = 0
numNumbers = rng1.SpecialCells(xlConstants, xlNumbers).Count
If Err <> 0 Then numNumbers = 0: Err = 0
numformulas = rng1.SpecialCells(xlFormulas).Count
If Err <> 0 Then numformulas = 0: Err = 0
numBlanks = rng1.SpecialCells(xlBlanks).Count
If Err <> 0 Then numBlanks = 0: Err = 0

Msg = "Address:     " & Chr(9) & rng1.Address & Chr(10) & _
      "Last Row:    " & Chr(9) & rng1.Rows(rng1.Rows.Count).Row & Chr(10) & _
      "Last Column: " & Chr(9) & rng1.Columns(rng1.Columns.Count).Column & Chr(10) & _
      "Total Cells: " & Chr(9) & rng1.Count & Chr(10) & _
      "   Formulas: " & Chr(9) & numformulas & Chr(10) & _
      "   Blanks:   " & Chr(9) & numBlanks & Chr(10) & _
      "   Constants:" & Chr(9) & numConstants & Chr(10)

Mg2 = "Errors:      " & Chr(9) & numerrors & Chr(10) & _
      "Logical:     " & Chr(9) & numLogical & Chr(10) & _
      "Text:        " & Chr(9) & numText & Chr(10) & _
      "Numbers:     " & Chr(9) & numNumbers

 title1 = "SheetStats for " & Application.ActiveSheet.Name & _
      " in " & Application.ActiveWorkbook.FullName

 iANS = MsgBox(Msg & Mg2, , title1)

End Sub

   MsgBox _
      "Address:     " & Chr(9) & _
          Selection.Address & Chr(10) & _
      "First Cell:    " & Chr(9) & _
          Selection(1).Address & Chr(10) & _
      "Active Cell: " & Chr(9) & _
          ActiveCell.Address & Chr(10) & _
      "Last Cell:    " & Chr(9) & _
          Selection(Selection.Cells.Count).Address & Chr(10)

    'This really doesn't do anything, it might change activecell.
    'Just demos getting and selecting a range
    Dim strt As Range, eend As Range
    Set strt = Selection.Cells(1)
    Set eend = Selection(Selection.Cells.Count)
    Range(strt, eend).Select

FontInfo -- a Function to get font information

The default font is identified with Tools, Options, General, Standard Font:

Function fontinfo(cell As Range) As String
  fontinfo = cell.FONT.Name & " -- " & cell.FONT.Size
  If Left(cell.FONT.FontStyle, 7) = "Regular" Then
     fontinfo = Trim(fontinfo & Mid(cell.FONT.FontStyle, 8, 100))
  Else
     fontinfo = Trim(fontinfo & " " & cell.FONT.FontStyle)
  End If
End Function

An example of use, depending on what font you actually used in your table the formula shown might be substituted by something such as:
    Arial -- 12 Bold
    Webdings -- 12
    Wingdings 2 -- 12

A table such as the following can be created using

  C2: =CHAR(32)      D2:  =CHAR(32+1)
  C3: =CHAR(32+16)   D3:  =CHAR(32+17)

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

1

=FontInfo(C3)

2

32

!

"

#

$

%

&

'

(

)

*

+

,

-

.

/

3

48

0

1

2

3

4

5

6

7

8

9

:

;

<

=

>

?

4

64

@

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

5

80

P

Q

R

S

T

U

V

W

X

Y

Z

[

\

]

^

_

6

96

`

a

b

c

d

e

f

g

h

i

j

k

l

m

n

o

7

112

p

q

r

s

t

u

v

w

x

y

z

{

|

}

~



8

128

€

�

‚

ƒ

„

…

†

‡

ˆ

‰

Š

‹

Œ

�

Ž

�

9

144

�

‘

’

“

”

•

–

—

˜

™

š

›

œ

�

ž

Ÿ

10

160

¡

¢

£

¤

¥

¦

§

¨

©

ª

«

¬

­

®

¯

11

176

°

±

²

³

´

µ

¶

·

¸

¹

º

»

¼

½

¾

¿

12

192

À

Á

Â

Ã

Ä

Å

Æ

Ç

È

É

Ê

Ë

Ì

Í

Î

Ï

13

208

Ð

Ñ

Ò

Ó

Ô

Õ

Ö

×

Ø

Ù

Ú

Û

Ü

Ý

Þ

ß

14

224

à

á

â

ã

ä

å

æ

ç

è

é

ê

ë

ì

í

î

ï

15

240

ð

ñ

ò

ó

ô

õ

ö

÷

ø

ù

ú

û

ü

ý

þ

See my Symbols page.

References in this document

BoldSum, Sum the cells having Bold format attribute
FontStyle, Display Font Style used in referenced cell
FormulaBox font information for first cell, and general information for all cells in a selection

Fonts, Getting a List of Installed Fonts (tip 79) -- John Walkenbach (tip 79)

A simple technique (for Excel 97 or later) to retrieve a list of installed font names, and an alternative to an API function on Stephen Bullen's site for those who don't have XL97.

The Font Thing -- Sue Fisher [update notes], thanks to Jim Rech (2000-03-15) for telling us.

Provides information on installed and uninstalled fonts, font samples for your text, including use of two fonts in samples.

Some Additional Notes on VBA Usage

Worksheet Functions used in VBA

WorksheetFunction Property Example

This example displays the result of applying the Min worksheet function to the range A1:A10.

Set myRange = Worksheets("Sheet1").Range("A1:C10")
answer = Application.WorksheetFunction.Min(myRange)
MsgBox answer

Sub Dmax()
   MsgBox Application.WorksheetFunction.Max(Range("d1:d18"))
End Sub

Sub Dmax2()

   MsgBox Application.WorksheetFunction.Max(Worksheets("## 33 ##").Range("d1:d18"))
End Sub

Assigning a Formula

Something else to try

,,,1,16
,,,2,32
,,,4,64
,,,8,128
Select cell C1 before invoking the following:

Sub Macro7()
    Range(cells(1, 1), cells(4, 1)) = 1
    [a5] = "=sum(a1:a4)"

    iValue = "d1:e4"
    iValue = InputBox("Supply range, example: d1:e4")
    If iValue = "" Then iValue = "d1:e4"
    ActiveCell.formula = "=SUM(" & iValue & ")"

    cells(5, 2).formula = "=SUM(" & iValue & ")"
    [e5] = "=SUM(" & iValue & ")"
End Sub

Results:
a1:a4      all contain 1
a5:  4
b5:  255
c1: 255    (and is still the active cell)
e5: 255

Converting Formula to/from absolute

FormulaText using R1C1 format

Subj:    Re:  formula shown in cell
Date:   98-01-17 00:46:57 EST
From:   dana2@msn.com (Dana De)
To:   DMcRitchie@msn.com (DMcRitchie)

I use a slightly different function that adjusts for the reference style in
use.   Just another idea. 

Function FormulaText(cell_ref)
    'Allow formula to be updated if changes are made on the sheet
    Application.Volatile
   'Test for reference style in use
    If Application.ReferenceStyle = xlA1 Then
        'Set the return value of the function to the A1 style formula
        FormulaText = cell_ref.Formula
    Else '  xlR1C1 --Set the return value of the function to the R1C1 style formula
        FormulaText = cell_ref.FormulaR1C1
    End If
End Function

Status Bar

status message like	(RClick here)	SUM=3.44	EXT	CAPS	NUM	SCRL	END	FIX
Ready Calculating % Found A22 Circular Status from Excel or Macro	Rclick on status bar choices   Average   Count   Count Num   Max / Min / Sum		ADD
Extended mode (F8).  Click on a "start" cell (Anchor), press F8, now click on another cell and this will highlight *all* cells between the 'anchor' cell and the one selected.  To remove indication hit F8 once more.  Use of Shift+F8 shows ADD and allows you to keep adding more ranges until turn off with Shift+F8 (test with MarkSepAreas). Caps Lock, Num Lock, Scroll Lock, and End are keys on the keyboard.  For more information on use of End in shortcuts see  http://www.mvps.org/dmcritchie/excel/shortx2k.htm Scroll Lock Key.  Press this and the arrow keys will scroll the worksheet, rather than moving the selection down the page. FIX is an option to enter with a fixed number of decimals if no decimal point is keyed in.  This is an option that would mess most people up.  Tools --> Options --> Edit --> Fixed decimals (number) It is useful for accounting types who like to key in 500 instead of 5.00 Count  [WS Functions COUNTA + COUNTBLANK],  Count Num [WS Function COUNT]

Reporting progress on Status Bar

  Application.StatusBar = 5*INT(20*n/ncnt)&"%"
  Application.StatusBar = False      'return control to Excel

    CommandBars("AutoCalculate").Enabled = True

Order values using LARGE or SMALL

If the 2nd and 3rd largest is the same and you want to see both results you can make numbers a little different so that you can see both in a reply posting by Denny Campbell 2000/03/01.

I recommend the GetFormula() described at the beginning of this web page; however, the GetFormulaInfo that came with Excel presents an interesting solution and a lot more of an example in the way of VBA code.  I prefer GetFormula() because it can show the coding of formulas on the same page as they are used.

GetFormulaInfo

1. Make a copy of the worksheet:  Worksheet Functions and name it Samp1.  Normally you would be able to use the original, but the sheet name is too long to prefix it with "Formulas for ".
2. Using the copy select A1:F105
3. Invoke macro
   Tools --> Macro --> GetFormulaInfo
4. Look at the created sheet "Formulas for Samp1".

Segments of Original Spreadsheet: Worksheet Functions

Worksheet Functions

Formulas for Worksheet Functions ( a portion of the created spreadsheet)

Excel Add-In .XLA

Well I've been creating Macros subroutines and Functions and placing them into my personal.xls file, not realizing that there was more to it then that.  If a function didn't work there I made another copy back into my test file, never realizing that I had two copies of everything until I deliberately deleted copies in my test file (1998/08/09).  The functions no longer worked.  A quick check on what is now Google Usenet Archives to find out if I had lost some connection between the two turned up that I needed an add-in.  So thanks to Chip Pearson's posting (1998/04/23), I have created my first add-in. (see option 2 below in his posting)

1) Store the function in your Personal.XLS worksheet (or some other worksheet) and place this in the XLSTART directory.  Since it will be opened whenever you start Excel, it will be available at all times.  Even in this case, you'll have to precede (or "qualify") the function name with the workbook name: =Personal.XLS!MyFunction()

2) Store the function in your Personal.XLS worksheet (or some other worksheet) and save this worksheet as an Excel Add-In (Save As and change File Type to "Excel Add-In" at the very bottom of the list).  Then load in this add in, from the Tools->Add Ins menu.

According to Norman Harker the better library to place an addin is C:\Program Files\Microsoft Office\Office\Library so that you don't have to go searching for where it is located.  [ref.]

Add-in Links, links for addin, digital signature

Found the following KB articles and it looks like everything is there.

• Q151490 XL: How to Create a Global User-Defined Function
• Q141288 XL: How to Use a Custom Function in Another Workbook
• Q108425 XL: How to Create Add-in Application Name and Description
• Q156942 XL97: How to Create an Add-in File in Microsoft Excel 97

Addin workbooks Add-in topics on Ole P's site: What is an Addin, How to install one

Tip 45, Excel 97: Creating Add-Ins John Walkenbach

How to create addins , reply posting of links by Tom Ogilvy, 2000-05-30
Another tip from Tom Ogilvy on add-ins: Creating an Add-In

Digital Signature, Create your own digital certificate

Related Information

• See my BuildTOC page for information on organizing and documenting location of sheets, macros, functions, toolbars, and other things related to Excel.  Some things are found in the Related Area at bottom.  Also have articles on Fill-Handles and Replication, and on Cell Comments     At the bottom of this page you will see links to my home page and to my index.
• Placing Formulas into Cell Comments
• See Add-in Links, links for addin, digital signature on this page.

Related information in MS KB

• Q163678 -- XL Err Msg: "Too Many Different Cell Formats"
• Q173707 -- OFF97: How to Run Sample Code from Knowledge Base Articles
• XL: Macro to Search for Cell Formats: Font, Border, and Interior, printing cell characteristics
• Q289245 -- XL2002: How to Use the Excel Formula Evaluator (new feature with Excel 2002)

Related Postings

• AN=571953226 -- Coloring characters within a text string in VBA, see replies by Chip Pearson and Tom Ogilvy in this thread 12Jan2000.

Related information on other sites

• Creating a List of Formulas (Tip 37) can be found on John Walkenbach's site (Tip 37).  It uses less coding but is identical in function, to the GetFormulaInfo example from Microsoft.
• Determining the Data Type of a Cell (Tip 62).  Excel provides a number of built-in functions that can help. These include ISTEXT, ISLOGICAL, and ISERROR. In addition, VBA includes functions such as IsEmpty, IsDate, and IsNumeric. Tip 62, John Walkenbach
• Count, number of formulas, number of conditional formulas  Walkenbach/Hager
• COUNT, Counting Techniques (Tip 52)    range using: DCOUNT, COUNT, COUNTA, COUNTBLANK, COUNTIF
• Counting Years,Months and Days (Tip 55)    - John Walkenbach - also gives an idea of what an array formula  is and how to implement.
• Multifunctional Functions (Tip 43)  i.e. -- count of cells that contain a number between 3 and 10
     =COUNTIF(data,">=3")-COUNTIF(data,">10")
• Format, "Select by Format" is one add-in in John Walkenbach's (pay for) PUP 2000 package that lets you select all cells on a spreadsheet with a given format.
• Creating a Worksheet (Tip 28) Map: Creates a map for a worksheet.  The map color-codes each cell by type (text, number, or formula) -- John Walkenbach. 
• Custom Help Files some information on finding packages to create HELP files. (J.Walkenbach 2000-06-23)
• Formula List will list the formulas used in a specified range.  Part of the Simtools.xla and Formlst.xla add-ins available from Northwestern Univ for heavy statistical modeling, Monto Carlo simulation and Tornado simulation -- I'm impressed.  In any case it reinforces the fact that you need good documentation to get much accomplished -- and that part I do understand.
• Conditional Formatting will override the text colors that can be produced for numeric values by normal cell formatting.
• Conditional Formatting, David Hager's "Excel Experts E-Letter Archives" letters 3, 8, and 14.  Letter #8 includes a Narrative on Conditional Formatting in a Word Document.
  (some techniques for those still on XL95 can be found at John Walkenbach's tip 25).
• Eliminating Specified Formulas is based on entering the formula to be removed, defaults to active cell.  Solution provided by Nick Hodge see complete thread in Google Usnet archives.  Nick's code is interesting in that it converts the formula to R1C1 form so that it can find equivalent formulas to remove based on the active cell.  Another feature is that values are copied and pasted at the end to a new sheet, not by creating a new sheet and then changing values.  (see code).  A more aggressive elimination of formulas can be done by selecting the area, COPY (Ctrl+C), EDIT, Paste Special, Values. 
• Deleting Unused Formats code from Leo Heuser can be found in David Hager's EEE007, about 1/2 way down the page.  Look for DeleteUnusedCustomNumberFormats   [also see updated version 2001-05-06]
• Modifications to the Function Wizard's Categories, Laurent Longre, also see <:eWu2zpoOAHA.277@cppssbbsa04>
• Number Formatting, normal cell formatting should not be overlooked, it is easy to get carried away with Conditional Formatting that one loses track of the obvious.  Also see format, "About Worksheet Formatting" in HELP.

Fonts

• Fonts, Getting a List of Installed Fonts (Tip 79) -- John Walkenbach
• The Font Thing -- Sue Fisher [update notes], thanks to Jim Rech (2000-03-15) for telling us.  Provides information on installed and uninstalled fonts, font samples for your text, including use of two fonts in samples.

Modules

• Code Module And Code Names, Four types of Modules:  Standard Code Modules, Workbook And Sheet Code Modules, User Forms, and Class Modules.  Code will not run if placed in the type of module.  Chip Pearson.
• Class Modules, Links to Chapters from MS publications and papersd

Worksheet Functions

• Function names can be seen in the Paste Function Wizard (Shift+F3).  User Defined Functions are near the bottom of the left side window.  
• The same categories as seen in the Paste Function Wizard can be found in the Creating Formulas and Auditing Workbooks section of Microsoft Excel 2000 Help can be seen at http://support.microsoft.com/support/Office/InProdHlp/contents/excel/tocxlmain9hhc15.asp
• The Table of Contents you see in Help can also be found online at
  Microsoft Excel 2000 Help
  http://support.microsoft.com/support/Office/InProdHlp/contents/Excel/tocxldefault.asp
• A more compact list (overview) can be found as
  Origin of Excel Worksheet Function Names -- http://www2.odn.ne.jp/excel/functioninenglish.html« by Janma,
  the rest of his Excel site, "Excel Skill Training Hall", is in Japanese.
• A reference to Excel's builtin functions that you should definitely download and maintain on your computer
  Excel Function Dictionary -- http://homepage.ntlworld.com/noneley « by Peter Noneley,
  Alternative download site http://homepage.ntlworld.com/peter.noneley)
  workbook with 157+ sheets, each with an explanation and example of most of Excel's builtin functions.  /\

This page was introduced on opening January 1, 1998. 

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com.