# Visual Basic for Applications/Folder Hashing in VBA
## Summary

* These modules are made for Microsoft Excel only. It hashes files in whole folders. It handles both flat and recursive folder listing, makes log files, and verifies files against hash files made previously.
* Any of five hash algorithms can be used on the worksheet. They are, MD5, SHA1, SHA256, SHA384, and SHA512,. They are displayed on Sheet1 of the workbook in either hex or base64 formats. If log files are also required for these hashes, they are made in SHA512-b64 format for future verification; this format is independent of the format chosen for worksheet listings.
* Verification results appear on Sheet2 of the workbook. Verification failures are highlighted in red. Make sure therefore that Sheet1 and Sheet2 exist in the workbook. These results can also be delivered to a log file for future use.
* Log files, when made, are found in the default folder. Make log choices on the user form's check box options.
** HashFile*.txt logs have a name that is date-stamped, and contains the number of files listed in it. Separate logs can be made for each run.
** HashErr.txt is the error log. It logs file item paths that could not be hashed. There is only one of these, and the results for each run are appended with a date-time stamp. When full, just delete it and a new one will be made as required.
** VerReport*.txt logs a copy of verification results. A separate log can be made for each verification run. It too has a date-time stamp in its file name.
* The process is slower than FCIV, but has more algorithms to choose from. However, unlike FCIV no single file can exceed about 200MB. See File Hashing in VBA for notes on ways to hash larger files. A recursive run of the Documents folder, (2091 user files, and 1.13GB in total), took seven and a half minutes. It included writing to the worksheet, making a hash log, and logging 36 filter exclusions in an error file. Verification is faster, taking about half of that time.
* A user form layout is shown in Figure 1. The exact control names are given, and these correspond exactly to those in code. The use of the same control names is essential for a trouble-free installation. Regrettably, there is no way in Wikibooks to download an Excel file, or for that matter the VBA code files themselves, so the main work is in the making of the user form.
* Set filter conditions in FilterOK. The fastest results can be had when the filter conditions are as narrow as possible. A wide range of filter conditions can be set directly in code, and for items filtered, their paths will be listed in the error file.
* Be sure to set VBA Project references. Required are Visual Basic for Applications Extensibility 5.3, mscorlib.dll, and Microsoft Scripting Runtime, in addition to any others that you may require. The VBA editor's error setting should be Break on Unhandled Errors.
* My Documents versus Documents. There are four virtual folders in the Libraries category of the Windows Explorer, My Documents, My Music, My Pictures, and My Videos. When the Windows Explorer's Folder Options are set to NOT display hidden files, folders, drives and Operating system files, the correct locations are nonetheless returned by the folder selection dialogs, namely Documents, Music, Pictures, and Videos. When there are NO restrictions on viewing hidden and operating system files and folders, then selection dialogs will wrongly attempt to return these virtual paths, and access violations will result. It is only by avoiding this situation that easy listings can be obtained, so check that the Folder Options of Windows Explorer are set in accordance with Figure 2.

## The Code Modules

IMPORTANT. It was found that the hash routines errored in a Windows 10, 64 bit Office setup. However, subsequent checking revealed the solution. The Windows platform must have intalled the Net Framework 3.5 (includes .Net 2 and .Net 3), this older version, and not only the Net Framework 4.8 Advanced Services that was enabled in Turn Windows Features on and off. When it was selected there, the routines worked perfectly.
There are three modules to consider; the ThisWorkbook module, that contains the code to run automatically at startup; the Userform1 module, that contains the code for the controls themselves, and the main Module1 code that contains everything else.
* Make sure that Sheet1 and Sheet2 exist on the workbook.
* Then, make a user form called UserForm1, carefully using the same names as the controls in Figure 1, and in exactly the same places. Set the UserForm1 as non-modal in its properties. Save the Excel file with an *.xlsm suffix.
* Double click the UserForm1, (not a control), in design mode, to open the code module associated with it, then copy the respective code block into it. Save the Excel file. (Saving the file in the VBE editor is exactly the same as saving on the workbook.)
* Insert a standard module, and copy the main code listing into it. Save the file.
* Lastly, when all other work is done, transfer the ThisWorkbook code, and save the file.
* Set the Windows Explorer folder options in accordance with Figure 2.
* Close the Excel workbook, then reopen it to be display the user form. If the user form is closed for any reason, it can be re-opened by running the Private Sub Workbook_Open procedure in the ThisWorkbook module. (ie: Place cursor in the procedure then press F5.)

