locked
unknown method of .Net class in VBA - differend results RRS feed

  • Question

  • Hello,

    please i can't get same result in VBA form same method of class - difference C# and VBA - the method in VBA System.Security.Cryptography.SHA256Managed.ComputeHash() is not availible - why it is possible? I found only ComputeHash_2() method in internet.

    Please help me.

    In this case i will get byte array - but from C# get some values - there is an app done (its not possible to change it) and i want symply create same value from Excel - cose testing and user hepls for using the application (big data tables manipulations, data preparing in Excel).

    ---------------

    Code in C#

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Globalization;
    using System.Security.Cryptography;
    using System.Xml.Linq;
    
    namespace ConsoleApplication5
    {
        class Program
        {
            static void Main(string[] args)
            {
                var xml = "<Product Code=\"2036\"><TCD>12/1/2014 12:00:00 AM</TCD><Duration>35</Duration><Premium>500</Premium><PaymentMode>bank_transfer</PaymentMode><PaymentFreq>monthly</PaymentFreq><Growth>4</Growth><Indexation>0</Indexation><Persons><Person Id=\"b07b5367-018c-4377-aaee-b8b91bcdc10b\"><BirthDate>1/1/1982 12:00:00 AM</BirthDate><FirstName /><Surname /><Title /><Gender>male</Gender><RiskCategory>I</RiskCategory><Job /><JobCategory /><Sport /><SportCategory /><Coverages><Coverage Code=\"2036\"><SA>100000</SA><Duration>35</Duration><Loading>0</Loading></Coverage><Coverage Code=\"6081\"><SA>100000</SA><Duration>33</Duration><Loading>0</Loading></Coverage></Coverages></Person></Persons><Topups><InitialCosts Calculate=\"false\" /><AutomaticTopups Calculate=\"false\" Percentage=\"0\" /><AE28 Rate=\"1.2\" /><Normal /></Topups></Product>";
    
                var uniEncoding = new UnicodeEncoding();
                var byteInput = uniEncoding.GetBytes(xml);
                
                var hash = new SHA256Managed().ComputeHash(byteInput);
                
                Console.WriteLine(BitConverter.ToString(hash));
                Console.ReadLine();
            }
        }
    }


    --------------------------

    Code in VBA - Excel 2010

    Sub digest256()
        Dim lngLoop As Long
        Dim oUTF, oEnc
        Dim HMAC() As Byte
        Dim byteInput() As Byte
        Dim strText, strTemp As String
     
        'input text value of Selection.Cells(1, 1) is same as xml in C#
        strText = Selection.Cells(1, 1).Value
        
        Set oUTF = CreateObject("System.Text.UnicodeEncoding")
        Set oEnc = CreateObject("System.Security.Cryptography.SHA256Managed")
        byteInput = oUTF.GetBytes_4(strText)
        
        
        ' here differences       - call method ComputeHash shows Error "invalid argument"
        ' call another method - found from internet take different results
        HMAC = oEnc.ComputeHash_2(byteInput)
        'HMAC = oEnc.ComputeHash(byteInput)
    
        'Convert the byte array to a hex string
        For lngLoop = 0 To UBound(HMAC)
          strTemp = strTemp & UCase(Right("0" & Hex(HMAC(lngLoop)), 2))
        Next
        
        ActiveSheet.Cells(2, 1).Value = strTemp
    End Sub



    Thursday, November 20, 2014 8:52 AM

All replies

  • Hi VF,

    I tried your C# and VBA code, and reproduced your problem that ComputeHash method is not available in VBA code. But I get the same result with C# code and VBA code for the same string. the only difference is that in C# returned to me a hex string split with dash "-", whereas the VBA method returned to me only the converted hex string.

    This is the C# code result:

    16-61-B8-67-54-C5-F7-0A-56-FB-73-3B-39-8E-A9-3D-65-65-E4-36-C9-F4-DA-B8-3C-8B-06-60-98-E9-1A-68

    And this is the result of the VBA code, I used exactly the same code with yours, without any change:

    1661B86754C5F70A56FB733B398EA93D6565E436C9F4DAB83C8B066098E91A68

    As you can see, they're actually the same, I tried both Excel 2010 and 2013, also tried VS2010/.NET4.0 and VS2013/.NET4.5.

    The only problem I can think of is that you use the difference string for the C# ComputeHash method and the VBA ComputeHash_2 method. Please debug the VBA code to make sure the parameter string is the same.

    By the way, this forum is actually for Excel Development, your question is only about C# & VBA but not about Excel Development. I recommend that you post such questions in this forum:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Caillen Friday, November 21, 2014 6:28 AM
    Friday, November 21, 2014 6:25 AM