Excel VBA Hash of All Formulas and VBA

sypder, I have never worked with Hash but when it comes to VBA scripts, please consider the below scripts. They both are same but will they not generate different Hash?

Sub Sample
msgbox "a"
End Sub

Select allOpen in new window

Sub Sample
'~~> This is a message box
msgbox "a"
End Sub

Select allOpen in new window

Sid

Yes, they are the same but would generate different hashes. This would actually be fine for my purpose. I have a whole set of Excel sheets which are suppose to be identical. I am just trying to make sure everyone is running the same "version" and without their own custom changes.

Why not version control it?

Sid

sypder,

You are asking to do something that is very difficult and provides little benefit.

Consider all the elements of a workbook that can vary such as formatting, validation rules, column widths, etc.

Use the file save date time. Or add a version number to the document properties.

If you are truly just interested in formulas and VBA code, do a simple comparison of the formulas and VBA code between the two workbooks.

Kevin

Further to what I suggested above, I started having more thoughts around it.

Even if you version control it or take the date created or any such parameter there is no guarantee that the file will remain unchanged. The only way I can think of (Please correct me If I am wrong) is to use

"Version Control" PLUS "Protect your formulas and the VB editor"

That ways, the user will not be able to change the formulas nor will they be able to Add/Delete/Modify any code.

Sid

Dave: You are changed your Eeple again. lolzzz... But at least the 'Gender' is correct ;)

With Respect:

>>Locking it down is possinly an option but does signal - maybe correctly - a lack of trust.

Yes that is one way to look at it. However 'protection' can also be looked as an option to prevent deletion of formulas/code by mistake and similar scenarios.

>>in some cases some formulae may need end-user modifying to cope with certain circumstances

That would defeat OP's main question then.

Sid

>Locking it down is possinly an option but does signal - maybe correctly - a lack of trust.

Who is your user group - grandma?

I lock everything down - but without passwords. Never use passwords. It's a UI attribute.

Kevin

Also just to be expand on 'Protection', I didn't mean protect the entire sheet but just the main formula cells while keeping the rest of the cells unlocked so that the users can input their own formulas if they want to.

Sid

Whoa! You type very fast Kevin... Your post was not there!!! :)

Sid

I'm in Seattle. My bits have about 3,000 fewer miles to travel to San Luis Obispo, CA than from India.

>That would defeat OP's main question then.

Sure. But I was giving an example from my experience as to why I thought it was a reasonable question

>I lock everything down - but without passwords
Which of course stops inadvertment mistakes, but not intended changes

In this case it's argubale that the sheets should be locked down with passwords to prevent any changes. Which may or may not be possible/optimal depending on the OP situation

until proven otherwise it is a reasonable question :)

Cheers

Dave

It sounds like the concept is doable, but that there is not "quick" script. Thanks for all the thoughts.

I understand all of the comments about version control, but this spreadsheet is dispersed very widely across many different companies, so I cannot expect anyone to follow any one procedure. There are also a few reasons why we cannot password protect the cells. Thanks again.

brettdj, this is very similar to what we have. I would love to just have a spreadsheet that I could use to make sure that everyone has the current formulas, didn't make any changes (unintentional or otherwise), and that there are no numbers within the formula that got tweaked. I will try to code something on my own, just trying to save time if it already existed...