Question : Need MD5 Hash Function for Excel – with usability like a formula function
I need a function that will take an entire cell value in Excel and convert the value into its MD5 hash equivalent in a new cell. I have seen several comments about using VBA or other software. However, I am not real familiar with VBA. I would like to have a function that works like the other Excel functions where I can say something like “=MD5HASH(CellAddress)” in the Excel Formula bar where CellAddress is the cell I want converted.Is there such a function? Or, is there a way to take the VBA script and make it available in that way?
![Excel webservice function Excel webservice function](/uploads/1/1/9/7/119736797/623189960.png)
We offer over 800 free APIs for developers to develop the next big thing, add yours if you own an API. The WEBSERVICE function is only available in Excel 2013 and later for Windows. The WEBSERVICE function may appear in the Excel for Mac function gallery, but it relies on Windows operating system features, so it will not return results on Mac.
Also, I need the output to be the 32 character format. Below is an example output/input of what I need:
input: The quick brown fox jumps over the lazy dog
output: 9e107d9d372bb6826bd81d3542a419d6
output: 9e107d9d372bb6826bd81d3542a419d6
Solution: Need MD5 Hash Function for Excel – with usability like a formula function
Ok, you have to go to the VB editor (Alt-F11), right-click on your workbook in the project window, and click Insert->Module.
Then, paste the “code module” portion (except for the “Option CompareDatabase” line) into the window that pops up.
Then, paste the “code module” portion (except for the “Option CompareDatabase” line) into the window that pops up.
Next, right-click the workbook in the project window again and select Insert->Class Module
At the bottom-left, select the “Name” field, and enter “clsMD5” there
At the bottom-left, select the “Name” field, and enter “clsMD5” there
Paste the “class module” piece in the clsMD5 window, once again except for that first option line
Then, going back to the excel workbook window, you can set the formula like you say (=md5hash(…))!
I’ve already done this, so I’m attaching a sample.
md5.xls(100 KB) (File Type Details)
Using web services from Excel
Of course it’s easy in Google Docs…
Still, I got pretty excited when I saw this for the first time:
In Google Docs,
importXML
lets you read XML from a web service and then use XPath to select particular elements:where
A4
is the URL http://carbon.brighterplanet.com/flights.xml?origin_airport=MSN&destination_airport=ORD… yielding 327.4
kilos of carbon emissions or so.![Webservice In Excel For Mac Webservice In Excel For Mac](/uploads/1/1/9/7/119736797/840452747.png)
What about Excel?
I first looked for an equivalent to
XMLHttpRequest
for Excel. A helpful StackOverflow post about Msxml2.ServerXMLHTTP
and WinHttp.WinHttpRequest.5.1
got me started, but they’re Windows only. You’ll get errors about ActiveX can't create object
.Excel Webservice Xml
Then, thanks to an excellent tip from Kennedy27, I found
QueryTables
:I wrote a VBA function called
GetEmissionEstimate
and voilà!Web Service In Excel For Macbook Pro
It took me a while to remember that Excel is essentially functional programming, so the key to getting the result to auto-refresh is to make sure the output of your VBA function is entirely dependent on the input:
Using Webservice In Excel 2013
Now if I change the destination airport, the emission estimate will automatically update.