Code not running on new computer

[John Wol]

I have a small .xlsm file which contains two simple macros and a VB module designed to return a 10-character hash from a character string using "=BASE64SHA1(A1)". I'm not skilled in VB so I simply found the code from a web site. The only mod I made was to set the length of the hash to 10 characters.

I've been using this file regularly for about a year on each of three computers without problems all using Office 365.

I have recently bought a new computer, installed Office 365 and tried to use the file. "=BASE64SHA1(A1)" only returns "#VALUE". The other two macros in the file work correctly. The same file continues to work correctly on the other computers.

I have compared the Excel options on the new computer with those on one of my other machines they all seem identical but I might have missed something.

I would be grateful for any help you might be able to offer.

 

[mole999]

did you just copy the file or recreate it

 

[John Wol]

It's the same file downloaded from Onedrive in each case.

 

[MARK858]

Can you post the code that you are using please.

 

[John Wol]

As requested:-

[/COLOR]Public Function BASE64SHA1(ByVal sTextToHash As String)


    Dim asc As Object
    Dim enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    Dim bytes() As Byte
    Const cutoff As Integer = 10


    Set asc = CreateObject("System.Text.UTF8Encoding")
    Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")


    TextToHash = asc.GetBytes_4(sTextToHash)
    SharedSecretKey = asc.GetBytes_4(sTextToHash)
    enc.Key = SharedSecretKey


    bytes = enc.ComputeHash_2((TextToHash))
    BASE64SHA1 = EncodeBase64(bytes)
    BASE64SHA1 = Left(BASE64SHA1, cutoff)


    Set asc = Nothing
    Set enc = Nothing


End Function


Private Function EncodeBase64(ByRef arrData() As Byte) As String


    Dim objXML As Object
    Dim objNode As Object


    Set objXML = CreateObject("MSXML2.DOMDocument")
    Set objNode = objXML.createElement("b64")


    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text


    Set objNode = Nothing
    Set objXML = Nothing


End Function

[COLOR=#574123]

 

[MARK858]

Deleted as missed something...

Edit: afraid it is encrypting fine for me as is, are both codes in the same regular module?

 

[John Wol]

Thank you for testing the code. It works fine for me on two out of my three computers.

Yes, the whole of the above code is in a single module (Module 1). The top left dropdown is showing "General" and there is no other option. Top right is showing "BASE64SHA1" there is an option to change this to "EncodeBase64".

I neglected to mention that, in my attempt to get this working, I reinstalled Office 365 completely. I did this before posting on this Forum.

Please don't assume that I am an expert. I may be missing something that you think should be perfectly obvious and not worth mentioning (e.g. a setting in Excel or Windows).

 

[mole999]

all allowed in the Trust centre / installed options in the references the same ?

 

[John Wol]

I have run 2 PCs side by side and compared all of the settings in the Trust Centre, they are identical. I even added the whole of my Documents folder to the list of Trusted Locations. Bear in mind that the two macros in the file work.

Please explain "installed options in the references" (I told you I'm not an expert).

 

[mole999]

right click a sheet name > view code

a new VBA window opens

Tools > References

and that is where you can see the references that excel uses, the ticked ones should be the same on each machine generally