Selva’s Site

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad1
Today someone asked me how to display a formula in a cell. What a silly question? It’s Easy, just enter an apostrophe (‘) in front of the formula and one can see the formula. Oh No! But if you don’t want to disturb the cell where the formula resides? Oh! So you want to show the result of the formula as well… Yes! at the same time a user should be able to see the formula as it is in a different cell. Isn’t it simple? Enter into the cell, copy the formula starting “=” and till the end and paste it in a different cell and add apostrophe in front of it. But what if, you have thousands of formula to show? Now, there is something to work on…. Let me share what i tried sometime back and i think this should work!

Open a new Excel Workbook, press alt+F11, if you dont see a small window with the names of the sheets in a workbook, click ctrl+R… Now you will be able to see a window with all the workbooks and its contents like sheets, userforms, & codemodules. Right click the new workbook project and choose insert –> module… Now double click the module that has just been inserted… Do you see code window? If you are not, press F7 key and you will see the code module. Now in this module, copy the following code and paste it.

Option Explicit
Public Function ShowFormula(ByRef iCell As Range)
  If iCell.HasFormula = True Then
    ShowFormula = iCell.Formula
  Else
      ShowFormula = ""
  End If
End Function

Now, you are ready to show the user the formula as it is wherever you want. just type "=showformula(" and then the reference to a cell to display the formula of that particular cell.

eg., =showformula(e6)

To download an example workbook, CLICK HERE