Skip to main content

Excel Macro: Base64 HMAC Encryption

Some APIs require each REST request to be signed with an HMAC SHA256 signature, and these signatures need to be converted to base64. This custom function calculates HMAC SHA1, SHA256, SHA384 or SHA512 with Base64 encoding. To use this code, you need do this:

  1. Download and install the .NET Framework 3.5, only working in .NETFramework 3.5.
  2. Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0 (or whatever your latest is. This will give you access to the XML Object Library.)

Base64 HMAC Encryption

'------------------ Modules ------------------
Public Function Base64HMAC(ByVal sType As String, ByVal sTextToHash As String, ByVal sSharedSecretKey As String)
    'sType: SHA1, SHA256, SHA384, SHA512
    Dim asc As Object, enc As Object
    Dim TextToHash() As Byte
    Dim SharedSecretKey() As Byte
    
    Set asc = CreateObject("System.Text.UTF8Encoding")  'only woring in .NET Framework 3.5
    sType = UCase(sType)
    Select Case sType
    Case "SHA1"
        Set enc = CreateObject("System.Security.Cryptography.HMACSHA1")
    Case "SHA256"
        Set enc = CreateObject("System.Security.Cryptography.HMACSHA256")
    Case "SHA384"
        Set enc = CreateObject("System.Security.Cryptography.HMACSHA384")
    Case "SHA512"
        Set enc = CreateObject("System.Security.Cryptography.HMACSHA512")
    Case Else
        Base64HMAC = "Error! sType value: SHA1, SHA256, SHA384, SHA512"
        Exit Function
    End Select

    TextToHash = asc.Getbytes_4(sTextToHash)
    SharedSecretKey = asc.Getbytes_4(sSharedSecretKey)
    enc.Key = SharedSecretKey

    Dim bytes() As Byte
    bytes = enc.ComputeHash_2((TextToHash))
    Base64HMAC = EncodeBase64(bytes)

    Set asc = Nothing
    Set enc = Nothing
End Function

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

    'Inside the VBE, Go to Tools -> References, then Select Microsoft XML, v6.0
    '(or whatever your latest is. This will give you access to the XML Object Library.)

    Dim objXML As MSXML2.DOMDocument60
    Dim objNode As MSXML2.IXMLDOMElement

    Set objXML = New MSXML2.DOMDocument60

    ' byte array to base64
    Set objNode = objXML.createElement("b64")
    objNode.DataType = "bin.base64"
    objNode.nodeTypedValue = arrData
    EncodeBase64 = objNode.Text

    Set objNode = Nothing
    Set objXML = Nothing
End Function

Examples

You can test the code in the VBE Immediate Window with the Debug.Print method, for example:

run this code in Immediate Window:

debug.Print Base64HMAC("sha256","YourText","YourKey")

Result:

KTGtkOlKXYiF0s7yyzTZr9utrHfSuY0KE+kJLT1zetA=

or use as worksheet function:

Base64 HMAC SHA1

=Base64HMAC("sha1","abc","1234567890")

Base64 HMAC SHA256

=Base64HMAC("sha256","abc","1234567890")

Base64 HMAC SHA384

=Base64HMAC("sha384","abc","1234567890")

Base64 HMAC SHA512

=Base64HMAC("sha512","abc","1234567890")

Check Result

Check result here: Hash and HMAC calculator

Base64 HMAC SHA256 in different languages

If you need examples of creating base64 hashes using HMAC SHA256 in different languages (Javascript, PHP, Java, Groovy, C#, Objective C, Go, Ruby, Python, Perl, Dart, Swift, Rust, Powershell, Shell), read this post.

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code into a Standard Module:

  1. Activate the Visual Basic Editor by pressing ALT + F11.
  2. Right-click the project/workbook name in the Project Window.
  3. Choose Insert -> Module.
  4. Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

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

Format your code: <pre><code class="language-vba">place your code here</code></pre>