Add-In Load Time Registry Binary Value Conversion to Decimal

[Bilbo_Baggins_Esq]

HI All!
Long time no see.
I hope everyone has been well and fine.

I find myself needed help creating a custom VBA function in Access (2016).

I need to convert the add-in load time values stored as binary registry keys in the registry to decimal values.
A few sample values look like this:
05 00 00 00 5e 00 00 00 71 02 00 00 cb 00 00 00 da 00 00 00 4e 00 00 00
05 00 00 00 3e 00 00 00 4e 00 00 00 5e 00 00 00 f4 01 00 00 ac 00 00 00
05 00 00 00 10 00 00 00 10 00 00 00 0d 03 00 00 1f 00 00 00 0f 00 00 00
02 00 00 00 0F 00 00 00 2F 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

As Shown above, I have added a space “ “ between each pair, but in the registry they are stored as true binary pairs (24 pairs each)

Now, before any on suggests it would be easier for my quest to just capture the actual binary value directly from the local registry, unfortunately, that isn’t possible.
The load times being converted are not from my machine but rather are supplied to me in CSV format from other machines.
I have already imported the CSV to an Access table and the values are in a “Short Text” field.

DISCLAIMER: While I have done a lot with Access and other development over the many years, I have thus far managed to successfully avoid contact with conversion of Binary and or Hex so I’m a bit sketchy on just exactly how to actually do it.
I can work with strings, no problem, but conversion of binary values to decimal I’ve never worked with.

I did some searching and I did find a “BinToDec” function offered by “datAdrenaline.”
I’ve inserted it into a module but I knew ahead of time it wasn’t going to work because it specifies an inpout length of 32 characters or less and these are 48 even minus the spaces between the values.

I’d deeply appreciate some help with a function here.

THANKS IN ADVANCE

 

[arnelgp]

Its not binary its hex on display.

 

[isladogs]

As Arnel rightly pointed out this isn't binary as that just has 0 and 1 values.

Here's a function that will convert Hex to Binary (untested)

Function Hex2Bin(sHexBin As String) As String
    Dim sHB, i      As Integer

    sHB = Array( _
          Array("0", "0000"), _
          Array("1", "0001"), _
          Array("2", "0010"), _
          Array("3", "0011"), _
          Array("4", "0100"), _
          Array("5", "0101"), _
          Array("6", "0110"), _
          Array("7", "0111"), _
          Array("8", "1000"), _
          Array("9", "1001"), _
          Array("A", "1010"), _
          Array("B", "1011"), _
          Array("C", "1100"), _
          Array("D", "1101"), _
          Array("E", "1110"), _
          Array("F", "1111"))

    For i = 0 To 15
        sHexBin = Replace(sHexBin, sHB(i)(0), sHB(i)(1), Compare:=vbTextCompare)
    Next

    If Len(Replace(Replace(sHexBin, "0", ""), "1", "")) <> 0 Then
        Hex2Bin = "Invalid characters"
    Else
        Hex2Bin = sHexBin
    End If
End Function

I don't know if its possible to convert more than 32 binary characters due to the limitations of the decimal datatype.

There are several Bin2Dec functions doing the rounds. Some seem better than others.
AFAIK the one below was not by datAdrenaline and its worked fine for everything I've thrown at it. See if it works for you (for a binary string):

Function Bin2Dec(BinaryString As String) As Variant
    Dim x As Integer
    For x = 0 To Len(BinaryString) - 1
        Bin2Dec = CDec(Bin2Dec) + Val(Mid(BinaryString, _
                  Len(BinaryString) - x, 1)) * 2 ^ x
    Next
End Function

If for any reason you also need it, I have a Dec2Bin function as well

 

[arnelgp]

Even if you successfully convert that hex dump, still you need the actual data structure of iy in order to group and parse it.

 

[Bilbo_Baggins_Esq]

Its not binary its hex on display.

I was thinking it looked like both, hex in binary.
Meaning the binary values are stored in hex.

Is that what you mean, and if so, would that mean I'd first need to convert each pair from Hex to binary and then convert those to decimal?

If not, I'm afraid I'm off in the weeds?

Can you elaborate on what you mean by "hex on display" and how I work with that to get these decimal values?

These are add-in load times so I'm sure the end result will look like seconds and decimals of seconds.

 

[isladogs]

I clicked send before I'd completed my last post.

A Google search will find VBA Hex2Dec converters

There are also plenty of Hex to decimal converters online
For example the Code Beautify site has various converters
https://codebeautify.org/hex-decimal-converter

Your first example (spaces removed) is 050000005e00000071020000cb000000da0000004e000000
In binary that's 1010000000000000000000000000101111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

which converts to a decimal value 1.2259964380591624e+56

 

[arnelgp]

You need a hex2dec converter, there are many ou there.

 

[Bilbo_Baggins_Esq]

I clicked send before I'd completed my last post.

A Google search will find VBA Hex2Dec converters

There are also plenty of Hex to decimal converters online
For example the Code Beautify site has various converters
https://codebeautify.org/hex-decimal-converter

Your first example (spaces removed) is 050000005e00000071020000cb000000da0000004e000000
In binary that's 1010000000000000000000000000101111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

which converts to a decimal value 1.2259964380591624e+56

Thanks!
Well, your calculated result confirms something I was afraid of.
The structure sequence has to be different than shown.
That value above can't possibly be an accurate add-in load time.
Likely, there is some internal, proprietary Microsoft utilization going on.

 

