Hide Formula in Worksheet Without Protecting Worksheet

How to hide a formula in worksheet without protecting it with a password using VBA. We’ll use the collection object Dictionary to achieve our goal. One of the best articles on the Dictionary collection object is available at this website.

Watch the video below:

Watch this video on YouTube.

Here are the main points for solving our problem with reference to the Dictionary variable taken from the link mentioned above:

  • A Dictionary in VBA is a collectionobject: you can store all kinds of data in it: numbers, texts, dates, arrays, ranges, variables and objects.
    ‘Every item in a Dictionary gets its own unique key.
  • With that key you can get direct access to the item (reading/writing/adapting).
    ‘The way in which the Dictionary stores items is comparable to the Collection object.
  • The Dictionary has some properties/functions/methods like .keys, .items and .removeall, that can be advantageous compared to the use of a collection.
    ‘Generally speaking you can use a Dictionary to store/group data that have a common property (key).
  • Scattered data can be integrated easily.
  • You can group all kinds of data in a Dictionary to get easy and quick access to them since they are temporarily stored in memory.
    ‘Instead of manipulating data in an Excel-worksheet on the hard disk, you can do that in memory.
  • No screenrefreshing, no autocalculation, no background backup is necessary, so it will speed up your code considerably.
  • A Dictionary can only contain unique keys. That’s why you can use the property .keys to create a list of unique strings, numbers or dates.
  • The Dictionary isn’t part of the standard VBA library.
  • It ‘s an element in the Microsoft Scripting Runtime library.

Here’s the complete VBA code:

Dim myDic As New Dictionary
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range
Set myRng = Range(“D2:D6”)

If myDic.Count <> myRng.Count Then
For Each myCell In myRng
‘myCell.Address is the key, myCell.FormulaR1C1 is the content
myDic.Add myCell.Address, myCell.FormulaR1C1
Next
End If

If (Target.Count = 1) And (Not Application.Intersect(myRng, Target) Is Nothing) And (Target.HasFormula) Then
With Target
.Value = .Value
End With
End If
‘ MsgBox myDic.Count
myDic.RemoveAll
End Sub

Further Reading:

Dictionaries

Sample file for download and practice:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Hide Formula in Worksheet Without Protecting Worksheet”

  1. Please add me to your email list for updating me with your VBA tricks & tips, etc
    I am already a subscriber of your Yt channel.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.