Получение MD5 хэша в VBA
Ещё один вариант этой функции, — для получения хеша файла:
- 25177 просмотров
Комментарии
Помог вариант без использования NET Framework. Почему-то вариант с Framework работает только со старыми версиями, а с 4.5 и 4.8 выдает ошибку.
Спасибо Вам, добрый человек! Очень помог вариант через API. Работает как надо.
Нашел решение на другом форуме через API
Считывается содержимое произвольного файла (в бинарном виде)
Так что код будет одинаковый для любого файла
Ваш ответ, на сколько я понял, позволяет получить хэш-сумму содержимого, а не файла. Это же разные вещи. Или это не так?
И также получить хэш-сумму фотографии или PDF документа в таком случае не получится, поскольку эти типы документов как правило формируются без считываемого текста.
Сначала надо считать текст из файла в переменную, потом вычислить MD5 для содержимого этой переменной (содержимого файла),
потом преобразовать полученную строку в числовое значение, и убрать знак минус (-), если он есть
Вот как-то так. Готового кода у меня нет.
Функция XL.ComputeMD5Hash: закодировать текстовую строку в MD5 hash
Функция XL.ComputeMD5Hash – текстовый тип формулы – возвращает MD5 хеш значение для заданной текстовой строки с заданной кодировкой.
Используя алгоритм хеширования MD5, функция помогает закодировать любую текстовую строку в 128-битное хеш значение. Поддерживаемые кодировки: ASCII, Unicode, BigEndianUnicode, UTF7, UTF8, UTF32.
Перед началом работы добавьте «Функции» в Excel
«Функции» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Как захешировать данные в md5 excel
Существует ли функция Excel для создания хэш-значения?
Я работаю с несколькими списками данных, которые имеют ключ по имени документа. Имена документов, хотя и очень описательные, довольно громоздки, если мне нужно их просмотреть (до 256 байт-это много недвижимости), и мне бы хотелось создать меньшее ключевое поле, которое легко воспроизводится в случае, если мне нужно сделать VLOOKUP с другой workseet или книги.
Я думаю хэш из названия, который был бы уникальным и воспроизводимым на каждое название было бы больше всего соответствующий. Есть ли функция, или я смотрю на разработку собственного алгоритма?
есть мысли или идеи по той или иной стратегии?
6 ответов
вам не нужно писать свою собственную функцию — другие уже сделали это за вас.
Например, я собрал и сравнил пять хэш-функций VBA на этом ответе на StackOverflow
лично я использую эту функцию VBA
- ее назвали с =BASE64SHA1(A1) в Excel после копирования макроса в VBA модуль
- требуется .NET, так как он использует библиотеку » Microsoft MSXML «(с опозданием обязательный)
настройка длины хэша
- хэш-изначально это 28 символов Unicode строку (регистр + спецсимволы)
- настройки длины хэш-кода со строки: Const cutoff As Integer = 5
- 4 цифры hash = 36 коллизий в 6895 линиях = 0.5 % частота коллизий
- 5 цифр хэш = 0 коллизий в 6895 строках = 0% коллизий
есть также хэш функции (все три функции CRC16), который не требует .NET и не использует внешние библиотеки. Но хэш длиннее и создает больше коллизий.
вы также можете просто скачать это пример книги и поиграйте со всеми 5 реализациями хэша. Как видите, на первом листе хорошее сравнение
Я не очень забочусь о столкновениях, но мне нужен слабый псевдорандомайзер строки, основанные на строковом поле переменной длины. Вот одно безумное решение, которое хорошо работает:
здесь Z2 — это ячейка, содержащая строку, которую вы хотите хэш.
«MOD» S там предотвратить переполнить к научной нотации. 1009 является простым, может использовать что-нибудь X, так что X*255 max_int_size . 10 произвольно; используйте что угодно. Значения «Else» являются произвольными (цифры pi сюда!); используйте что угодно. Расположение символов (1,3,5,7,9) произвольное; используйте что угодно.
для достаточно небольшого списка вы можете создать скремблер (хэш-функцию бедного человека), используя встроенные функции Excel.
здесь A1 и B1 содержат случайную начальную букву и длину строки.
немного возиться и проверки, и в большинстве случаев вы можете получить работоспособный уникальный идентификатор довольно быстро.
как это работает: формула использует первую букву строки и фиксированную букву, взятую из середины строки, и использует LEN () как «функция раздувания», чтобы уменьшить вероятность столкновений.
предостережение: это не хэш, но когда вам нужно что-то сделать быстро, и можете проверить результаты, чтобы увидеть, что нет никаких столкновений, он работает довольно хорошо.
изменить: Если ваши строки должны иметь переменную длину (например, полные имена), но извлекаются из записи базы данных с полями фиксированной ширины, вам нужно сделать это так это:
Как захешировать данные в md5 excel
Регистрация на форуме тут, о проблемах пишите сюда — alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите восстановить пароль
| Поиск по форуму |
| Расширенный поиск |
| К странице. |
| Страница 1 из 4 | 1 | 2 | 3 | 4 | Следующая > |
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
это — стандартный модуль.
пустой
а вы вставили код в модуль листа
куда не надо было вставлять
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
и все-же что-то не так
ATT.jpg
а с других модулей убрали ее?
в коде название функции GetHash а в формуле Gethash. Разве после написания в строке формул функции не приводятся к "дефолтным" именам?
Is there an Excel function to create a hash value?
I’m working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real estate) and I’d love to be able to create a smaller keyfield that’s readily reproducible in case I need to do a VLOOKUP from another workseet or workbook.
I’m thinking a hash from the title that’d be unique and reproducible for each title would be most appropriate. Is there a function available, or am I looking at developing my own algorithm?
Any thoughts or ideas on this or another strategy?
9 Answers 9
You don’t need to write your own function — others already did that for you.
For example I collected and compared five VBA hash functions on this stackoverflow answer
Personally I use this VBA function
- its called with =BASE64SHA1(A1) in Excel after you copied the macro to a VBA module
- requires .NET since it uses the library «Microsoft MSXML» (with late binding)
Customizing the hash length
- the hash initially is a 28 characters long unicode string (case sensitive + special chars)
- You customize the hash length with this line: Const cutoff As Integer = 5
- 4 digits hash = 36 collisions in 6895 lines = 0.5 % collision rate
- 5 digits hash = 0 collisions in 6895 lines = 0 % collision rate
There are also hash functions (all three CRC16 functions) which doesn’t require .NET and doesn’t use external libraries. But the hash is longer and produces more collisions.
You could also just download this example workbook and play around with all 5 hash implementations. As you see there is a good comparison on the first sheet
I don’t care very much about collisions, but needed a weak pseudorandomizer of rows based on a variable-length string field. Here’s one insane solution that worked well:
Where Z2 is the cell containing the string you want to hash.
«MOD»s are there to prevent overflowing to scientific notation. 1009 is a prime, could use anything X so that X*255 < max_int_size . 10 is arbitrary; use anything. «Else» values are arbitrary (digits of pi here!); use anything. Location of characters (1,3,5,7,9) are arbitrary; use anything.
For a reasonably small list you can create a scrambler (poor man’s hash function) using built-in Excel functions.
Here A1 and B1 hold a random start letter and string length.
A little fiddling and checking and in most cases you can get a workable unique ID quite quickly.
How it Works: The formula uses the first letter of the string and a fixed letter taken from mid-string and uses LEN() as a ‘fanning function’ to reduce the chance of collisions.
CAVEAT: this is not a hash, but when you need to get something done quickly, and can inspect the results to see that there are no collisions, it works quite well.
Is it possible to use the MD5 hash function within Excel?
The probability of accidental collisions varies depending on the input and is rare, with a likelihood of one in several hundred million. It's worth noting that files that are similar but not identical are more prone to having different hashes. While MD5 hashes have been replaced with more secure cryptographic hashes in the last decade, they remain suitable for the OP's purpose of verifying files on their local filesystem.
MD5 Hash function in excel?
In my document, I want to change several instances of excel cells from serial numbers to their corresponding MD5 hash . I am unsure if there is a pre-existing formula in Excel that can perform this task or if I would need to use VBA. If VBA is necessary, could you provide instructions on how to do it?
Solution 1:
The question regarding the password hash function for Excel VBA contains broken links. However, there is an updated version of the accepted answer available.
An implementation for both VB and VBScript can be found at: http://web.archive.org/web/20080526064101/http://www.frez.co.uk/freecode.htm#md5
Converting to Excel should be a straightforward process, in my opinion.
Regrettably, the resolution has been provided on experts-exchange, but they do not permit direct links. Thus, we must utilize Google. To access the solution, conduct a Google search and select the top result. It will be necessary to scroll down extensively to locate the accepted answer.
Source : Password hash function for Excel VBA
Solution 2:
Although this question is dated, I found it helpful and wanted to offer my solution to a similar issue.
Generate a module and incorporate the following script.
To call MD5, you can use:
Solution 3:
This web page https://www.mrexcel.com/board/threads/convert-string-to- md5-hash .973381/ provided me with the most suitable answer.
By utilizing the .NET API instead of incorporating everything in VB, it is expected to be efficient in terms of speed.
Here is the link to the documentation for the MD5CryptoServiceProvider class in the System.Security.Cryptography namespace of the .NET framework version 6.0: https://docs.microsoft.com/en-us/dotnet/api/system.security.cryptography.md5cryptoserviceprovider?view=net-6.0
Add the following VBA code:
Then call it using:
For those interested in understanding MD5, a helpful learning tool is available in the form of a pure excel implementation found on this website: https://tzamtzis.gr/2017/web-analytics/. The tool can be accessed without the need for VBA and provides valuable insights into MD5's workings.
Hash Password with MD5 and SHA1 in Excel VBA
In this video, I will demo how to Hash Password with MD5 and SHA1 in Excel VBAYou can see more detail and download source code …
MD5 : Generate excel's files
What is the method to produce multiple Excel files with the hashtag MD5 Hash ?
I have a multitude of files, and specifically for one of them, I need to generate a hash.
Like example bellow
As I'm working on SISS, it seems necessary to develop a Script Component in my Solution using either C# or VBA.
Anyone has suggestion to solve it ?
Solution 1:
The task known as "Execute Process" has the capability to execute a script in .bat file format, which ultimately creates a file with CSV (Comma Separated Variable) extension.
This link leads to a response on Stack Overflow with an ID of 9770913 and a user ID of 447901.
Solution 2:
Although I am unfamiliar with SISS, there exist numerous illustrations of creating md5 for files. One such illustration involves utilizing C#, while another example pertains to excel files. It should be noted that excel files can be treated like any other file, wherein one reads the bytes, inputs them into the hash function, and digests the resulting output.
Solution 3:
Avoid utilizing MD5 since this hashing algorithm is prone to collisions and there are free online resources that can reveal the hashed value. Instead, it is advisable to use contemporary hashing algorithms like SHA or B-Crypt that are not vulnerable to known collisions.
- https://md5decrypt.net/en/
- Check out the MD5 cracker tool available at hashkiller.co.uk.
- Check out the website that provides a tool for decrypting MD5 hashes — md5online.org.
- This website, whose URL is provided above, contains information about generating collisions for the MD5 hash function.
Why MD5 hash values are different for two excel files which appear the same
I possess two distinct excel files that are saved in different paths. One file was obtained by directly downloading it from the browser, while the other was downloaded through a selenium driver. Upon manual inspection, I observed that both files are identical. However, the generated MD5 hash values of both files differ. What steps can I take to resolve this problem?
Solution 1:
Hashing functions like MD5 are commonly used to verify the integrity of a file, stream or any other resource. By checking the bit level of a file, you can ensure that it has not been tampered with.
When dealing with a file that has integrity constraints at the bitwise level, using a hashing function is an ideal solution for comparison. This is due to the fact that the consequences of doing so are significant.
Considering the characteristics of Excel spreadsheets, modifying even a single bit on the bitwise level can cause a significant difference in the hash of the file. However, it is not always the case, but this should not be a concern.
As the driver utilized by Excel differs significantly from Selenium's driver due to Selenium's potential modifications and optimizations to the file, such as compression, it follows naturally that the resulting hash values will differ as well.
To start with, open the file using diff and analyze the variations between the two files. It's a nearly obvious fact that if the hashes of two files differ, the files themselves are also dissimilar.
Next, develop a driver that checks the accuracy of the data in the spreadsheets by comparing them. This will ensure the document's integrity and allow you to take hashes of the information instead of verifying the files using bitwise analysis.
My suggestion would be to export both as a CSV file and review them line by line to make comparisons.
Solution 2:
The MD5 algorithm calculates the entirety of a file, including its metadata such as the file name and dates. This means that two files may have the same "main content" but differ in certain bytes.
Deciding which section of the file is relevant for MD5 verification may pose a challenge.
If you have a Windows operating system and are solely interested in Excel files, consider utilizing this particular software available for download at http://www.formulasoft.com/download.html.
Solution 3:
Have you confirmed that the metadata is indeed incorporated in the hash? It's advisable to conduct some investigation on this matter.
Finding matching hashes would be improbable if the timestamps did not match. Moreover, even if the filename were changed multiple times, the hash value would remain unchanged. If metadata were included in the hash, the constantly changing accessed timestamp properties due to AV scans would result in a constant change in the hash value on a machine under constant scanning.
Solution 4:
Old post, new perspective:
The zip standard incorporates a timestamp, as stated in the Wikipedia article on zip. To determine if two spreadsheets have matching data, follow this process.
In case the diff command does not return any results, it means that the two files have different hashes but the spreadsheets remain unchanged.
According to alvonellos, hashing is accurately explained in the accepted answer. MD5 hashing Utilizing MD5 hashes for files that differ in any way, even by a single bit, will almost certainly result in different outcomes. Despite MD5 hashes being deprecated in favor of more cryptographic-secure hashes in the past decade, they still serve as a reliable option for validating files on the local filesystem. It's highly unlikely to experience accidental collisions, with the likelihood being one-in-several-hundred million, depending on the input. Notably, files that are similar but not identical are more likely to have different hashes. Crafting two files with the same hash is challenging and requires precise modifications in various places in one of the files. If you have doubts about MD5, you can use any SHA or other hashing algorithm to obtain similar results.
Deep dive into .XLSX:
The .zip format is what underlies the .xlsx format. To decompress an .xlsx, one can employ the linux unzip utility.
Prior suggestions propose the computation of a diff for the two files, without specifying the optimal approach. However, by utilizing unzip on the .xlsx file, a directory structure containing the essential components of the spreadsheet will be generated.
After expanding file1.xlsx to dir1/ and file2.xlsx to dir2/ in two separate spreadsheets, you can perform a recursive diff on the resulting files.
If your primary concern is checking for dissimilarity in the files' content, the command will provide an answer. The absence of any output signifies that there are no differences in content between the directories, implying that the original spreadsheets' content is identical.
To explore the dissimilarities within the .xlsx files, you can investigate the header bits using the xxd utility available on Linux.
The sixth octet in the time-stamp displays acab and 66ac while the seventh octet indicates the date, which is a354 for both.
The CRC-32 hash generated by the zip utility can be found in the eighth and ninth octets, making it a useful tool. If you have the xxd utility available, you can easily skip the unzipping process by utilizing this hash.
The outcome will resemble something along these lines:
This confirms that the hashes of the two files are identical, irrespective of their timestamps.
Excel formula-based function for SHA256 / SHA512 hashing without VBA or macros
In 2017, those who prioritize security should steer clear of 'broken' hashes like MD5. Is there a method available, that doesn't involve VBA or macros, to perform more secure SHA256 or SHA512 hashing in Excel?
An impressive illustration of this was accomplished over three and a half years ago with MD5, which can be viewed in this Stack Overflow post about generating an MD5 hash function in Excel without utilizing VBA.
The rationale behind not using VBA/Macros is to ensure compatibility with mobile devices across different platforms, including Excel for iOS.
It should be noted that the initial Stack Overflow post includes a response that was effective but contained a non-functioning hyperlink. As such, a fresh link has been provided for your reference: https://tzamtzis.gr/2017/web-analytics/excel-function-md5-hashing-without-vba/
Solution:
I possess a tool that has a limit of 55 characters, and its purpose is mainly educational. For instance, it can be used to understand the functioning of SHA256 in Excel.