How to Hide Formula in Excel Using VBA (4 Methods)

Sometimes we want to hide formulas for certain results to make any large worksheet less crowded. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to hide the formula in Excel using VBA macro.


Download Workbook

You can download the free practice Excel workbook from here.


4 Methods of Using VBA to Hide Formula in Excel

Look at the following gif. Here all the cells in the Result table hold formulas behind the results.

Dataset of how to hide formula in excel using vba

We will see how to hide the formula from a specified range, from selections, from unprotected sheets and hide the formula while preventing cells from deleting in Excel using VBA macro.


1. Embed VBA to Hide Formula of a Range in Excel

If you want to hide the formula of a specified range in Excel with VBA code, then follow this section. But you must protect the sheet first if you want this code to execute successfully.

Steps to Protect a Sheet:

  • First, go to the tab Review.
  • Then, click Protect Sheet from the Protect group in the ribbon.

  • A Protect Sheet pop-up box will come up. Provide any password to open the sheet in the Password to unprotect sheet box.
  • Then, click OK.

  • Reenter the password to proceed again in the Confirm Password popup window.
  • After that, click OK again.

As a result, now you have a password-protected Excel worksheet.

Now we will learn how to hide the formula for a range in that sheet with the VBA code.

Steps:

  • In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • Next, in the pop-up code window, from the menu bar, click Insert -> Module.

  • Then, copy the following code and paste it into the code window.
Sub HideFormulaInProtectedSheet()
    Worksheets("Hide Formula").Range("G11:J14").FormulaHidden = True
End Sub

Your code is now ready to run.

How to hide formula for a range in excel using vba

  • Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the gif below to see the result.

Result of how to hide formula for a range in excel using vba

Finally, all the formulas behind the range G11:J14 are now hidden.

VBA Code Explanation

Worksheets("Hide Formula").Range("G11:J14").FormulaHidden = True

This piece of code will hide formulas from the range G11:J14 of the “Hide Formula” worksheet in Excel. You can insert the range and the sheet name according to your workbook.

Read More: How to Hide Formulas and Display Values in Excel (2 Ways)


2. Apply VBA Macro to Hide Formula from Selection in Excel

In the previous section, you hardcoded the range inside the macro. You can also hide formulas from a range selected by you. Let’s see how to do that with VBA in Excel.

Steps:

  • First, select the cell that you want to hide the formula.
  • Then shown as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • Then, in the code window, copy the following code and paste it.
Sub HiddenFormula()
    Selection.Locked = True
    Selection.FormulaHidden = True
    ActiveSheet.Protect Password:="ExcelDemy"
End Sub

Your code is now ready to run.

How to hide formula from selection in excel using vba

  • Later, Run the macro.
  • If you didn’t select the cell before going to the Visual Basic Editor, then you can select the cell this time as well. Just go back to the worksheet of interest and select the cell.
  • Then, go to the Developer tab and click Macros.

Run the macro for how to hide formula from selection in excel using vba

  • A Macro pop-up window will appear. Select the macro name from the Macro name list and press Run.

Result of how to hide formula from selection in excel using vba

As a result, the formula behind the selected cell (Cell G11 in our case) is now hidden.

You can execute this code to hide formulas for multiple cells as well.

  • Just select multiple cells instead of selecting one single cell and Run the macro as we showed you above.

How to hide formula from multiple selection in excel using vba

  • And the code will work just fine in hiding the formulas behind all those cells.

Result of How to hide formula from multiple selection in excel using vba

As you can see from the above gif, formulas from multiple cells, from G11 to J14, are now hidden after running the macro.

If you want to show the formulas again, then you must unprotect the sheet. How you can unprotect a sheet is shown below.

Steps to Unprotect a Sheet:

  • First, go to the tab Review.
  • Then, click Unprotect Sheet from the Protect group in the ribbon.
  • An Unprotect Sheet pop-up box will come up. Provide the password to open the sheet in the Password If you remember from the code, we stored “ExcelDemy” as our password. Insert that password to unprotect the sheet for the workbook provided with this article.
  • Then, click OK.

As a result, your sheet will become unprotected, and all the formulas will be visible again.

VBA Code Explanation

Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect Password:="ExcelDemy"

This piece of code refers that the selected part will be locked, their formula will be hidden and be protected with the password “ExcelDemy”.


3. Implement Macro to Conceal the Formula in Unprotected Sheet

Previous VBA codes require the worksheet to be protected before executing the macro to run the code successfully. But in this section, you will get to learn how to hide the formula even in the unprotected sheet.

The steps to achieve that are shown below.

Steps:

  • First, right-click on the sheet name and select View Code from the option list that appeared.

  • Then, copy the following code and paste it into the code window of the specified worksheet.
Dim iDictionary As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iCell As Range
Dim iRange As Range
Set iRange = Range("G11:J14")
If iDictionary.Count <> iRange.Count Then
    For Each iCell In iRange
        iDictionary.Add iCell.Address, iCell.FormulaR1C1
    Next
