• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 13792
  • Last Modified:

Md5 checksum in excel (vba)

Hi,

I searched and found a solution that almost made my day :)
http:/Q_23395844.html

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:
md5-testing.xls

Regards, Tobias Gårdner
0
tobiasgardner
Asked:
tobiasgardner
  • 4
  • 2
1 Solution
 
tobiasgardnerAuthor Commented:
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!
md5-testing.xls

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

Regards,
Tobbe G
0
 
aikimarkCommented:
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
    Next
    BigConcat = strConcat
End Function

Open in new window


You would be able to do something like this:
=md5hash(BigConcat(I4:Q4))

Open in new window


Note: this routine, as written, only handles a single contiguous range.  You would put this in Module1 in your project.
0
 
tobiasgardnerAuthor Commented:
Thanks I will keep that under good to have. In this case I can not use contiguous range.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
aikimarkCommented:
@tobiasgardner

>>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.
0
 
aikimarkCommented:
This version of BigConcat accepts a range that may have multiple areas.  This must be enclosed in parenthesis.

Example:
This formula would return the concatenated values from the 4th, 6th, and 8th rows from your F:Q data columns.
=BCA((F4:Q4,F6:Q6,F8:Q8))

Open in new window


You could feed the results of the BCA() function into your MD5 hashing function:
=md5hash(BCA((F4:Q4,F6:Q6,F8:Q8)))

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
        Next
    Next
    BCA = strConcat
End Function

Open in new window

0
 
aikimarkCommented:
The Better Concatenate Function (BCF) article has just been published.
http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now