[The_Doc_Man]

Here is my question: Are you talking about "load time" as being some sort of date/time field either in format or intent? Is that how you mean it?

 

[Bilbo_Baggins_Esq]

Here is my question: Are you talking about "load time" as being some sort of date/time field either in format or intent? Is that how you mean it?

Load time should be in seconds and decimals of seconds.

For example, 3.456734

 

[isladogs]

Thanks!
Well, your calculated result confirms something I was afraid of.
The structure sequence has to be different than shown.
That value above can't possibly be an accurate add-in load time.
Likely, there is some internal, proprietary Microsoft utilization going on.

I was surprised at the value so checked it by doing Hex2Dec direct and Hex2Bin followed by Bin2Dec. Same result each time AS LONG AS THE SPACES ARE REMOVED.

 

[Bilbo_Baggins_Esq]

I was surprised at the value so checked it by doing Hex2Dec direct and Hex2Bin followed by Bin2Dec. Same result each time AS LONG AS THE SPACES ARE REMOVED.

It's obviously not the actual value but I'm sure you calculated it as shown correctly.

Clearly there is some missing piece of the process to successfully derive the actual load time but absent some insight from Microsoft on that sequencing, I think we're out of gas.

I really appreciate your time and interest.

 

[The_Doc_Man]

I have a theory on what that string really is. I am working on a way to test the theory. Until I do, it might be unwise of me to attempt to actually post it because I don't want to lead anyone astray.

 

[isladogs]

Do you have a copy of the function that creates these registry keys ....
Or can you contact the author if it's an addin?

Just a thought
Perhaps these aren't load-in times but the date/time at which an event completed
E.g UNIX datetime since 1/1/1970 or from some other reference point expressed in milliseconds then converted to hex.
Might be worth playing around to reverse engineer the results

 

[Bilbo_Baggins_Esq]

I have a theory on what that string really is. I am working on a way to test the theory. Until I do, it might be unwise of me to attempt to actually post it because I don't want to lead anyone astray.

WOW!!!

Bearing mind we don't have the luxury of working directly from the key's actual value, if it helps for any other purpose, the keys can be found at:
HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Outlook\AddInLoadTimes\

 

[Bilbo_Baggins_Esq]

Do you have a copy of the function that creates these registry keys ....
Or can you contact the author if it's an addin?

Just a thought
Perhaps these aren't load-in times but the date/time at which an event completed
E.g UNIX datetime since 1/1/1970 or from some other reference point expressed in milliseconds then converted to hex.
Might be worth playing around to reverse engineer the results

I'm 99.999% certain Outlook writes these values itself on AppOpen.

 

[Bilbo_Baggins_Esq]

Do you have a copy of the function that creates these registry keys ....
Or can you contact the author if it's an addin?

Just a thought
Perhaps these aren't load-in times but the date/time at which an event completed
E.g UNIX datetime since 1/1/1970 or from some other reference point expressed in milliseconds then converted to hex.
Might be worth playing around to reverse engineer the results

I don't have handy any actual custom Outlook add-ins, but I have a crap load of various Excel custom add-ins which I wrote and for which I am 100% certain I didn't write any code to track load time and/or add/update any key values.

So, I confirmed a specific Excel add-in was NOT shown in the \Excel\AddInLoadTimes\ section.
I added the add-in in Excel, added a sub to a custom button on the ribbon, and closed Excel.
When I reopened Excel, I could watch the Splash and Excel loading the add-in and low and behold, when I refreshed the registry folder, there it was with a load time value.

Again, this is Excel, but i suspect the same is true for Outlook.

 

[isladogs]

In case it helps, attached are my Outlook add-in load times

Interestingly (for me at least) several of these are for addins I've long since deleted (e.g. SnagIt) ... or never knew existed!

I tested my earlier theory but it seems to be wrong
Current Unix time in seconds = 1522710229

What I did notice is that in each group each pair with a hex value is followed by up to 3 zero pairs which I think are padding and could be discarded
e.g. 05 00 00 00 10 00 00 00 10 00 00 00 5e 00 00 00 1f 00 00 00 2f 00 00 00

=> 05 10 10 5e 1f 2f
=> 0510105e1f2f as HEX
=> 5566552219439 as DEC
But even if that's the case, as yet I've no idea what it means
Hopefully Doc can use the figures as part of his test

NOTE:
As an aside, I now understand the numerical part of the image attachment done a few minutes ago (1522709359) - the Unix time when uploaded

 

[The_Doc_Man]

Take a look at this excerpt from your first post:

05 00 00 00 5e 00 00 00 71 02 00 00 cb 00 00 00 da 00 00 00 4e 00 00 00
05 00 00 00 3e 00 00 00 4e 00 00 00 5e 00 00 00 f4 01 00 00 ac 00 00 00

Those extra zeros are NOT padding. Note that in the first line, following the 71, you have an 02 in the 2nd byte of the group of 4 bytes. You have an 01 in the 2nd byte of the group of 4 in the second line after the f4.

I'm having second thoughts about my theory because the darned strings have WAY too many bytes. I'm not done looking at it yet, but I am not sanguine on what those things actually represent.

 

[arnelgp]

You csn searchfunction in this firum that read values from the registry. I think that it will give you the result withou the need of conversion provided you supply the correct datatype