Named ranges are really nice parts of Excel – you just give a name to the range and you start referring it by it. No more Z56 or AM31, the ranges start having names the way you like them.
So, one day you make a special dashboard or a model in Excel, in which you protect your workbook with a password. The named ranges are in different settings worksheets, as a simple way of building a MVC architecture. Of course, the worksheets are very hidden and you already feel quite ok. Then, you go to the Names Manager and you see the following picture:
Yup! The named ranges are visible. You “SpecialPassword” can be seen by anyone. What should you do? Option 1 – use a database and proper hashing for storing of sensitive information. Option 2 – hide the names, from the Name-Manager, thus the worksheet would still refer them, but they will be not visible:
This is the way to achieve it with VBA:
|
1 2 3 4 5 6 7 8 9 10 |
Sub UnhideAllNames() Dim tempName As Name For Each tempName In Names 'Debug.Print tempName .Name tempName .Visible = False Next tempName End Sub |
Cheers! 🙂