End If
If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then
    With Target
        .Value = .Value
    End With
Else
    For Each iCell In iRange
        iCell.formula = iDictionary.Item(iCell.Address)
    Next
End If
End Sub

How to hide formula in unprotected sheet in excel using vba

  • Don’t run this code, save it.
  • Next, click on the Tools -> References… from the code window tab.

  • From the appeared References – VBAProject pop-up window, check Microsoft Scripting Runtime.
  • Then, click OK.

  • After that, go back to the worksheet of interest and click on the cells to check whether the formulas behind them really hidden now or not.

Result of how to hide formula in unprotected sheet in excel using vba

As you can see from the above gif, if you click on the cells that hold formulas behind the results, you will see that the formulas are hidden now.

VBA Code Explanation

Dim iCell As Range
Dim iRange As Range

Defining the variables.

Set iRange = Range("G11:J14")

Setting the range to hide the formula.

If iDictionary.Count <> iRange.Count Then
    For Each iCell In iRange
        iDictionary.Add iCell.Address, iCell.FormulaR1C1
    Next
End If

This piece of code refers that if the dictionary count is not equal to the range then for each cell of the range, the R1C1 style applies to the formulas.

If (Target.Count = 1) And (Not Application.Intersect(iRange, Target) Is Nothing) And (Target.HasFormula) Then
    With Target
        .Value = .Value
    End With

This piece of code is to check whether the range holds the formula or not. If the intersection of the range doesn’t fulfil the above condition, then it replaces the value in the cell with the value that it has.

Else
    For Each iCell In iRange
        iCell.formula = iDictionary.Item(iCell.Address)
    Next
End If

Otherwise, pass the cell address as the argument of each item.


4. Embed VBA to Hide Formula and Prevent the Cells from Deleting

It is a very convenient way to manage a large dataset by hiding the formula. But what if the cells that hold formulas get deleted by some mistake. Then you will lose both the data and the formula behind it.

To hide the formula and prevent the cells from further deletion, continue reading this article.

Steps:

  • As shown before, go to the code window from the View Code option appeared by right-clicking the worksheet of interest.
  • Then, copy the following code and paste it into the code window of the specified worksheet.
Private Sub Workbook_SheetSelectionChange(ByVal iObj As Object, ByVal Target As Range)
Dim iRange As Range
On Error Resume Next
   iObj.Unprotect Password:="ExcelDemy"
   With Selection
   .Locked = False
   .FormulaHidden = False
End With
If Target.Cells.Count = 1 Then
If Target.HasFormula Then
   With Target
   .Locked = True
   .FormulaHidden = True
End With
   iObj.Protect Password:="ExcelDemy", UserInterFaceOnly:=True
End If
   ElseIf Target.Cells.Count > 1 Then
   Set iRange = Selection.SpecialCells(xlCellTypeFormulas)
   If Not iRange Is Nothing Then
   With Selection.SpecialCells(xlCellTypeFormulas)
   .Locked = True
   .FormulaHidden = True
End With
   iObj.Protect Password:="ExcelDemy", UserInterFaceOnly:=True
End If
End If
   On Error GoTo 0
End Sub

How to hide formula and prevent deletion in excel using vba

  • Don’t run this code, save it.
  • Now, go back to the worksheet of interest and if you click on any cell then the cell will show you no formula, meaning the formula is hidden.

Result of how to hide formula and prevent deletion in excel using vba

Not only that, if you right-click on any cell then you will notice the Delete option is unavailable, meaning you can’t delete the cell now.

VBA Code Explanation

Dim iRange As Range

Defining the variable.

On Error Resume Next

If any error occurs, go to the next line.

   iObj.Unprotect Password:="ExcelDemy"
   With Selection
   .Locked = False
   .FormulaHidden = False
End With

Setting the password while unprotecting the sheet. Also, making the cells unlocked and the formula visible.

If Target.Cells.Count = 1 Then
If Target.HasFormula Then
   With Target
   .Locked = True
   .FormulaHidden = True
End With

This piece of code refers that if the selected range has formulas, then lock the range and hide the formulas.

iObj.Protect Password:="ExcelDemy", UserInterFaceOnly:=True
End If

Protect them with the password “ExcelDemy”.

ElseIf Target.Cells.Count > 1 Then
   Set iRange = Selection.SpecialCells(xlCellTypeFormulas)

Otherwise, include the cells that hold formulas as the range.

If Not iRange Is Nothing Then
   With Selection.SpecialCells(xlCellTypeFormulas)
   .Locked = True
   .FormulaHidden = True
End With

To lock the newly included cells and make the formula hidden.

Read More: How to Hide Formulas in Excel until Data Is Entered (2 Methods)


Conclusion

To conclude, this article showed you 4 effective methods for how to hide the formula in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo