Md5 checksum in excel (vba)

Posted on 2011-09-15
Last Modified: 2012-06-27

I searched and found a solution that almost made my day :)

The problem I have is that some strings that I want to create checksums on are more than 255 characters and than I get a #VALUE returned.

I want to create a checksum for each row in my sheet, so I have a formula similar to this: =md5hash(concatenate(C4;D4;E4;F4 ...).

Thankful for all help.

Now attached a file for your reference:

Regards, Tobias Gårdner
Question by:tobiasgardner
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2

Author Comment

ID: 36547703
One answer to my own question after some more browsing...
There is a problem to pass strings as in the example, but if the value is concatenated in a separate cell before passed to the md5hash function, it will work....
In new attached example, the string is concatenate in E6 and the md5hash is calculated in D6 with reference to E6 and this works!

Perhaps there are other solutions and if someone has one, I am interested :)

Tobbe G
LVL 45

Accepted Solution

aikimark earned 500 total points
ID: 36552857
If you create your own concatenation routine, such as BigConcat()

Public Function BigConcat(parmRange As Range) As String
    Dim rngCell As Range
    Dim strConcat As String
    For Each rngCell In parmRange
        strConcat = strConcat & rngCell.Value
    BigConcat = strConcat
End Function

Open in new window

You would be able to do something like this:

Open in new window

Note: this routine, as written, only handles a single contiguous range.  You would put this in Module1 in your project.

Author Comment

ID: 36558220
Thanks I will keep that under good to have. In this case I can not use contiguous range.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 45

Expert Comment

ID: 36567417

>>I can not use contiguous range
you use some contiguous ranges in the workbook you posted and in your question text.  You can use the BigConcat() function to coalesce such ranges in your CONCATENATE() call.  If you encounter character limits, you can rename the BigConcat() function as BC().

The BigConcat() can be tweaked to accept more ranges (as parameters).  It would then behave like the CONCATENATE() function.
LVL 45

Expert Comment

ID: 36568655
This version of BigConcat accepts a range that may have multiple areas.  This must be enclosed in parenthesis.

This formula would return the concatenated values from the 4th, 6th, and 8th rows from your F:Q data columns.

Open in new window

You could feed the results of the BCA() function into your MD5 hashing function:

Open in new window

Public Function BCA(parmRangeAreas As Range, Optional parmDelim As String) As String
    'BCA is a Big Concatenation of Areas function
    'pass a parenthesis enclosed range area and the BCA function will concatenate the values in all cells.
    'The optional parameter, parmDelim allows you to add a delimiter string between the concatenated cell values.
    Dim rngArea As Range
    Dim rngCell As Range
    Dim strConcat As String
    Dim strDelim As String
    For Each rngArea In parmRangeAreas.Areas
        For Each rngCell In rngArea
            strConcat = strConcat & strDelim & rngCell.Value
            strDelim = parmDelim
    BCA = strConcat
End Function

Open in new window

LVL 45

Expert Comment

ID: 37007587
The Better Concatenate Function (BCF) article has just been published.

The BCF is immune from performance problems you might encounter with large concatenation operations and allows you to automatically insert a delimiter string.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question