## Using the App

There are two main functions; making hashes on the worksheet and an optional hash log, and verifying computer folders against a previously made hash log. The hashing mode also includes an optional error log, to list both errors and files avoided by the user-set filters. Verification results use an optional log of their own. Be sure to note the required Folder Options of Figure 2 before any hashing activities.

### Making hashes

* Set the options, recursion, output format, and hash algorithm in the topmost panel. Make log file selections on the check boxes.
* Select a folder to hash with Select Folder to Hash. Then, pressing the Hash Folder button starts the listing on Sheet1 of the workbook.
* Wait for the run to finish. The user form's top-caption changes to advise that the application is still processing, and message boxes advise when the run is complete. The Stop all Code button can be pressed at any time to return to the VBA editor in either of the two working modes.
* Filtered files will be ignored in hashing. These are files deliberately avoided by user settings in the FilterOK procedure. Such files will be listed in the error file (HashErr*.txt), if selected.
* Log files are available for inspection, if such options were selected, located most often in the workbook's launch folder.
* Restrict hashing to user libraries. Owing to the large numbers of hidden and otherwise restricted files in Windows, it is recommended that hashing be restricted to the contents of the user profiles. Although some files will be restricted even there, for most this is not much of a limitation, since it still includes Documents, Downloads, Music, Pictures, and Videos, and various other folders.

### Verifying Folders

The verification process verifies only those file paths that are listed on the chosen hash file, and will not even consider files added to the file folders since the hash file was made. When folders are changed, new hash files need to be made in a working system.
* Make a file selection in the bottom panel, by pressing Select File to Verify. This must be a log file (HashFile*.txt) made at an earlier time for the purpose of verification. It is the same file that can be made during a hash run, and regardless of any settings made for worksheet listing, these files will always be made as SHA512-b64 format.
* Press Start Verification to start the process. Results are listed on Sheet2 of the worksheet, and any failures are color-highlighted. The user form caption changes to advise that the application is still processing, and message boxes advise when the process is complete.
* Review the results , either on Sheet2 or in the verification results file (VerHash*.txt) in the default folder. Consider further action.

### Code Modification Notes

* Code modified 17 Oct 20, replaced the API version of folder selection with one that is independent of 32 or 64 bit working
* Code modified 28 Jan 19, modified SelectFile, to set All Files as default display.
* Code modified 9 Dec 18, corrected CommandButton6_Click, one entry wrongly marked sSht instead of oSht.
* Code modified 5 Dec 18, corrected Module1, code error in initializing public variables.
* Code modified 5 Dec 18, updated Module1 and UserForm1 for improved status bar reporting and sheet1 col E heading.
* Code modified 4 Dec 18, updated Module1 and UserForm1 for more responsive output and reporting improvements.
* Code modified 2 Dec 18, updated Module1 for error reporting improvements, and GetFileSize larger file reporting.
* Code modified 1 Dec 18, corrected Module1 and UserForm1 for error log issues.
* Code modified 30 Nov 18, updated to provide algorithm selection and a new userform layout.
* Code modified 23 Nov 18, corrected sheet number error, format all code, and remove redundant variables.
* Code modified 23 Nov 18 updated to add verification and a new userform layout.
* Code modified 21 Nov 18 updated to add error logging and hash logging.

### The Standard Module1
## See Also

* File Hashing in VBA: Contains code for single file hashing, and in particular, notes on applications to hash large files.*
* String Hashing in VBA: Code for the hashing of strings.