# Visual Basic for Applications/Printable version

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/Visual_Basic_for_Applications

# Character 1D Arrays
## Summary

* This VBA code module is intended for any Microsoft Office application that supports VBA.
* It allows strings to be loaded into one-dimensional arrays one character per element, and to join such array characters into single strings again.
* The module is useful in splitting the characters of a single word ,something that the Split method cannot handle.

## Notes on the code

Copy all of the procedures below into a VBA standard module, save the workbook as a xlsm type, then run the top procedure to show that the process is accurate.

## External Links
# Array Data To Immediate Window
## Summary

This VBA code module allows the listing of arrays in the immediate window. So that the user can see examples of its use, it makes use of various procedures that fill the array for demonstration and testing. The VBA code runs in MS Excel but is easily adapted for any of the MS Office products that run VBA. Clearly, mixed data varies in length and in its number of decimal points. This module displays the array neatly taking account of the variations that might otherwise disrupt the layout. It can decimal point align the data or not, according to internal options.

## Code Notes

* DispArrInImmWindow is the main procedure. It formats and prints data found on the two dimensional input array. It prints on the VBA Editor's Immediate Window. Options include the printing of data as found or making use of decimal rounding and alignment. The entire output print is also available as a string for external use. The process depends on monospaced fonts being set for any display, including the VBA editor.
* RndAlphaToArr, RndNumericToArr, and RndMixedDataToArr load an array with random data. The data is random in the content and length of elements, but in addition, numerics have random integer and decimal parts. Each allows adjustment of options internally to accommodate personal preferences.
* TabularAlignTxtOrNum is not used in this demonstration. It is included for those who prefer to format each individual column of an array during the loading process. Its input variant takes a single string or number and returns the formatted result in a user-set fixed field width. The number of decimal places of rounding can be set. Note that when all data in a column of a numeric array is loaded with the same parameters, the result is always decimal point alignment.
* WriteToFile is a monospaced font, text file-making procedure. If the file name does not exist, it will be made and saved automatically. Each save of text will completely replace any previously added. It is added here in case a user needs to save an output greater than that possible for the Immediate Window. The Immediate Window is limited to about two hundred lines of code, so large arrays should make use of the main procedure's sOut string. Again, wherever outputs from the main procedure are used, monospaced fonts are assumed.
* Note that the user might add a procedure to export large values of sOut, the formatted string, to the clipboard. Procedures exist elsewhere in this series that will accomplish this.

## The VBA Module

Copy the entire code module into a standard VBA module, save the file as type .xlsm and run the top procedure. Be sure to set monospaced fonts for the VBA editor or the object will have been defeated.

### Updates

* 26 Nov 2019: Adjusted DispArrInImmWindow code to better estimate maximum column width, taking account of imposed decimal places.

## See Also
# Array Data To WorkSheet (1D or 2D)
### Summary

This MS Excel VBA code listing transfers data from a one or two dimensional array to a worksheet.

### Code Notes

* Arr1Dor2DtoWorksheet transfers data from an array to a specified worksheet, and at a specified location within it. It takes one-dimensional or two-dimensional arrays, and is able to distinguish between them, prior to the transfer. Non-array inputs are detected as are arrays that are not allocated. One-dimensional arrays are transferred into a sheet row in all cases. Two-dimensional arrays are displayed in the same row and column shape as in the array. There are no facilities in the procedure to transpose data, but procedures exist elsewhere in this series for that purpose.

### The VBA Module

* Copy the entire code listing into a VBA standard module and run the top procedure. Save the worksheet as type .xlsm. Comment and de-comment lines in the top procedure and adjust parameters to test the main procedure.

### See Also
# Array Output Transfers
## Summary

This VBA code module demonstrates four basic methods of array display. It is intended to run in MS Excel, though with the exception of the first method, transfer to the worksheet, it could be adapted easily for MS Word or other MS Office applications that run VBA.

## Code Notes

* The code first loads an array with selected random data. Then the entire array is transferred to the worksheet. The data is further formatted and displayed in the immediate window, in well spaced columns. An additional copy of the formatted output is passed to the clipboard for further external use, and it is also sent to a text file to illustrate the method.
* RndDataToArr can load an array with random data. The data types can be set as parameters, and further limits can be found within the procedure itself. Alpha, integer, decimal, dates, and mixed data are available, most random in both length and content.
* Arr1Dor2DtoWorksheet can transfer either a one-dimensional or two dimensional array to a worksheet. It can be positioned at any location. It checks that an array exists,that it is allocated, and its number of dimensions in setting the transfer range.
* DispArrInImmWindow formats and displays a 2D array in the immediate window of the VBA editor. It takes account of all data length in setting the well aligned columns. There are parameters to set maximum decimal places and the choice of decimal point alignment or raw data. The layout can handle mixed columns of text and numbers, though it has best appearance when all data of a column is of the same type. The whole array's formatted output is available as a single string for external use, useful for arrays beyond 199 rows, as too big for the immediate window.
* CopyToClip is used to pass a string to the clipboard. It is used here to upload the formatted array string. The clipboard will retain the contents only until the calling application (Excel) closes. It should be noted that other clipboard procedures in this series retain their content until the Windows platform closes.
* GetFromClip retrieves the contents of the clipboard. It is used here purely for demonstration. It passes the entire formatted string of the array to a text file.
* WriteToFile opens and writes to a named text file. It completely replaces any text that it already contains. If the file does not exist the procedure makes it, in the same directory as the Excel file itself.

## The VBA Code Module

Copy the entire code listing into an Excel VBA module, and run the top procedure to test the four array transfer methods. Save the file as type xlsm. The code writes to Sheet1, and to the immediate window of the VBA editor. Further array listings will be found on the clipboard and in a text file made for the purpose.

## See Also
# Charts from Arrays
## Summary

Charts can be either embedded, where they are found in association with worksheets, or can occupy sheets of their own. The code example below makes basic charts on their own sheets. Purely to test the code, there is a procedure to fetch a selection of cells from the worksheet. Clearly, this procedure is justified only for testing since there are easier ways to make charts starting from a selection of cells. Array charting is generally most useful when data is not first written to a worksheet.
The chart procedure runs from an array. The array can contain one X series, and any practical number of Y series. However, the layout of the array is strict; the first row must contain only X data. All other rows will be treated as having Y series data in them. No heading labels can be included.
If the source data has its series in columns instead of the rows required by the chart array, then the data is transposed before the charting point. A transpose procedure is included in the code.
The code can be tested as a self-contained standard module.

## The VBA Code

Because there are too many variations of chart types to accommodate with any accuracy, only the most general properties can be considered in one procedure. As a result, the user should add any specific code to the appropriate sections.
Note that in the supporting procedures, both empty selections and insufficient selections generate errors, so a minimal error handling was added.

# Character Frequency Charts in Excel
## VBA Code Listings

At times it is useful to make an Excel chart from VBA. The code below makes a frequency bar chart based on a given string. It is shown in testing mode with a random string input. The user should replace that string with his own. There are various charting options.

# Clipboard VBA
## Summary

There are three main ways to pass text to and from the clipboard with VBA code.
* The DataObject method:
** This is perhaps the simplest implementation.
** Its main restriction is that the contents of the clipboard will be lost when the launching application is closed; generally this is not a problem when running Excel modules, but should be borne in mind.
** Some users elsewhere report bugs. See DataObject Bugs Forum for details of the bugs and one suggested fix. All of the procedures on this page are tested and work well in both Windows 7 and Windows 8.1 for Excel 2010. The DataObject method has recently been adopted for the VBA Indenter module, in this same series.
** Other methods avoid these restrictions. In the unlikely event of problems with these procedures, either of the next two methods would suffice.
** An example of the DataObject method is given in section two of this page.
* User form control methods:
** When user forms are to be displayed, then the copy and paste methods of the text box can be used. These methods work well and are well tested.
** When no user form is to be displayed, a hidden form can be used. The form with a text box, is loaded but never displayed. Then, the invisible user form's controls can still then be coded as normal. The text box must have its Multiline property set to true for most useful text transfers. It will be found best, in general,to set the form's ShowModal property to False; this allows for convenient code tracing and avoids many other confusions.
** An example of the hidden user form method is given in section four. Another example in section three, for a visible user form, shows how to track the active text box prior to copy.
* API methods:
** These methods make use of Windows libraries, and have copious declarations in their module headings. That said, they work well, and are described by Microsoft documentation as being the most suitable.
** One example of API use is displayed in section five. See Send-Information-to-the-Clipboard for more details.

## DataObject Method

* These methods make used of a DataObject . They are by far the most adaptable, since any text that can be placed in a variable can then be placed onto the clipboard using the PutInClipboard method. Text can also be brought into a VBA string variable with the GetFromClipboard method. The procedures CopyToClip and GetFromClip in the example below first send text to the clipboard, then fetch it again, before displaying the text in a message box. Set a reference to Microsoft Forms 2 in the editor options for this; if you cannot find it just add a user form to your project and it will be added to the selections.
* Reports of bugs in DataObject methods are reported elsewhere. These apply to Windows versions beyond Win 7, and are reported to involve an unusual persistence between the object and the clipboard. If difficulty is found with these methods then either the dummy userform method or the API methods could be tried.

## Visible User Form Method

The code module below provides the VBA code for a form module, (shown here as UserForm1). In it there are command button click routines for textbox Copy and Paste. To use the copy procedure the user simply selects some text then presses the button on the user form. To paste the contents of the clipboard into a textbox, the user must first place the insertion point somewhere within a textbox before pressing the requisite button.
In order to clarify which textbox is active, there is a mouse-up event for each, where a number is loaded into a module-level variable whenever a mouse is used in the box. Although this code is made for three textboxes, it can easily be extended to any number.
The code assumes that there is a user form UserForm1, with TextBox1, TextBox2, TextBox3, CommandButton1 and CommandButton2 in it. In addition, note that there is a module level variable in the code. Since the VBA code is fairly generic it applies to most MS Office applications.

## Hidden User Form Method

This code should be placed in a standard module. The project needs a user form called Temp, with a single TextBox1 set with MultiLine=true. TextBox contents are always text.

## API Method

The code below was tested on an Office 2010 version of Excel, 32 bit system, and worked well. Since that time, with 64 bit 2019 Excel, the code will not work in its current state, but needs further changes to the declarations for 64 bit use.
The following VBA code makes use of API calls, and is recommended by Microsoft in their MS Access page Send-Information-to-the-Clipboard. Such methods should overcome the current bugs in the DataObject methods for Windows 8 and 10. The code should be copied into a standard module in its entirety.

## See Also

* Send-Information-to-the-Clipboard: A clearly worded page by Microsoft showing how to use API methods for the clipboard. Although described for MS Access, it works in MS Excel just as well.
* DataObject Bugs Forum: A description of a DataObject bug for Windows versions beyond Win7.

# Simple Vigenere Cipher in VBA
## Summary

* This VBA code module runs the sixteenth century Vigenere cipher. It is used to conceal the contents of a message, and was probably used in short term military messaging . The method is an early example of an attempt to disguise the natural frequency of the language used in communication. The sender and recipient share a secret word or phrase, the so-called key, used to scramble (encrypt) and unscramble (decrypt) the message. The code does not include spaces, since these tend to assist in code breaking, and although it could have been further restricted to just capital letters, it was decided that adding integers would make it more useful.
* The code is intended for Microsoft Excel, but is easily adapted to work in other MS Office products that run VBA. For example, to run it in MS Word, the results will still display in the message box, but it will be necessary to comment-out all lines, (put an apostrophe before each), of the '''output to sheet 1'' and ''make columns fit sections''.
* Figure 1 shows the Vigenere table without integers or other characters. Figure 2, the basis for the coding, shows a similar table that includes integers and capitals, and no other characters.
* The Vigenere cipher makes use of a repeated keyword or phrase. That is to say, the key string is repeated as often as necessary to cover the message, prior to working. This can be seen in the example of Figure 1, where the very short key "BULGE" was extended to "BULGEBUL" to cover the eight characters of the message. Clearly, the extension of the key in this way avoids any fixed relationship in the encryption, especially when more complex keys are used. A very good description of the "Key Elimination" method for cracking such simplistic keys can be found in Vigenere Cipher.
* The coded version of the cipher uses a calculation to simulate the tabular method. The twenty-six letters of the alphabet and the ten integers are assigned number values between zero and thirty-five. Then, for encryption, key values are modulo-36 added to message values to make the ciphertext. For decryption, key values are subtracted from the ciphertext, again using modulo-36 arithmetic, and always producing positive values. Numbers are converted back to characters for display.

## Notes on the Code

* No userform is provided. Instead, type message and key strings, and the boolean value for the working mode, into the top procedure directly. Interested parties might well add a user form of their own.
* CheckInputs makes sure that no illegal characters are included, while procedure LongKey makes a key value equal in length to the message.
* CharaToMod36 converts each string character, of both message and key, to its set-position number. Another procedure, Mod36ToChara converts these numbers back again prior to display.
* AddMod36 performs modulo-36 addition, and subtracts 36 from numbers larger than 35 to keep the result within the set. The procedure SubMod36 performs subtraction, and adds 36 to any negative results, again, to keep the numbers within range.
* There is some latitude for improvement of the code. For example, the set could be further extended, and the key could be tested to avoid some of the flaws that are characteristic of this cipher. At present, the user must interpret the position for spaces in the decrypted results; this helps to better conceal the use of the frequently used space character. So, extend the set only at the risk of worsened performance. As mentioned before, a user form could be made to replace direct entry.
* Because repeat patterns can develop, some care is needed in coding. Clearly, a key that consists only of one repeated character would not be very secure, especially if it were the letter A. (Try it!). A good mixture of characters makes for the best key, and if the key completely covers the message without repetition, so much the better. This latter situation helps to avoid patterns that might make for easier cracking. In fact, if instead of a repeated key, a hash of the key were used, many of these pattern weaknesses might be avoided. Those who have an interest in such modifications will find hash procedures elsewhere in this series; (use base64 output). That said, care should be taken to include only alpha characters and integers from any such hash, or errors will result. (B64 strings from hash algorithms typically have three additional symbol characters to avoid, =, +, and /. )

## A Larger Vigenere Table

If all else fails, and for those who prefer manual working anyway, the table in the above drop-box may be found useful. It lists both capitals and integers. Notice that although both tables have a passing similarity, their content is quite different in places, so they are not fully interchangeable.

## A Worked Example

The following panel shows how the calculation works for the coded version. It is analogous to the adding and subtraction of character distances within a closed set. Other implementations of the manual method have included the sliding of one set of characters against another for the required distances, sometimes using concentric discs. Figure 2 can be interpreted as a listing of every possible combination of messages and keys.

## The VBA Code Module

Copy this entire code listing into an Excel standard module, save the file as a xlsm type, then run the top procedure. No user form code has been provided, so the user should enter his message (sTxt) and key (sKey) strings directly into the section identified in the top procedure. Be sure to identify whether encryption or decryption is intended with the setting of the variable bEncrypt.
Corrections: 6 April 2020; corrected a note in SubMod36; does not affect operation.

## See Also
# Error Handling
## Summary

The code module below shows one layout method for error handling. It uses a bit more space than the usual thing but has good clarity. It also includes error logging and a block for testing the code by raising errors. Only a few errors have been listed.
Notice that no formatting is done in the log writing procedure itself, and that a choice of block logs with line separation or serial logs with comma-separation are both included.

## External Links
# File and Folder Dialogs
## Summary

At times we need to access files and folders to provide input for procedures, and the code below will do this. They are not much different to the dialogs that Windows uses, and each of them works by returning a full path string to the chosen item. When a folder is selected, the returned string does not include the end backslash; the user needs to add that himself.
The two dialogs ''SelectFolder'' and ''SelectFile'' will work with both 32 bit and 64 bit version of MS Office, but the API procedure ''BrowseFolder'' is not intended for 64 bit working; it works only in 32 bit systems. For completeness, another version of the API for 64 bit systems has been added at the foot of the page. Although these two look a bit similar, it is important to choose the right one for your version of MS Office. All three can be run from the test procedure.
Just copy the entire code listing into a standard module for use, and comment out the API version that is unwanted, assuming that an API is used at all.

## VBA Code Module

The default file-type listing that opens in SelectFile is decided by which of the Filters.Add code lines appears first in the sequence. For example, to have the All Files as your prefered listing, just move that line so that it immediately follows the Filters Clear line. Of course, the listing can also be changed by selecting the drop menu while the dialog is open.

## External Links

* BrowseFolder : Chip Pearson's page on the API folder browser.
* FileDialog Properties and Methods: The Microsoft documentation for the FileDialog selection methods. It includes a code panel showing the use of file multi-selection.

# Recursive File Listing of Folders
## Summary

* Recursive listings are tricky, and it is found to be difficult without module or public declarations of some sort. This version although a bit clumsy will perform as expected for files that can be accessed.
* A public variable is used as a counter to keep track, between iterations, of the numbers of files found, since Microsoft advises us that static variables are not usually used with recursion. The VBA code is not specific for any particular Office application, so would work in say MS Excel or MS Word etc.
* The user might need to introduce more filtering; for example, to exclude certain file types, or to avoid those of zero size. A comment in the code listing shows where such a code function could be added to the existing condition.
* Because the array is public, it can be accessed from any other module for its further processing or output. Copy the code entirely into a code module, and modify the folder and recursion condition to your own values.
* My Documents versus Documents. There are four virtual folders in Libraries, My Documents, My Music, My Pictures, and My Videos. When the Windows Explorer's Folder Options forbid the display of hidden files, folders, and drives, the correct locations are returned by various folder selection dialogs, namely Documents, Music, Pictures, and Videos. When hidden folders are permitted, then dialogs and listings will attempt to make use of these virtual paths. Access violations will result. To avoid undue problems, check that your folder options are set not to show hidden files or folders. This procedure avoids these folders altogether, but access violations can be avoided, provided that hidden files are allowed to stay hidden.

## VBA Code
# File and Folder Utilities
## Summary

* This first set of utilities concentrates on the basic FileSystemObject set; that is, the set used to find whether or not a file or folder exists, what their sizes are, and whether or not they have a particular attribute. A basic path parsing procedure is also provided. All of these procedures need a reference set in the VBA editor to Microsoft Scripting Runtime
* No universally useful code was found for testing for open files. Although many procedures exist, they all fail in some way, usually failing to identify open text or image files, or Office files that are marked as read-only. The basis of the problem is that many such files in Windows do not lock when opened by a user, so procedures that attempt to detect the open state by trying for sole access, cannot do so. Any reader with a universal solution is, as always, invited to comment.

## VBA Notes

At times it is useful to know whether or not a file or folder has a particular attribute, for example, to avoid hidden or system files in listings. The procedure HasAttribute does this, taking a path to the file as parameter and a short-code to identify the attribute of interest. However, the attribute bundle is delivered with all of the attribute number values added together, so this type of test, like other enumerations that involve constants (eg; the message box types), makes use of the AND function to split the bundle.
For example: (See procedure HasAttribute below.) Assume that the attribute bundle from GetAttr equals 37
and that we are testing for the "system" attribute only ("S") with vbSystem = 4. Now, for numbers, the AND operator performs a bitwise AND on each column, so gives:
0100101$_2$ = 37$_{10}$ = vbArchive + vbSystem + vbReadOnly 0000100$_2$ = 4$_{10}$ = vbSystem _______
0000100$_2$ = 4$_{10}$, interpreted by boolean variables as True since it is non-zero
That is to say, the "system" attribute is present in the attribute bundle. If the "system" attribute were not set, then the result would have been all zeros
It is important to note that the returned value tests only one attribute at a time; that is to say, although a file returns true for for read-only ("R"), it might also have other attributes that are not tested. If users would rather have all of the file or folder attributes returned in one string, some work might be done to concatenate the result codes.
An example of file path parsing is given in the ParsePath procedure. The example uses the Split function to place all of the backslash separated terms into an array, then recombines them to make the path. A similar method, split on the dot is used to make the file name and suffix.

## External Links
# Font Utilities
## Summary

* This page lists VBA procedures that are mainly to do with fonts. That is to say, how VBA handles fonts.
* The function GetTextPoints finds the width of text in points. A label on a userform extends when loaded with the string. The width is then read from the control. The userform and its contents are loaded but never shown. Despite its seeming lack of elegance, this method is perhaps the simplest way of getting the fitting width for text, correct for any variation in the font. The function is useful in the precise sizing of controls for complex layouts, such as tables within text boxes.
* The procedure ListAllExcelFonts lists Excel's fonts on a worksheet. It makes use of GetTextPoints. While listing whether or not the font is monospaced, it also makes a sample of test text in each font. It also lists the width in points for the sample text in each font. Normalizing these width figures might be more useful but it is unclear as to which font is best to represent the standard. As ever, informed comments would be useful.
* The procedure FontExists tests whether or not a font exists. It returns true in the function name if the parameter font name exists, otherwise it returns false. Run testit to try the function.

## Font Tests

The function GetTextPoints can be used to determine whether or not a font is monospaced. Although at first sight it would appear suitable for determining the presence of kerning, the userform control used to measure the width of text does not kern the text applied to it in any case. As such, kerning will always be found to be absent. The tests, whether used visually or in an automated mode, compare the lengths of selected strings. If the strings of the first pair below are the same length, then the font is monospaced. Elsewhere, if kerning had been applied, then the strings of the second pair would be different in length.
Monospace test strings: IIIIIIIIII HHHHHHHHHH
Kerning test strings: for completeness only.
AAAAATTTTT ATATATATAT

## External Links
# The Elusive Button
## Summary

These VBA code modules are intended for Microsoft Excel. They show how to make a button that continually escapes attempts to click it. The code needs only a user form called UserForm1, and two command buttons, CommandButton1 and CommandButton2; The code will size the controls and the form itself.

## Code Notes

* The MouseMove event applies to specific controls; in this case a CommandButton. It fires whenever the mouse moves anywhere within the area of the control, and is used here to move the control before the user can select it.
* The code proposes random direction and shift amounts, then checks to make sure that the resulting shift will stay on the form, before moving the control. When a proposed shift is rejected, the fact that the mouse is still moving ensures that another event will still fire before a selection can be made. Selection HAS been known to happen, perhaps when there is an unlikely number of rejected shift values; a click procedure has been included to note the fact, just in case.
* The VBA help page for this event has an impressive set of options, as yet unexplored here.

## The ThisWorkbook Module

Copy this code into the ThisWorkbook module of the project. Save the file as xlsm type. It will run whenever the file is opened.

## The Userform1 Module

Copy this code into the UserForm1 module. It can be accessed by double-clicking the userform in design mode. Save the file, making sure it is xlsm type. The code is run by opening the file or by clicking the above Open event procedure in the ThisWorkbook module.

### Code Modifications

Added colors and overlaps, 2 Feb 2019 Added notes to code, 2 Feb 2019

## See Also
# String Hashing in VBA
## Summary

* The VBA code below generates the digests for the MD5, SHA1, SHA2-256, SHA2-384, and SHA2-512 hashes; in this case for strings.
* A hash is an output string that resembles a pseudo random sequence, and is essentially unique for any string that is used as its starting value. Hashes cannot be easily cracked to find the string that was used in their making and they are very sensitive to input change. That is to say, just a change in one character at the start will produce a completely different output. Hashes can be used as the basis of pseudo random character tables, and although not purely random, such methods can produce output quality that is at least as good as the in-built Rnd function of VBA.
* The use of a hash allows programmers to avoid the embedding of password strings in their code.
** The memory space occupied by an application can be read with special utilities, so passwords might be found in code, then used in a normal user login. Instead, the hash of the password is listed in code, and the password is hashed for comparison only during a logon. This avoids access to the application via the conventional user route, since any hash that is found could not be reverse engineered to obtain the value needed at the user interface. This method assumes that the code cannot be run by the intruder at any location other than the logon device, and that they are unable to change the memory contents.
** If a hacker can change the memory contents, then a common exploit is to change the hash in memory for one of their own; one that corresponds to a password that they can use at the user logon interface. The counter action against this attack is for all of the logon files to be encrypted with the user's officially issued password. Then, even if the hash is changed, the files needed for the logon attempt cannot be decrypted for use.
* Hashes can also be made from entire files, and the code for doing so differs only slightly from the string hashing versions given below. The main difference in file hashing is that the file is first turned into a string before using conventional techniques. Code is given elsewhere in this series for file hashing. String hashes will produce an output even when the empty string is used as a starting point, unlike for file hashing where an empty text file can raise errors.
* This VBA code is not specific for any one application, so it will work in any of say, MS Word, MS Excel, or MS Access. These code versions include options for base-64 output or hex.

## Code Listings
### Notes on the Code

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.
The VBA code below generates the digests for the MD5, SHA1, SHA2-256, SHA2-384, and SHA2-512 hashes, for strings, in either of the hex or base-64 output formats. These codings each make use of MS Office's built-in functions, and provide consistent results. It has been noted that original implementations elsewhere for the same digests can differ widely in their outputs. Only one example has been given with a seed or salt parameter (StrToSHA512Salt), and it should be noted that the HMACSHA512 class output differs from the SHA*Managed class hashes given in the remainder. The Managed classes give the best widely reported results. Note the VBA references required for correct operation. A reminder of these is given in some procedure headings.
In each case, coders can find the unmodified hash values in the bytes array and at that point they are in 8-bit bytes, that is, the numbers that represent the ASCI code as it applies to a full eight-bit, 256 character set. The code that follows the filling of the bytes array in each case decides which version of the ASCI character set to deliver. For a hex set of characters, 0-9, and A to F, the total bit set is broken into double the number of four-bit bytes, then returned for use. For the base-64 set, lower case letters, upper case letters, and integers mainly, six bit characters are made for output. These two sets are the most useful here, since they consist of commonly used characters. The 128 and 256 ASCI sets are too full of both exotic and non-printing characters to be useful. For each hash version its bit count is a constant, so the length of its output will vary according to the chosen type.
If your data is in ANSI, you will get different results between Excel/ACCESS and SQL Server when using T-SQL HASHBYTES function for characters code over 128. To solve those differences use StrConv instead of .GetBytes_4

## See Also

* File Hashing in VBA : A companion page in this series that lists code for single file hashing. Combine this with file listing code for extensive hashing of files.
* Folder Hashing in VBA :Another companion page that makes recursive folder hash listings, and logs. Uses up to date hash algorithms, but limited to files no larger than about 200MB.

# File Hashing in VBA
## Summary

* This section contains code for making file hashes, that is, hashes of entire files.
** Several algorithms are provided, with output options for base64 or hex. The VBA code below generates the digests for the MD5, SHA1, SHA2-256, SHA2-384, and SHA2-512 hashes.
** The code is made for single files, but the code given on an adjacent page, Folder Hashing in VBA, can be used for recursive hash listings, again with a choice of hashes and output options.
** String hash routines are given in another section.
** In general these hashes do not make use of a seed value, but to illustrate the method, the code contains one such example, (FileToSHA512SALT). Please note that its output differs from that of the SHA512Managed class. A note exists in the respective procedure in case other salted (seeded) inputs are of interest.
** These listed algorithms can hash any single file up to about 200MB (Mega Bytes) in length, beyond which an out of memory error will be generated in GetFileBytes. Specific tests found that hashes work well for a 200MB zip file but fail for a 500MB zip file; the exact break point is unclear. For files larger than 200MB, other facilities exist.
* Large file hashing, say beyond 200MB is best done with other tools. Four such examples are mentioned here:
** Microsoft's FCIV utility, is free download. It is a command-line application, capable of hashing both single files and whole folder trees. It handles large files with ease, but only for MD5 and SHA1 hashes. It sends both base64 and HEX outputs to the screen but only b64 output format to a file. Prepared files can be verified against any new run, but results only to the screen. It is a bit tricky to use, even with their instructions, so the pages Running the FCIV Utility from VBA and File Checksum Integrity Verifier (FCIV) Examples might be found of use to the novice. So far, Microsoft have not extended the coding to include contemporary algorithms.
** PowerShell in Windows 8.1 and above, can make large single-file hashes, using all of the MD5. SHA1, SHA256, SHA384, and SHA512 algorithms. It produces output on the screen only, though the output can also be piped to the clipboard for pasting as required. There are no simple options for hashing a folder or for output to an xml file. For completion, an example of its use is given in File Checksum Integrity Verifier (FCIV) Examples. In Windows 10, hashes can also be obtained at the command prompt with certutil -hashfile <full path to file to hash> MD5, though size limitations are unclear. (Change md5 to sha1, sha256, or sha512, etc).
** An external application that can handle large files is MD5 and SHA Checksum Utility. It is a stand-alone application, and a basic version is available as a free download. It produces MD5, SHA1, SHA2/256, and SHA2/512 hashes for single files. The outputs are in HEX and are displayed together on a neat user interface. A more complex commercial version is also available.
** FSUM Fast File Integrity Checker is another free, external application for command line use. It resembles FCIV in many ways but includes up to date algorithms. (MD2, MD4, MD5, SHA-1, SHA-2( 256, 384, 512), RIPEMD-160, PANAMA, TIGER, ADLER32, and CRC32). In addition to large file HEX hashes it can carry out flat or recursive folder hashes. The code to enter is not identical to that of FCIV but a text file is provided with examples in its use. The web page FSUM Fast File Integrity Checker has the download and other details, though the text file fails to mention that results can be easily piped to the clipboard with |clip. Although a graphical interface exists elsewhere, the command-line application has been found the most stable..
* The permissions for files need to be considered when attempting hashing. Hashing has to access files to obtain the bytes that they contain. Although this does not involve actually running the files, some folder and file types might be found locked at run time. In fact, this type of access is the main difference between string hashing and file hashing. Whenever files are accessed, error handling tends to be needed. It is assumed here that the user will add his own error-handling, or that he will go-around files that are troublesome before the hashing attempt. Users should know that the code cannot handle an empty text file; for example, a Notepad file that has been saved without any text in it. The GetFileBytes routine will error. A message and exit will be produced if an empty file is encountered, as for a file in excess of 200MB.
* User files and folders have few restrictions. The empty file problem apart, those who want to access user files in folders that they have made themselves will not usually have any problems, and interested parties should know that there is a recursive folder hashing module in another section of this series that might be of related interest. Folder Hashing in VBA also contains notes on how to avoid virtual folder problems with music, video, and other Microsoft libraries.
* Hashing is concerned only with the content of a file, and not its name, or other file details. This means that duplicates of files under any name can be found by comparing their hashes. In secure systems with deliberately confusing file names, this means that a very long file list could be hashed until a searched-for hash value is found, rather than depending on a less secure file name to find it. Alternatively, file names are sometimes just the file's hash value, so that hashing can reveal any error or illegal change. In such a case a hacker might change the file then name the file with a corresponding hash, but he does not know the required hash algorithm or private string to use, so changes will always be detected when the owner runs his own hash verification.

## Code Listings

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.

### Modifications

* Added default code for transfer of results to the clipboard, 11 Sep 2020
* Set file selection dialog to open with all-file types to be listed, 25 July 2019
* Added file selection dialog, and file size limits, 17 Jun 2019

### Using Built-in Windows Functions in VBA

The code to make hashes of STRINGS and for bulk file hashing is given elsewhere in this set. The panel below bears code that is virtually identical to that for strings, but with only slight modification, is used to make hashes of single whole FILES. The user provides a full path to the file via a selection dialog as the starting parameter. A parameter option allows for a choice of hex or base-64 outputs. Functions are included for MD5, SHA1, SHA2-256, SHA2-384, and SHA2-512 hashes.
For frequent use, the selection dialog is most convenient, though the code contains a commented-out line for those who intend to type the file address into the procedure; simply comment out the line not needed.
In each case, coders can find the unmodified hash values in the bytes array and at that point they are in 8-bit bytes, that is, the numbers that represent the ASCI code as it applies to a full eight-bit, 256 character set. The code that follows the filling of the bytes array in each case decides which version of the ASCI character set to deliver. For a hex set of characters, 0-9, and A to F, the total bit set is broken into double the number of four-bit bytes, then returned for use. For the base-64 set, lower case letters,upper case letters, and integers mainly, six bit characters are made for output. These two sets are the most useful here, since they consist of commonly used characters. The 128 and 256 ASCI sets are too full of both exotic and non-printing characters to be useful. For each hash version its bit count is a constant, so the length of its output will vary according to the chosen type.
As a general point; message boxes do not allow copying of their text. If copying is needed, replace the message box with an input box, and set the output hash to be the default value of the box. Then it can be copied with ease. Alternatively use the output of the Debug.Print method in the immediate window. A procedure has been included to overwrite the clipboard with the results: If this is not inteded then comment the line out in the top procedure.

## See Also

* String Hashing in VBA : A companion page in this series for those who want only to hash strings.
* Folder Hashing in VBA :Another companion page that makes recursive folder hash listings, and logs. Uses up to date hash algorithms, but limited to files no larger than about 200MB.
* Running the FCIV Utility from VBA: How to use the Microsoft fciv.exe command line utility to make MD5 and SHA1 file hashes from VBA. The MS utility, although awkward to use, allows hashing and verification of entire folder trees.
* File Checksum Integrity Verifier (FCIV) Examples: More details on how to use the FCIV utility for those without much experience working from the command line prompt.

## External Links

* MD5 and SHA Checksum Utility: a external site's free application to simultaneously display MD5,SHA1,SHA256,and SHA512 hashes of single files (Hex only). Includes a browse-for-file function and drag and drop to make life easy. This utility can also be used to hash large files; author-tested for a 500MB file.
* FSUM Fast File Integrity Checker : The basic command-line version of the FSUM hasher download.
* FSUM GUI : A graphical interface download site for the FSUM utility. This allows file browsing, drag and drop, and other facilities to simplify the otherwise command-line working.

# 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.

## External Links
# Running the FCIV Utility from VBA
## Summary

The Microsoft FCIV Utility, the File Checksum Integrity Verifier , is a free downloadable zipped package that allows a user to produce both SHA1 and MD5 hashes for any single file, a flat folder, or recursively for all files and folders. It can export its entire results package to a nominated .xml file. It can also conduct verification of file sets against a previously saved listing. It is used from a command prompt, but can be run using the Shell function from code like VBA, or from a batch file. For further reading in its use see: Availability and description of the File Checksum Integrity Verifier utility.

## Making File Hash Listings
### FCIV Hash Run at the Command Line

For completion, the command line code here will make an XML file of SHA1 hashes of the entire Documents folder. Omission of the xml term and the path that follows it will result in a screen listing.
Notice the need for double quotes for paths that contain spaces.
The fciv utility is assumed here to reside in the FCIV folder.

### FCIV Hash Run from VBA

The Shell function in VBA has no Wait feature, so the Shell line is best as the last. That is to say, even while the Shell command is still processing, it will pass control to the next line in the procedure that contains it before it is done; so the procedure's end will otherwise interrupt the Shell function and the process is likely to fail. The quotes are also a little different in this case from the usual VBA expectation. Note that all of the paths have been enclosed in two sets of double quotes and that the entire command line itself is then enclosed in one additional set of double quotes. Assuming that the fciv.exe has been downloaded and installed as shown, this code line exports all of the hash strings for every file in the users Documents folder, and all of its subfolders, to the file myhash.xml. An exclusion file path could also have been added.
Notice that the use of VBA has some limitations, in that although an output can be made to a file with great success, verification output is limited to the command line processor. See examples on the page File Checksum Integrity Verifier (FCIV) Examples.

## See Also

* File Checksum Integrity Verifier (FCIV)_Examples : Detailed examples of how to run the fciv utility, from both the command line and VBA. There are additional notes for the use of the command prompt screen.

## External Links

* Availability and description of the File Checksum Integrity Verifier utility : The Microsoft page on fciv, though a bit sparse in description.
* Folder Hashing in VBA :Another companion page that makes recursive folder hash listings, and logs. Uses up to date hash algorithms, but limited to files no larger than about 200MB.

# Use Log Files from VBA
## Summary

At times it is useful to write strings to a text file from VBA. For example, for listing files, their hashes, or for simply logging errors. Text files are here intended to mean files with the .txt suffix. There are several procedures listed in the code module for both writing and reading such files.

### Writing to Text Files and Logs

* The procedure SendToLogFile APPENDS a string to a text file. The user optionally selects his own path and file name, but there is no OVERWRITE choice with this method. If user parameters are not given then the defaults are used. This procedure places the parameter string in line with a time date string, with each record entry on a new line.
* The procedure LogError1 is intended to APPEND log errors, and is an example of the Print# statement. It is assumed here that the log file will always be placed in the same folder as the calling Workbook. As such, no path check is needed, and the minimum of coding applies. All formatting of the parameter text is assumed to be done externally. Readers can find format details for Print# in VBA help, and might also care to compare the advantages of using the Write# statement instead.
* The procedure LogError2 is also intended to APPEND log errors and performs the same task as LogError1. It is however an example of the OpenTextFile method of the Scripting object. This procedure needs a reference in the VBA editor to Microsoft Scripting Runtime. Notice that this log will write every successive record into the first line unless vbNewLine characters are included at the end of the parameter string itself.
* Procedure WriteToFile REPLACES any existing text, as opposed to appending it to any existing entries.
* There are conventions in logging. Logging with a text file (.txt) means placing each record on the same line with the individual fields separated by a single tab character. The number of fields is the same for each record. Another convention is to use a comma-separated file format (.csv) where the fields are separated by commas instead of tabs. Both of these formats can be imported into MS Office applications,though users should pay particular attention as to how different log writing methods handle quotes.

### Reading Text Files and Logs

* VBA can also read text files into code for processing. However, once the notion of reading files is introduced, the choice of writing formats becomes more important. In addition, file reading can place more demands on error handling, and testing for path integrity.
* The procedure GetAllFileText returns the entire contents of a .txt file . Readers should first confirm that the text file exists. File utilities elsewhere in this series would suit this purpose.
* The procedure GetLineText returns an array of text file lines. The same comments regarding early file checks also apply in this case.

## VBA Code
# Message Boxes
## Summary

This code block contains a message box function for YES, NO or CANCEL.

## External Links
# Input Boxes
## Summary

This code block contains an input box function. It includes a number of fairly common validation routines that are selected within the main procedure.

## External Links
# Pseudo Random Repeated Substrings
## Summary

This page describes some matters that apply to the Rnd function of VBA. In particular it illustrates that repeated substrings can result when the Randomize function is wrongly positioned inside the same loop as Rnd, instead of before it.

## The VBA Rnd Function

* The Rnd function is pseudo random, as opposed to true random . True randomness is found rarely, one notable example being the sequence of data that can be derived from white noise. White noise, like the radio noise from the sun or perhaps the unintentional noise in a radio or other electronic device, has a fairly uniform distribution of frequencies, and can be exploited to produce random distributions of data; also known as linear probability distributions because their frequency distributions are straight lines parallel to the horizontal axis.
* Pseudo randomness can be obtained with a feedback algorithm, where a sequence of output values of a function is fed back and assists in the making of the next part of an output stream. These are referred to as pseudo random number generators (PRNG). Such a process, although complex, is nonetheless determinate, being based entirely on its starting value. Such generators, depending on their design can produce long sequences of values, all unique, before the entire stream eventually repeats itself.
* A PRNG output stream will always repeat itself if a long enough set of values is generated. The Rnd function in VBA can generate a sequence of up to 16,777,216 numbers before any one number is repeated, at which time the entire sequence itself is repeated. This is adequate in most cases. The Rnd function has been described by Microsoft as belonging to the set of PRNGs called Linear Congruential Generators (LCG), though it is unclear as to whether or not the algorithm has since been modified.
* The Rnd function is not suitable for large tables or for cryptographic use, and VBA itself is quite insecure in its own right. For given starting values the generator always will produce the same sequence. Clearly, if any part of the stream is known, this allows other values in the sequence to be predicted, and this state of affairs is insecure for cryptographic use. Perhaps surprisingly, modeling methods that make much use of random values need even longer unique sequences than that produced by Rnd.
* The exact coding of the Microsoft Rnd function is not available, and their descriptive material for it is quite sketchy. A recent attempt by me to implement the assumed algorithm in VBA code failed because of overflow, so those who intend to study such generators in VBA need to use another algorithm. Perhaps study of the Wichmann-Hill (1982) CLCG algorithm, that can be implemented in VBA would be a better choice. A VBA implementation, (by others), of the Wichmann-Hill (1982) algorithm is provided in another page of this series, along with some simpler generator examples.

## Worst Case for Rnd Substrings?

* A well designed PRNG stream consists of unique numbers, but this applies only to the designer's unfiltered set of numbers in the range from zero to unity, [0,1]. As soon as we start to take some values from the stream, and ignore others, say to make custom outputs, the new steams will take on different characteristics. The combination of cherry-picking elements from the natural sequence and the mapping of a large set to a very small set takes its toll. When observing the new set, the count of characters to the cycle repeat point shortens, and the number of repeated substrings increases throughout the set.
* The code listing below allows checking of a Rnd stream for substrings, using preset filter settings, eg; capitals, lower case, integers, etc., and in addition, includes a similar generator based on a hash for those who would like to compare it.
* The repeat substring procedure is quite slow, depending as it does on the location of repeats. The worst case is for no repeats found where the number of cycles becomes maximum at (0.5*n)^2, the square of half the number of characters in the test string. Of course the smallest number of cycles is just one when a simple string is repeated, eg; abcabc. Clearly, an increase of string length by a factor of ten could increase the run time by a factor of one hundred. (1000 characters in about 2 seconds, 2000 in 4secs, 10000 in 200secs, is, so far, the best timing!).
* Coding layout can affect the length of repeated substrings too. The reader might compare the effect of placing the Randomize function outside the random number loop, then inside the loop, while making an output of only capitals. (See code in MakeLongRndStr). In the past the repeat strings have worsened considerably when placed within. The code as listed here to test Rnd with 1000 samples of capitals, no use of DoEvents, and Randomize wrongly placed inside the loop, will return a repeat substring of up to four hundred characters for this author. Increasing the code lines in the loop, affecting the time (?) for each iteration of the loop to run, also affects the length of any substrings.

# A PRNG for VBA
## Summary

* A pseudo-random number generator (PRNG), if run for long enough, generates a characteristic sequence that is based on its algorithm. This sequence repeats forever and is invariant. The Rnd function of VBA, if placed in a loop without a parameter, and without making use of Randomize at all, will generate 16,777,216 values between zero and unity, then start again at the beginning, making a repeating sequence with a cycle length of 16,777,216. The only option for the user is to choose the starting point within that one sequence. This is done by choosing a start value or seed. In the case of Rnd, the start value is chosen in two ways: by default, using the system timer, or with a user-set number. Again, the start parameters that are set by the user do not make new sequences, they just decide which bit of that one long sequence will be used. Linear Congruential Generators (LCG), the type used by Microsoft's Rnd function are described in detail at Linear congruential generator.
* The maximum cycle length for a single stage LCG is equal to its modulus. For combined generators, the maximum cycle length is equal to the least common multiple of the cycle lengths of the individual generators. A well-designed generator will have the maximum cycle length and consist only of unique values throughout its sequence, but not all generators are well-designed. The above link describes the design values required to make an LCG with a maximum cycle length over all of its starting values.
* The code module below contains the Wichmann-Hill (1982) CLCG (combined LCG) in VBA and is fully functional. It is called RndX and is used in conjunction with its own RandomizeX. It has a much longer repeat cycle than Microsoft's Rnd. A summary of the most useful settings for RndX is given, with additional details for those who need them in a drop box. Sadly, this author lacks the tools and knowledge for any serious testing of number generators, so the offerings below are likely to be of interest only to beginners.
* Long-cycle generators are awkward to study in Excel. The cycles of both Microsoft's Rnd and the user function RndX are much too long to write a complete cycle to a single worksheet column. The solution is either to list only parts of long streams or to make a number generator with a cycle short enough for a full listing. Listing in a single column this way allows confirmation of the repeat cycle length, then after trimming the rows to a complete set, counting rows after the removal of duplicates will confirm for the skeptical that all of the values are unique. A module is included with procedures to list one section of the Wichmann-Hill implementation, that fits into about 30269 rows or so, and another with a very simple generator for further testing, that fits into just 43.

#### Microsoft's Rnd algorithm

Microsoft's Visual Basic for Applications (VBA), at present uses a linear congruential generator (LCG) for pseudo-random number generation in the Rnd function. Attempts to implement the Microsoft algorithm in VBA failed owing to overflow. The following is its basic algorithm.
Similarities will be noticed between Microsoft's Rnd and the one below, described by Wichmann-Hill (1982), in which a sum of three LCG expressions is used in the production of each output number. The combination of expressions gives RndX, with the coded values, its much improved cycle length of:

## VBA Code - Wichmann-Hill (1982)

A reminder about module level variables may be in order. Module level variables hold their values between procedure runs. In fact they will retain values until the VBA is no longer used at all or the code is edited. The code has been laced with resets for these variables, to ensure starting with intended values, as opposed to old stored ones from the previous top procedure runs.
On a cautionary note; although this algorithm has improved properties over the resident Rnd, the applications on which these generators are run are not particularly secure. Consider also that the output of all LCG coding is entirely predictable if the starting value is ever known. In fact, if any part of such a stream is known, then it is possible for those who intend harm to find the entire stream by comparing it with stored values. These facts when taken together limit the use of such a VBA implementation to study or non-critical applications.
That said, these are likely to be the most useful parameter configurations: In each case RandomizeX should only be called once, before and outside any generator loop that contains RndX. This advice also applies to the Microsoft function Rnd and its companion Randomize.
* To produce outputs with an unpredictable start point, and a different start point each time it is run:
** Call RandomizeX without any parameter before calling RndX, also without any parameter. This uses the system timer.
* To produce outputs from a large set of start points, repeatable, and chosen by a user parameter:
** Call RandomizeX with any numeric parameter before calling RndX without any parameter. Changed RandomizeX parameter values result in different start points of the standard algorithm stream.
* To produce an unpredictable, single value, different each time it is run:
** Call RandomizeX without any parameter before calling RndX with a parameter of zero. This uses the system timer.
* To produce a repeatable single value, related to, and chosen by a user parameter:
** Call RandomizeX with any numeric parameter before calling RndX with a parameter of zero. Changed RandomizeX parameter values result in different values that are peculiar to each parameter.
* Refer to the drop box below for a complete tabulation of the parameter settings and their outcomes.
PRNG RndX and RandomizeX Parameter Details :
RndX and RandomizeX Parameter Details :
| RandomizeX parameter | RndX parameter | Behaviour of function (assuming coding is to produce a sequence) |
| none. | none | PRNG stream determined by runtime sampling of the computer’s system timer. Stream uncertain. |
| none | positive | PRNG stream determined by runtime sampling of the computer’s system timer. Stream uncertain. Positive parameters of RndX do not affect it at all. |
| none | negative | One number, repeatable, and each one different and depending on the value of RndX parameter. Example; RndX(-3) leads to 0.05079271 |
| none | zero | One number, repeatable, decided by runtime sampling of the computer’s system timer; Example; sequence is 0.1741…, 01741… |
| numeric$^2$ | none | PRNG stream, repeatable, and each one different and depending on the value of RandomizeX parameter. |
| numeric | positive | PRNG stream, repeatable, and each one different and depending on the value of RandomizeX parameter. Positive parameters of RndX do not affect it at all. |
| numeric | negative | One number, repeatable, and each one different and depending on the value of RndX parameter. The RandomizeX parameter value has no effect at all. Example; RndX(-51) leads to 0.8634… |
| numeric | zero | One number, repeatable, and each one different and depending on the value of RandomizeX parameter. Example; RandomizeX(2346) leads to 0.2322… |
| function not used | none | Default PRNG stream, repeatable, and always same. Example; sequence is 0.8952…, 0.1114…, 0.9395… |
| function not used | positive | Default PRNG stream, repeatable, and always same. Example; sequence is 0.8952…, 0.1114…, 0.9395… |
| function not used$^1$ | negative or zero | One number, repeatable, and each one different and depending on the value of RndX parameter. Example; RndX(0) = 0.8694...: -5 = 0.0846… |
The code in this section should be saved as a separate standard module in Excel.

## Simpler Tests of PRNGs

The code module below contains a stripped down version of the Wichmann-Hill (1982) algorithm, in fact using only the first of its three calculated sections. It will make several complete streams of values on Sheet1 of the workbook in which it is run, using different start values. Notice that the first values are all repeated at row 30269, as will the whole stream if extended. After producing the list, use the spreadsheet's functions for column sorting and the removal of duplicates to see that each column contains the appropriate number of unique entries. An even simpler generator with a repeat cycle of just 43 is also included that might make study more manageable, and the cycle of Microsoft's Rnd can be seen to repeat at 16777216 (+1) by running TestMSRnd.
The code in this section should be saved as a separate standard module in Excel.

## References

* Wichmann, Brian; Hill, David (1982), Algorithm AS183: An Efficient and Portable Pseudo-Random Number Generator, Journal of the Royal Statistical Society. Series C

## See Also

* Wichmann-Hill CLCG: A Wikipedia article on the specific combined linear congruential generator in question.
* Linear congruential generator: A good Wikipedia description of the conditions for a full cycle.

## External Links

* How Visual Basic Generates Pseudo-Random Numbers for the RND Function: Microsoft kb231847 knowledge base item

# A Pseudo Random Character Table
## Summary

This code module is intended for MS Excel. It makes a pseudo random table of characters, integers and capitals in this case, on Sheet1. A new and different table is made each time the procedure is run.

## The Table

* Copy the code into a standard VBA module in Excel, and run the procedure MakePseudoRandomTable to make a table. As shown, Sheet1 will be overwritten.
* The output uses a monospaced font, Consolas, for the clearest layout and type. In addition to ensuring a neat layout vertically and horizontally, monospaced tables allow the reading of sequences on a diagonal, so greatly extend their usefulness.
* Adjust the size of the table by changing the values nRows, and nCols in the code heading, and if necessary insert the name of the sheet to use. The code will add numbered row and column headings and will add these to each page that is displayed or printed.
* If an exact number of columns and rows is needed, adjust the margins for the sheet, and perhaps the font size until the required result is obtained.
* The proportion of integers to capitals is just 10/36, but is easily changed in code with a little effort.

## External Links
# Listing Prime Numbers
## Summary

This module implements the Sieve of Eratosthenes method for the listing of prime numbers. It is made to run in Microsoft Excel as a standard VBA module. It lists the prime numbers found between unity and some parameter integer value, on Sheet1 of the Workbook, and makes use of a message box for short listings.
* Overflow is a problem for such procedures, but provided that the input parameter is kept within a few millions or so, overflow is unlikely.
* The method although simple is quite slow, since even to test one single value, the entire sequence of multiples (2,3,5,7,...n) must be completed. Large values of input will take several minutes to complete. A faster approach is to test only those factors that are smaller than the square root of the input value; this modification is used in the procedure GetPrimeFactors.
* Note that the procedure will clear any contents of Sheet1 before each listing.
* An animated GIF found in Wiki Commons is included in Figure 1 to illustrate the method.
* GetPrimeFactors and its utility DecMod list the prime factors of a supplied integer. It is written for the decimal subtype, and so it handles inputs of up to 28 full digits, (assuming all nines). The time to complete varies greatly, depending on how many primes are found. There is one peculiarity noted; for example, with an input of 23 nines the answer takes a very long time, but for 28 nines it takes just fifteen seconds or so. Other values like 20, 21, and 22 nines, and so on, are virtually instantaneous. The use of a string for input in the test procedure testGetPrimeFactors is simply to prevent Excel from truncating the displayed input integer, and has no bearing on the method used; it is not string math here; just a decimal subtype loop.

## See Also
# Big Number Arithmetic with Strings
## Summary

* This VBA module is intended for Microsoft Excel but can run with minor changes in any of the MS Office applications with a VBA editor.
* The data types of VBA prevent big number calculations. That is to say, beyond twenty or thirty digits, and even then much care is needed to avoid overflow. Strings have few such restrictions, being limited in most cases to the size of the memory space of the application.
* The code module below includes most of the basic arithmetic functions without any size restriction.
* The work is not by this author, but credit should be given to Rebecca Gabriella's String Math Module notes at Big Integer Library. This author has made only cosmetic changes to the work, and added a test procedure to illustrate its use.

## Code Notes

* The module includes functions for the following: Addition, subtraction, multiplication, and division, all using integer strings. Also included are conversion functions to restore base10 from some other base, and to produce a new base from some existing base10 input. Other support functions such as RealMod are also included.
* No output code to the sheet has been provided. Owing to Excel's habit of truncating numbers, even if they are strings, users who want to use the worksheet should concatenate an apostrophe before the display string to prevent this happening. The apostrophe will not be displayed. It is unclear how else this device affects the subsequent use of the numbers.
* A remainder after division is produced. It can be found as sLastRemainder, and is public.
* Users who install code in MS Access should change the Option Compare Text to Option Compare DataBase. The former is intended for MS Excel.

## Added Big Math Fuctions

* Factorial makes use of Multiply and other integer math functions of the main module. It is of course fairly slow but is not otherwise limited in any practical way. The code has been arbitrarily limited to calculating values up to 1000!, but this can be adjusted by the user. DoEvents is important here, since it allows breaking the run if an unwise attempt is made. Reciprocal factorials and negative factorials are not handled here.
* IntStrByExp raises integer strings to an exponent. Again, negative exponents cannot yet be handled in this procedure, though the number to raise can take a negative value.

## External Links

* Rebecca Gabriella's String Math Module: Big Integer Library for VBA (Visual Basic for Applications).

# Excel Sheet True Used Range
## Summary

* This code listing is for Excel. The procedure GetUsedRange returns the true used range of the Worksheet in the function name. An example is also given below of its use in the procedure WorkRangeInArray. It can typically be used to find the next writing position on a worksheet, but in any case returns all of the cell limits on each run.
* Reports on various internet sites describe problems with the built-in UsedRange function. The problem types, apart from errors of understanding, seem to be divided between issues concerning the number of cells scrolled and errors in reporting the used range itself. This author has been unable to reproduce errors in reporting the UsedRange but requests inputs from interested parties. Readers with a clear demonstration of the UsedRange problem might care to advise me of it in the Discussion tab of this page. It is true to say that the removal of cell content at the end of a worksheet will not result in a revised scrolled region, and the use of Ctrl-End will still travel to the old position after deletion. These two matters are not necessarily linked however, since this still happens even while the UsedRange is correctly reported. In the meantime this code module will obtain a true used range.
* The procedure GetUsedRange approaches the used cells from the outer limits in all four directions, and then, after noting the first filled cells that it encounters for each direction, defines the overall range as the smallest bounding rectangle that fits the whole thing. It optionally returns the row and column bounds at the same time.
* The procedure WorkRangeInArray makes use of GetUsedRange in an example that loads a source sheet range onto an array for work, then passes it back to a target sheet, same or other, at some specified or default position.

## VBA Code Listing (Modified 3 Dec 2016)

Added descriptive variable names for GetUsedRange parameters in accordance with suggestion in Discussion.(3 Dec 2016)

# Bubble Sort One Dimensional Arrays
## Summary

This page deals with the sorting of single dimensioned arrays. These are typically the ones used for placing lists into variants with the Array method. It is more rare to find such a sorting routine in VBA, since most sorting is done in two dimensions.

## Bubble Sorting One Dimensional Arrays

* The procedure BubbleSort1DArray uses the slowest of sorting methods. However, there would seem to be no disadvantage in doing so here, since few lists ever attain great size. There are options for ascending or descending sorts, and for the method of return. If a return array is provided, that will be used, otherwise the input array will be changed by returning in that.

## The Code Module
# Bubble Sort on One Key
## Summary

This page is intended for procedures that sort on two dimensions. Further, since some make use of multisort methods, this page is restricted to sorting on a single key. That is, using one column or row as the basis of the sort.

## Bubble Sort Arrays in VBA

* The procedure is for sorting a two dimensional array. This is perhaps the most common requirement. The options allow for column or row sorts, choice of sort index, and the choice of ascending or descending sorts. There is again, a choice of returning the sorted work in a different array with the input intact, or if not supplied, returning it with the original changed.
* The bubble sort's speed is suitable for most VBA projects, though faster sorting algorithms are used for more demanding applications. Although not available for Excel, those who are using MS Word might consider calling the SortArray function of WordBasic instead. In Excel the WorksheetFunctions might bear some study as to their sorting usefulness.

## The Code Module
# Bubble Sort on Multiple Keys
## Array Sort on Three Keys

* This rather long VBA code listing allows bubble sorting of an array on three keys. It is sometimes called an intersort.
* In case it is not clear what that means, suppose there are many names to sort; each with two forenames and a surname. The names records occupy a row each and their parts are in separate columns. The first key might sort the surnames column, but there could be many records called Smith. Then the second key sorts among the first forenames where the surnames were similar. And there might still be a lot of John Smith name records that are the same. The third key sorts the second forename column for those cases where there are records with the same surname and first forename combination.
* A similar function can be found on Excel worksheets in the advanced sort functions. Users unfamiliar with this sort type might well experiment there to better understand the process.
* The function here has options for ascending or descending sorts, row sort or column sort, and the option to return the sorted work in another array or the original. Up to three keys can be specified, though if there are unused keys, say, because only two intersorts are needed, it is assumed that Key1 and Key2 will be used before Key3. In any case, unreasonable settings will result in message box advice.

# Variable Beep from VBA
## Summary

This VBA code module examines various uses of the Beep API. Its parameters are frequency and duration. It has no wait parameter, so chords are not possible, just simple tones. It is stated to work well for Windows 7 versions onwards, but might ignore the parameters for earlier versions. Simple musical scales and tunes are illustrated, in addition to a Morse code text sender.

## Beep API Bugs

In the past many problems were found in the use of the Beep API. Since Windows 7, the Beep API has been re-coded by Microsoft to work with computer sound cards. Prior to Windows 7, the API functioned only with an on-board sound chip. Unfortunately, during the transition from sound chips to sound cards, some computer manufacturers still used chips while others used cards. This is the basis of the problem for older Windows versions. No problems should be encountered in the use of the Beep API for recent builds.

## Procedure Notes

Several procedures are provided in the module. Copy the entire code into a standard module for testing. The procedures are as follows:
* Beeper is the basic form of the function. Running it will make a simple tone in the computer's speakers. Note that it does not refer to the alert sounder that is built into Windows, but the speakers used for listening to media. Both the frequency and duration of the sound can be adjusted, though the consistency of the output, being designed for selected frequency use is not very good.
* TestNotes expands on the basic format. Run this to produce up and down scales. There are two ways to access frequencies:
** The first is just to enter the exact frequencies for each note in a sequence of code lines; this is the case for the do re me scales, the so-called natural notes, eg, C,D,E,F,G,A,B,C....
** The other, when the exact frequencies are not known is to use a formula to calculate the frequency based on knowledge of the relative position of the note with respect to a reference point. (See Figures 1 and 2). The reference point in this case is note A = 440 Hz. Figure 1 shows three octaves of notes around 440Hz, and Figure 2 shows how notes in a simple music score relate to note distance. The note-distance value can be used to calculate frequency for any other note. For example, in Figure 2, notice that the G notes have a distance of 10; this and all other note distances are listed in the table of Figure 1. When it is understood that G notes always occupy the second line of the treble clef, a simple score can be marked with both notes and distances, ready for coding.
* SendMorse sounds out the Morse code for a parameter string. The procedure gives a basic output, with adjustable frequency (Hz) and dot length (milliseconds).
** Delays are introduced with Delay for the inter-element (one dot), inter-character (3 dots), and inter-word (7 dots) intervals, in addition to the basic one to three ratio for dots and dashes. All timing is derived from the length of one short dot element. A random element can be added to all timing in Random, where the maximum percentage of error can be set; this is said to better resemble a human hand rather that the too-perfect program.
** The convention is to estimate words per minute in terms of dot duration also, as T = 1200 / W where T is dot duration in milliseconds and W is the generated number of words per minute. The international Morse code is given for reference in Figure 3.

## The Code

* Modified 24 Dec 18 to add randomness to all timing in SendMorse
* Modified 24 Dec 18 to add an omitted sCode declaration in SendMorse
* Modified 23 Dec 18 to correct data in SendMorse array vSN
* Modified 22 Dec 18 to show use of note distance in a tune.
* Modified 21 Dec 18 to correct timing errors for Morse code procedures.
Run TestBeeper, TestNotes, or testSendMorse to run the various procedures.

## See Also
# Play a WAV File from VBA
## Summary

* This code module plays sounds in Excel VBA using existing .wav files. Many such files can be found in the C:\Windows\Media folder, and can be made from text with a suitable VBA procedure.
* The procedure used is an API. This API code has been tested for Win10 64 bit with Excel 2019 64 bit, and works well.

## Notes on the Code

* The most general version that applies to all VBA uses an API function sndPlaySound. The code plays audio (WAV) files via the user's speakers as opposed to any internal sounders. Files must already exist on the user's PC for any sound that is played. In Excel, and when the user just needs to play strings, then the built-in Speak function is more convenient.
* Notice that sndPlaySound makes use of a wait parameter. When assembling discrete sounds that are not intended to overlap, the function can be made to play its sound before allowing the next line of code to run. Conversely, to overlap a new note with the tail of an old note, as in the playing of piano keys, the next code line is allowed to run before the sound has completed. Setting the Wait parameter to False will cause the code to run on before completion.
* Libraries of wav sound files can be downloaded from internet pages. They are generally organized for download in zipped files by category. That is to say, a set of spoken numbers, spoken letters, piano keys etc. If the ideal set is not available then there are several free text to wav file applications on the internet that can be downloaded and that allow sound files to be made from typed text, Readers might care to comment in the My discussion page about high quality file sets.
* Quite complex constructions can be made by playing sound files in sequence. Possible applications include audio advice for the progress of long procedure runs, or sound punctuation of the type already used by Microsoft for the opening and closing of processes. Some individuals construct entire piano keyboards on user forms, complete with flats and sharps.
* Readers who intend to use integers or letters for playback as a sequence might be interested to know:
** The task of code writing is greatly simplified if the sound files' names start with the characters that they represent. For example, a file that expresses the sound for one is best called 1.wav, three as 3.wav, and similarly with the letter set. The reason is that a string character from code can be used to construct the name of the file to call. This allows single line calls in loops at times, compared to the accessing of lists of elaborately named files. See the procedure ReadIntegers for the method to use. Clearly, without the wav file set that goes with it, this procedure is of limited use to the reader.
** The lead in and lead out times of sound files can make for a fairly halting delivery. To get around this, record a sound file with as much in the one file as possible, rather than depending on a sum of many to obtain the result.

## External Links

* Pearson - Play Sounds: A page by Chip Pearson with much more detail. (Currently still has the now outdated 32 bit code)
* WavePad Download : A FREE sound file editor, that also makes sound files.
* SoundTap Stream Recorder : A FREE stream recorder for anything on the speakers. Part of WavePad suite.
* Switch File Converter : A FREE file type converter. Part of WavePad suite.
* ReadPlease Text Reader : A FREE good quality text reader. Now discontinued, so get a copy while you can.

# Make a WAV File from a VBA String
## Summary

* This code module will make a voiced wav file starting from a VBA string. A reference should be set in the VBA editor to Microsoft Speech Object Library.
* The parameters are just the intended string to be spoken and a full path to the intended file name. The process MAKES a file but does not speak it as audio. Once made, the wav file can be spoken with procedures in an adjacent page, or tested in Windows Explorer by simply opening it.
* No error code has been added. For example, if an attempt is made to use a folder with restrictions, an error will be raised. Users might consider adding some error trapping.
* The intended target wave file need not exist. If it does not exist it will be made. If it exists however, it will be overwritten.
* Notice that the user should enter his own profile in the file path. This author could not make the environmental paths work which would otherwise have made the code independent of the profile details.

## VBA Code

Copy the entire code listing into an Excel standard module. Modify the paths to your own, and run the top procedure to make a reusable wav file of the string. Remember to set a reference to Microsoft Speech Object Library.

## External Links

* Pearson - Play Sounds: A page by Chip Pearson with much more detail.
* WavePad Download : A FREE sound file editor, that also makes sound files.
* SoundTap Stream Recorder : A FREE stream recorder for anything on the speakers. Part of WavePad suite.
* Switch File Converter : A FREE file type converter. Part of WavePad suite.
* ReadPlease Text Reader : A FREE good quality text reader. Now discontinued, so get a copy while you can.

# Read Aloud Strings and Text
## Summary

This page contains Excel VBA code to read out the contents of strings; that is, text held in a string variable. It can be adapted for use elsewhere in MS Office.

## Code Notes

Place the entire code listing into a Standard Module and save the file with an xlsm suffix. Run the various subs to see how the code works.

## The VBA Code
# Get Array Data Stats
## Summary

This VBA code module is intended for MS Excel, since it prints a basic set of statistics onto the worksheet. It assumes that there is well behaved numerical data in a 1D array as an input. A frequency distribution is produced and associated statistics for the set.

## Notes on the code

* Copy the entire code listing into an Excel Standard module, save it, then run the top procedure.

## See Also
# Discrete Data Bin Stats
## Summary

This code module is intended for MS Excel, since it writes a set of statistics to the worksheet. It assumes that there is discrete data in a 1D array, either string or numeric. A set of statistics is printed, not for the raw data on the array, but for the bin-count of discrete values found on it. It includes a listing of the bin counts and other statistics. For example, if the array contained only elements with a one or a zero value, the results would list the overall counts of each, and produce other statistics.

## Notes on the code

* Copy the entire code listing into an Excel Standard module, save it, then run the top procedure.

## See Also
# The Knuth String Shuffle
## Summary

* This code module includes both a Fisher-Yates character shuffling routine for strings, and a Durstenfeld-Knuth routine to shuffle one-dimensional arrays.
** The procedure FisherYatesStrShuffle shuffles characters of a single string. This is limited to shifting single characters around within one string.
** Procedure KnuthArrShuffle sorts the elements of a one-dimensional array. The procedure is limited only by what can be stored in array elements.
* The two methods are pseudo-random and bias-free. Elsewhere, the use of a random generator does not necessarily guarantee that the results will be free from bias.
* The code can work in any of the MS Office applications that support VBA.

## Code Notes

* The Fisher-Yates shuffle applies a pseudo random selection method. It is described here for characters in a string but a related method, the Durstenfeld-Knuth method is preferred for arrays.
** Taking each element of a string in sequence for repositioning leaves one end of the result string badly biased. The Knuth algorithm instead proposes a random position within the string. The element at that position is then accumulated into the output and removed from the original. Subsequent selections are made in the same way from the ever shortened string.
** Note that there is still the possibility of a given character being unmoved in the process, but only within expectation.
** Set the number of strings required with variable Cycles in the top procedure. The Immediate Window has proved the best place for display and copying.
** It should be pointed out that any attempt to avoid the unmoved elements, will not only change the random nature of the shuffle but prevent the use of other than non-repeat strings. That is to say strings with repeated characters could not then be shuffled.
* The Durstenfeld-Knuth method for arrays differs only slightly from that of the Fisher-Yates implementation.
** To reduce processing, and no doubt to overcome the burden of removing an element from the middle of an array during shortening, the algorithm instead overwrites the element selected for output with the last element. In this VBA implementation the array is then conveniently shortened by one element with Redim Preserve.
* See Fisher Yates Shuffle for a good description of both methods.

## The VBA Code Module

Copy all of the code below into say, an MS Excel standard module, save the workbook as an xlsm file type, and run either of the test procedures to test the requisite code. Be sure to open the Immediate Window for output.

## See Also

* Fisher Yates Shuffle: A very clearly written article in Wikipedia, that explains worked examoles step by step.

## External Links
# Compare Shuffle Methods for Bias
## Summary

This VBA code module shows how bias can affect a shuffle algorithm, even when a random generator is used. The module is intended for MS Excel and prints two statistics panels on the worksheet to show how the selected methods differ. The good version is the Knuth shuffle algorithm, and the other is one that makes methodical swaps, sometimes multiple swaps, throughout the length of the array.

## Notes on the code

* Copy the entire code listing into an Excel Standard module, save it, then run the top procedure.
* The shuffle algorithms run for many cycles, (user set), and the bin count of discrete outcomes is presented in statistics. Notice that a very small number of elements, (3 or 4), has been used to keep the display and run time manageable. It may be of interest to know that the number of discrete outcome combinations is equal to the factorial of the number of elements. That is; using just six elements, say A,B,C,D,E,and F, would result in 6! = 120 discrete combinations, and a corresponding increase in the time to run.
* In particular, it should be noted that a small coefficient of variation (CV) denotes a method that is close to random whereas one with a higher CV shows that there is bias in the method.
* The method that uses multiple swaps is biased, whereas the Knuth method is not.

## See Also
# Backup Text Boxes on Close
## Summary

This VBA code is written for Microsoft Excel but is easily adapted to other applications in the MS Office set. It saves all of the text from a user form's text boxes in a log file whenever the form is closed. Then later, on re-opening the form, or at any other time, the user can fill the boxes with the most recent saved text.

## The VBA Code

* The code needs a user form called Userform1, two text boxes, TextBox1 and TextBox2, and a command button called CommandButton1. Set the UserForm1 property ShowModal to false for convenient study. Copy the code below into the three respective modules and save the workbook with an xlsm file suffix.
* Any code that is found in text boxes will be saved when the user form closes. This includes inadvertent closure of the user form or the deliberate closing of the workbook. It does not of course protect against the effect of power failures. The saving of data happens without intervention, so may need consideration if the storage of sensitive data is to be avoided.
* The log file is called SavedText.txt, and will be found in the same folder as the workbook. If a log file of that name is not found, then it will be made by the code for use. The log file has only two fields, the text box name and the string contents found in it. The comma separator was avoided in favor of the less likely encountered string >Break<.
* The saving function runs from the UserForm_QueryClose event. SaveTextBoxes makes a log string in a userform controls loop, then exports the string via WriteToFile.
* WriteToFile makes a log file if it does not exist, but otherwise overwrites any text that it finds, so that only the most recently saved session will be found there. Users who employ the logging procedure elsewhere should note that an extra Cr and Lf are stored at the end of logged entries, and might need consideration.
* RestoreTextBoxes runs only by pressing CommandButton1, so the user chooses whether or not to insert text. GetAllFileText imports all of the log file's contents at once with the file retaining contents until it is next overwritten. The string is split twice, once to break it into lines, that is, one for each text box record, and then again to break each record into its two fields for matching control names in the main transfer loop.

## Code Changes

8 March 2019: Changed data separator from comma to other, in Standard Module

### For the Standard Module
## See Also
## External Links
# Block Illegal Characters
## Summary

This VBA example can be run in any of the commonly used Microsoft Office applications. The examples show how to exclude illegal characters from text boxes while the keys are being pressed. In each of the two examples, the insertion point simply remains where it was when an illegal character is entered; accepted characters appear in the usual way. Both examples make use of the TextBox_KeyPress event; then first accepting only integers and a few other characters, and the second only letters and some supporting characters.

## The VBA Code
### For the UserForm1 Module

* Make sure that the ShowModal property of the user form is set to False, to allow normal code working and study with an open form.
* Copy the code below into the UserForm1 module, and type text into the boxes to see the results.
* Setting the parameter KeyAscii in code to the ASCI value of a non-printing character (eg: zero), prevents the display of the character that originated the event. Otherwise, KeyAscii is found to contain the ASCI value of the key that was pressed, and it is displayed.
* The KeyAscii value can be changed to any asci value, and provided that it is a printing character, it will be displayed.
* In each case code is added to restrict the permitted positions in the text. For example, a minus sign is valid only at the start of a number, and a hyphen is never expected at the start of a word.

## External Links
# Validate with the Like Operator
## Summary

This VBA example can be run in any of the commonly used Microsoft Office applications. The examples on this page make use of the Like operator to compare strings. The first example shows how to check that a string conforms to the correct format of the United Kingdom's National Insurance Number (NINO), a number used in much the same way as the US Social Security Number (SSN). The rules for the format are clear so it makes a good example. Unlike examples elsewhere in this set that check for illegal characters while they are being entered, this method is carried out only when the user has completed the entry.

## The VBA Code

* The code needs a user form called Userform1, two text boxes, TextBox1 and TextBox2, and a command button called CommandButton1. Set the UserForm1 property ShowModal to false for convenient study. Copy the code below into the three respective modules and save the workbook with an xlsm file suffix.
* When the workbook is opened, the user form will be displayed. Type a number format into TextBox1 and when complete, press the tab key to move to the next textbox. If the number format is correct then the insertion point will move, but if not it will stay in the faulty text ready for correction. Setting the Cancel argument of BeforeUpdate to true prevents the move.
* Note that the Before_Update event will not run at all unless a change has been made the text since the last time that the insertion point entered the box. So, to labor the point, after leaving the box, if the user clicks in it again without changes, the event does not run when moving on. If this poses a problem then consider the use of the Exit event for testing.
* See also Input Boxes for a number of other validation-related procedures.

### Code Changes

There are no changes so far.

### For the Standard Module
## See Also
## External Links
# Delays Past Midnight
## Summary

This VBA module delays for a specified number of seconds. It will work in any MS Office application that can run VBA. The following points are worth noting:
* Most delay procedures have problems at midnight when the Timer function resets, so code that depends on the difference of two values that span that time will be in error, and will perhaps cause a failure. This procedure avoids such problems by also compensating for the number of lapsed days. As such it will be found useful in timing and clock applications, if not for measuring time, at least for deciding when the display is to be updated. For example; running a delay of twenty seconds from ten seconds to midnight (clock count 86390) to ten seconds after midnight (supposed clock count 86410) would reset at midnight, and would never reach the required end value. The problem is solved by adding one count of 86400 (the number of seconds in one day) to the stepped value for each time that a date transition is made.
* The anticipated resolution of the procedure is about 10-16mS, consistent with that of the system timer. It is perhaps of interest to note that the GetTickCount API while able to accept millisecond parameters is still limited to the same 10-16mS resolution of the system timer.
* The procedure parameter can take on integers and fractions of a second, provided comments about resolution are borne in mind.

## The Code

Copy the following VBA code into a standard module in Excel, Word, or any other Office application that supports VBA.

## See Also

* Wait Functions - Chip Pearson: Several other methods of delay are considered.

# Time Lapsed Between Dates
## Summary

This module contains VBA code to calculate the lapsed time between two fully expressed dates; that is, containing both date and time information. It can run in any MS Office applications like Excel that can run VBA code.
* This procedure shows how to extract integer values of the time components from a date variable rather than the more usual string representation of a date. That is to say, assuming that the difference between two dates is two days, to extract the integer two instead of some date string for the year 1900.
* Date variables contain a combination of both dates and times, but they need not do so. Some have only dates and some have only times, and when converted to the single data type, they can be seen to represent days in their integer parts and times in their fractions. Although the input parameters can contain any date variables, exact results are obtained only when both times and dates are included in each parameter. If time data is missing from a date, the calculation is still performed but uses midnight as the assumption.
* The integer part of a date-converted-to-single is just the number of days since 31 Dec 1899 . It follows then that negative integer parts describe the days before that reference date. In fact the date function can be used for dates in the Gregorian calendar from Aug 2, 718 through Dec 31, 9999, though this differs when other calendars are in use. Add integers to, or subtract integers from date variables to modify the date by that number of days. Subtraction also applies.
* The fractional part of a date represents a part of a day. The individual parts of time within it can be obtained as follows; multiply the date variable by 86400 to find the whole-seconds; by 1440 for whole-minutes; and by 24 for whole-hours. Then convert these results to the single data type before taking each integer part. To modify an existing date variable by a number of seconds, we simply add 1/86400 to it for each second; 1/1440 per minute, 1/24 per hour, and as stated earlier, whole units for days. Subtraction also applies.
* Various functions also exist to simplify date-time handling.

## The Code Module

Copy all of the VBA code below into a standard module.
* Run the top procedure to test the function. Two examples are given; one for exact date-time data and another where some time data is missing.
* The output result is a colon-separated string, containing any of a selection of formats; seconds only, minutes-seconds, hours-minutes-seconds, or days-hours-minutes-seconds. The format option is set with sConfig, and the optional units label is returned in sLabel.
* The procedure's detail is useful. The procedure LapsedTime illustrates the basics of multi-component extraction, as compared to the use of the VBA DateDiff function's counting intervals of one type.

## See Also

* Calculate Elapsed Time - Microsoft: An article by Microsoft describing methods of avoiding error in time comparisons.

# Date-Time String Formats
## Summary

This page lists VBA code to format date-time strings and intervals. It is for use in Microsoft Excel and similar applications that run VBA code. The procedure takes a date variable and returns it as a formatted string. The parameter nOpt sets the chosen format from a set that includes dates, times, or intervals. There are two main format types in use:
* Strings containing a date, are those that display a day, month, and year, with or without times added to them. The important point here is that the number of days stored in the date variable is intended to be displayed as a date, as opposed to an integer. For example, the integer stored for 25 December 2018 is just 43459 and is only converted for display. Nearly all of the format options in the code module are of this basic type.
* Strings containing time intervals, are intended to display the number of days as an integer, as opposed to displaying it as a conventional date; that is, in the format d:h:m:s, like a stopwatch. If a date format is selected for an intended for an interval of a couple of days or so, then a very early date near 1900 will be displayed. That said, such an error might still be useful for those curious about the actual number of days associated with a particular date. One time interval format has been included to illustrate the difference in the method, while a full set of these can be found on an adjacent page.
* Date-time variable assignment examples can be found in the procedure DateAssign.

## VBA Code Module

Copy the entire VBA code listing into a standard module, select a formatting option (1-15) in the top procedure , then run it.

### Modifications

* 10 Jan 2019, DateTimeFormat code modified to include one interval format.

# Avoiding Change Event Recursion
## Summary

* This VBA code is intended to run in Microsoft Office applications that can run macros, like Excel or Word.
* It provides two different examples of a text box change event, one for TextBox1 and another for TextBox2.
* It will be noted that on displaying the form and entering one character into TextBox1, the resulting number found there is about 290 or so; this is coded to show the number of iterations of the change event that have taken place.
* Doing the same in TextBox2 shows that there has been just one run of the event.
* The code of the TextBox2_Change event avoids multiple runs of the procedure, avoiding the possibility of false results in certain circumstances.

## External Links
# CommandButton Toggle
## Summary

This VBA code module is made for Microsoft Excel but is easily adapted for use in other Office applications that can run VBA with user forms:
* Two improved CommandButton_Click procedures are provided. They show better emphasis when buttons are prssed. CmmandButton1_Click has a toggle action, for example to set either one of two possible modes, and CommandButton2_Click performs the usual task of one function. In each case:
** The user-set captions change in transition to reflect the current state of the buttons, for example Running etc. This can be of interest in the toggle states or when a procedure takes a long time to run.
** The button size is swelled about its center. This avoids enlargement from a fixed top-left point. The amount of the increase can be set within the procedure.
** The background and font color of the buttons are set in code, so are easily modified. Separate colors can be used for the two states.
** The Me.Repaint lines ensure that the button formats update immediately. If they are not present the procedures will start, and perhaps end, before that was done. Without Me.Repaint, but when DoEvents exists in the procedure-to-run, the repaint might still appear to work normally, until a procedure is run that does not require DoEvents. For those who intend to study this point, it is shown up best in the CommandButton2_Click procedure.

## The Code Module

Copy the UserForm_Initialize, CommandButton1_Click, and CommandButton2_Click procedures into the UserForm module of an Excel project. This can be achieved by first inserting a form called UserForm1, with one CommandButton called CommandButton1 and another CommandButton2. Double-click within the form in design mode to access its module. The Workbook_Open procedure goes into the ThisWorkbook module. Then, save the workbook, and run Workbook_Open (or re-open the workbook) to test the buttons on the form.

### Modifications

* 20 Jan 2019, added the previously omitted Me.Repaint code lines

## See Also
# Styling User Forms
## Summary

* FormatForm is used to format a single specified userform with pre-selected colorings and fonts. This replaces a previous procedure to format all open userforms. Assumes that a userform called UserForm1 exists.
* The procedure AutoFormat performs auto-sizing and layout for simple array data, so that the display and label bar is tabular in appearance, regardless of the length of the various data. This latter procedure also has facilities to transpose the input in case it is needed.

## Code Modules
#### Last Modified 10 Jun 2017

Corrected name of TransposeArr2D in Autoformat. (12 Jul 2019) Replaced multi-form procedure with single-form procedure FormatForm.(18 Jan 19)
Changed code to more general TypeName in FormatAllLoadedUserForms(28 June 18) Added transpose function, previously omitted (10 Jun 2017) Removed font procedures to their new page Reduced number of AutoFormat controls.(17 Nov 2016)
Added GetTextPoints. (17 Nov 2016)

### For the Standard Module
# Check if a Workbook has VBA code
## Summary

This VBA code module is made for Excel, but is easily adapted for other MS Office applications. It checks a workbook to see if it contains any useful VBA dimensional or structural code. Line counting has been found to be less reliable than this since even empty modules will show two lines of code each.

## The Code Module

* Place all of the code below into the same standard module, and identify the test workbook address in wb.
* Then, run the procedure CheckForVBA to check whether or not the test workbook contains identifiable VBA code structures.
* The procedures first check to see that the workbook is not locked.
* Users can modify the test keyword list in procedure ContainsVBAKeyWords.
* The test workbook is closed again after inspection.
* Results are shown in message boxes here, but the top section is easily modified for other uses.

# Get the VBA Code String
## Getting the Whole Project String

The code module below is written for Excel but is easily adapted for Word and other MS Office applications. It makes a single string out of the entire code project for the same workbook in which it is run. In the past it has been found useful when a long string is needed to test say, character frequency code. With only slight modification the individual module text can be returned, and other details.

## External Links

* Programming in the VBA Editor : A comprehensive page by Pearson for the VBA editor.

# VBA Code Indenter
## Summary

* This admittedly long code module is used to indent or format any VBA code that it finds on the clipboard.. It uses fairly standard code indenting rules, examples of which can be seen in the formatting of the code on this page. Commercial formatting utilities are now available for VBA editors in 64-bit Windows, but the code below runs, not as an add-in but as a simple VBA code module. Within reason it will work for all recent versions of Windows and Excel, and with other Microsoft Office applications that can run VBA. It is fast enough for even the most critical user.
* It places the indented text back on the clipboard to replace the original text. The user can then paste it as he pleases.
* The first effort has few options.
** The depth of indenting can be set. This is done by setting the number of spaces per tab.
** The number of consecutive blank lines can be set. This is limited to none, one, or left as they are found.
** Existing line numbers can be preserved or removed, though no renumbering facility has been provided.
** Shared label lines can be split onto their own lines.
** The code routinely replaces comments that are continuations with comments on their own lines, though users can elect to avoid changes to comments altogether. If comment changes are permitted:
*** Rem comment style is optionally replaced with the apostrophe style.
*** Users can choose to insert one space after the comment apostrophe or to remove it where it already exists.

## VBA Code Indenter-Formatter Module

A few points bear mention:
* The code runs as one complete standard module. Run the procedure Indenter to indent the clipboard content.
* The user might also consider an optional user form to preview the indented output string (sRet in procedure Indenter) prior to Paste.
* Select code with complete pairings to make best use of the indenter. For example, so that there are no obvious If's without End If's. That said, snippets of code, procedures, or whole modules can be indented. In fact any text with recognized VBA keywords and line breaks, will get indented. Users should set the VBA editor error settings to Break for Unhandled Errors to avoid unnecessary interruptions from the intentional raising of errors.
* Readers who find the matter interesting might consider adding any bug reports to Discussion, and I will look at them when I can.

### Work Method Used

All working is done in a module-level string array. The process is:
* Get the clipboard string first. The method used to get the string is the same as the one listed elsewhere in this series. See Clipboard VBA, for the DataObject clipboard methods. This method replaces an earlier method using a dummy user form.
* Load an array with the string as a set of lines. Load only the code part without any line number that may be present. Then remove the existing indentation from every line. This means any leading and lagging spaces and tabs.
* Re-connect lines broken with continuation marks. This process avoids many line identification problems, especially from follow-on comment lines that have been folded without their own continuation marks.
* Identify and mark the array with line types. The line types that come in closed structures, like For...Next or Sub...End Sub pairs, are especially important for indenting. These are marked as start and end line types respectively. Initially, it is not important which structures they are, just whether or not they are starts or ends. Middles such as Else also need to be identified, as well as comment, blank lines, and a large set of so-called other lines.
* Match up corresponding start and end lines. It works like this: Starting at the top of the code; select the first start line and count it as one, then move down, incrementing both start and end counters until the two counters are equal; the matched end line is then found. After resetting the counters, move down to the second start, and repeat the process until all of the start lines have been matched. The array start lines are marked with the row numbers for the corresponding end matches.
* Check the pair counts. If the code does not contain at least one start-end structure, or the start and end totals do not match, the user is invited to proceed or exit. A user assigned error is raised to achieve exit.
* Assign the indents and outdent counts for the main structures. Starting at the top of the code lines, go to the first line marked as a start. Add one indent count for all of the lines that lie between that start and its corresponding end line. Move down to the next start line and repeat the process until it is done. Now, for any line anywhere in the array that is marked as a middle, outdent, that is, subtract one indent count. Indent counts are converted to spaces using an indenting option.
* Join indent spaces to the code lines and make one string from them all. Although users can set a spacing option, four spaces to each indent unit seems to be the most useful, much as in the VBA editor itself.
* Upload the indented string to the clipboard, then advise that it is there, ready for paste.

#### Code Module Modifications

15 Dec 2018: Code modified to add error when clipboard not text. 14 Dec 2018: Code modified to use DataObject copy and paste methods. 29 Mar 2017: Minor edit to GetClipboard function comment.

## External Links
# Redundant Variables List
## Summary

This very long code module lists an Excel project's redundant variables.
Running the top procedure checks the VBA project of ThisWorkbook, that is, the workbook in which the code is run. It produces both worksheet and user form outputs. The code is self-contained in the one module, but in addition, the user needs to make a user form called ViewVars, with a textbox in it called TextBox1. The details are not too important since the display is adjusted in code to fit the contents. However, the user form's property ShowModal should be set to False, and Multiline set to True. A testing mode of sorts can be had by setting boolean variable bUseWorkSheets in RunVarChecks to True. Be advised however, that this will clear all existing worksheets before writing to sheets one to five. To labor the point, if your intention is to not disturb the contents of project sheets one to five, then be sure that bUseWorkSheets of RunVarChecks is set to False; redundant variables will still be listed in the user form ViewVars after a few seconds of code run.

## Points to Note

There are some limitations:
** The listing can only work for code that compiles correctly; that is, sensible constructs if not necessarily working code.
** API variable declarations and enumerations of constants are not handled. That is to say, they will not be listed even if they are redundant.
** The module is coded to work with the VBAProject of ThisWorkbook . There is however, an optional parameter to access another workbook object for those who intend to check some other.
* The module works with the usual VBA variable naming methods. This includes the use of public same-names, and fully expressed variable descriptions. It does so by searching for compound similars as well as their simple forms. For example, although rare, the three forms myvar, Module1.myvar, and VBProject.Module1.myvar could all be used in code for the same variable. The use of these forms allows the same variable names to be used in any module heading without conflict.
* Several worksheet listings are made for output results and testing. The user should make sure that sheets 1 to 5 exist, since the code will not create them in this listing. The user might want to restrict or change these in the main procedure if they will conflict with other uses. A separate user form output makes use of procedure AutoLayout.
* The user form styles might not suit everyone, but the colorings and fonts can be changed in two user-sections of the procedure AutoLayout. Bear in mind however, that the chosen font must be monospaced for a neat layout. Apart from this restriction, the layout will handle any regular font size from about 6 to 20 points, as well as the bold and italic variants. That is to say, the code will auto-adjust the userform layout and sizes to produce a useful display.
* Procedures have not been marked as module Private. There is the remote possibility of same-name procedures being encountered when users also make use of other modules in this series. In future I will try to remember to mark them module-private if they look as though they were used elsewhere.
* Interested parties might like to advise of any bugs. Please use only the Discussion page and I will get to them as soon as I can.

## Work Method Used
### General Preparation

* The general method is to make a declared variables list then test each variable entry to see if it is used.
* The project string contains all of the code in the project. The string is loaded into a work array line by line, and is passed in variants from process to process.
* Procedure, module, and project name information is also added. Every code line is marked with this information.
* Quotes and comments are removed, since they could contain any text at all, and might confuse the decision process.
* Other confusions arise from continuation lines, so these are all joined up into single long lines prior to interpretation.
* Shared label lines and line numbers can also cause difficulty, so labels are given lines of their own, and line numbers are separated prior to any decision process.
* Blank lines are not needed so they are removed. Because there is a changed line count, the project work array is renumbered.
* Each code line is marked with its residential line ranges. Each line is given the code line range for the procedure and for the module in which it resides. This data is then easily found later.

### The Declared Variables

* The declared variables list, the array vDec, contains every declared variable in the project.
* It lists all other pertinent data about each variable. The scope of each variable is determined and added. The nominal search line ranges are also added. These are the line ranges suggested at first sight after knowing the scope of the variable. For example, a procedure level declaration would show the procedure line range, and a module-private item the module's line range.
* The variables are marked on vDec when they are found as used. The search sequence is, all of the procedure level variables, then the module private variables, then lastly the public variables. When there are same-name variables with different scopes, this sequence is useful, in that it progressively reduces the required search ranges.
* Every variable is checked for naming ambiguity before deciding which search method to use. Only if there is no names ambiguity can a so-called normal approach be taken; ie; searching the entire nominal line range. Otherwise, the nominal search range needs modified to avoid regions where same-name variables were already found. For example, a module variable search would not look in a procedure where a same-named variable had been both declared and used, but would check anyway if no same-name item were declared there.
* Public and module level variables have to be checked in three names. Variables' full names can include project, module, and variable names, or just module and variable names, in addition to the better known short forms.
* Public variables are handled a bit differently. These variables can exist in each module with the same name. There are two kinds of duplicated names possible for public variables; firstly, there is the kind where there is a public variable with the same name as a variable in any number of procedures, and secondly, there is the kind where the same name is used for a public variable in more than one module heading. In these same-name cases the use of public variables need at least the module and variable name when their use is not in the module where declared.
** Most of the time a public variable's name is entirely unique. That is, there is no other variable in the project with the same name. In this case the use of the variable can be searched throughout the project without restriction.
** If the public variable has no same-names in other module heads, but there are same-names in module or procedure variables, then the whole project must be searched for its use, taking into account line restrictions from modules and procedures where such same-names were already found as used.
** If the public variable has same-name variables in more than one module heading, then the determination of variable use must be handled in two stages;
*** The entire project must be searched without restriction using both compound forms of the public variable
*** Then search in the module where the public variable is declared, taking account of any procedure restrictions that apply from same-names there.
* After all this, any variables not marked as used can be listed as redundant.

## VBA Code Module
### Updated and Tested 17 Sep 2017

Modified changed word aliases to similars (15 Jan 2018). Modified AutoLayout to avoid wrap back in form. Label length plus 4 spaces now, not 2. (17 Sep 2017). Added a note on need for VBA Extensibility 5.3 and tested code - working OK.(31 Dec 2016)
Modified AutoLayout to reduce control count.(17 Nov 2016). Modified AutoLayout for better font choice.(16 Nov 2016). Added simpler options for fonts in AutoLayout.(16 Nov 2016) Modified code in dynamic arrays and added test mode switch bUseWorkSheets in RunVarChecks.(15 Nov 2016) Removed one redundant procedure and corrected TrimStr error.(13 Nov 2016) Corrected code call to NewPubRange in MarkPubVarUse. Parameter lines now whole project.(8 Nov 2016) Changes made to user form display procedures. (7 Nov 2016)

# Array Data To Immediate Window
## Summary

This VBA code module allows the listing of arrays in the immediate window. So that the user can see examples of its use, it makes use of various procedures that fill the array for demonstration and testing. The VBA code runs in MS Excel but is easily adapted for any of the MS Office products that run VBA. Clearly, mixed data varies in length and in its number of decimal points. This module displays the array neatly taking account of the variations that might otherwise disrupt the layout. It can decimal point align the data or not, according to internal options.

## Code Notes

* DispArrInImmWindow is the main procedure. It formats and prints data found on the two dimensional input array. It prints on the VBA Editor's Immediate Window. Options include the printing of data as found or making use of decimal rounding and alignment. The entire output print is also available as a string for external use. The process depends on monospaced fonts being set for any display, including the VBA editor.
* RndAlphaToArr, RndNumericToArr, and RndMixedDataToArr load an array with random data. The data is random in the content and length of elements, but in addition, numerics have random integer and decimal parts. Each allows adjustment of options internally to accommodate personal preferences.
* TabularAlignTxtOrNum is not used in this demonstration. It is included for those who prefer to format each individual column of an array during the loading process. Its input variant takes a single string or number and returns the formatted result in a user-set fixed field width. The number of decimal places of rounding can be set. Note that when all data in a column of a numeric array is loaded with the same parameters, the result is always decimal point alignment.
* WriteToFile is a monospaced font, text file-making procedure. If the file name does not exist, it will be made and saved automatically. Each save of text will completely replace any previously added. It is added here in case a user needs to save an output greater than that possible for the Immediate Window. The Immediate Window is limited to about two hundred lines of code, so large arrays should make use of the main procedure's sOut string. Again, wherever outputs from the main procedure are used, monospaced fonts are assumed.
* Note that the user might add a procedure to export large values of sOut, the formatted string, to the clipboard. Procedures exist elsewhere in this series that will accomplish this.

## The VBA Module

Copy the entire code module into a standard VBA module, save the file as type .xlsm and run the top procedure. Be sure to set monospaced fonts for the VBA editor or the object will have been defeated.

### Updates

* 26 Nov 2019: Adjusted DispArrInImmWindow code to better estimate maximum column width, taking account of imposed decimal places.

## See Also
# Viterbi Simulator in VBA
## Summary

This code is made for Excel. It simulates the behavior of a data channel's convolutional coding, though by necessity it concentrates on simple examples. Two rate 1/2 systems are provided; both with three stages, one for generator polynomial (111,110), and the other for (111,101). The code was written to improve understanding of the Wikibooks page A Basic Convolutional Coding Example, but might also be of elementary use for students without other software. The code concentrates on random errors of the type caused by Gaussian noise sources. Blank work sheets can be found in the drop-box below:
* Blank Work Sheets for (7,5) and (7,6) Configurations
* Viterbi Trellis Worksheet for configuration (7,6)Viterbi Trellis Worksheet for configuration (7,5)

## The Simulator

For each of the two configurations, rudimentary options have been provided. No user form has been included here, the author preferring to modify the settings directly in the top procedure's code. The branch metrics make use of CLOSENESS as opposed to HAMMING DISTANCE. A version using HAMMING DISTANCE can be found on an adjacent page.
* User mode settings allow various combinations of inputs and errors to be applied.
* Both coders produce two bits of output for every one bit of input. The message input (display stream m) can be specified by the user, manually or generated randomly to any given length. The decoder output message is distinguished from the original as m*.
* The user can run one cycle or many. Long cycle averaging is often useful. A message box summarizes the BER (bit error rate) results across all cycles. The user can output the metrics and basic streams for one chosen cycle to the worksheet.
* The coder output is modified to include errors. This simulates the effect of random noise in a transmission channel. The user can set specific errors in exact positions or apply random errors throughout to a specified bit error rate. Recall that error bit positions apply to the output of the coder and that the number of bits there will be double that of the message input.
* The display streams are labeled. The user can display the metrics and streams for a cycle. The streams are:
** m is the original message input to the coder.
** c is the coded output from the coder without any errors.
** r is the received version of the coder output with the applied errors.
** m* is the decoder output, the recovered message.

## The VBA Code

The code is provided below in one complete module. Copy the code into a standard module. Set options in the top procedure RunCoding, then run that procedure to obtain a summary of error correcting results. The process will clear Sheet1, so be sure that no other essential work is located there. As an example of setting the options, assume that the intention is to test the performance of the 7,5 configuration with both random inputs and random errors to BER 0.01. Proceed as follows:
* Set nCodePolyGen= 75 to select the 111,101 configuration,
* nModeNumber = 8 for random inputs with random errors,
* nAutoCycles = 100 for the average of 100 blocks,
* nLenAutoInput = 500 to use five hundred bits in each input block,
* nNumCdrFlushBits = 3 to add flushing bits at end of each input block,
* sngBER = 0.01 to apply one percent errors,
* Other options can be ignored for this task.
* Run the procedure RunCodingC. Output for the first cycle will be displayed on sheet one, and a summary for the changed BER across the decoder will appear on a message box when the run is complete.. Re-save the code or press the editor's reset button between runs with new parameters.

### The Module

Modification 14/Aug/18; removed column numbers restriction. User responsibility now. Code Functional 11/Aug/18. Modification 11/Aug/18; corrected ColourTheErrors procedure.
Modification 23/Mar/18; removed subroutine Notes as redundant. Modification 03/Nov/17; added back path edge stream to sheet display. Modification 01/Nov/17; corrected coding errors. Modification 31/Oct/17; added back path coloring.

## See Also

* A Basic Convolutional Coding Example
* Viterbi Simulator in VBA 2

## External Links
# Viterbi Simulator in VBA 2
## Summary

It has been noted that some calculate trellis metrics in different ways. So, this page includes an identical convolutional coding function to that in Viterbi Simulator in VBA. The main difference is that whereas that page displays its metrics in terms of CLOSENESS, this page does so in HAMMING DISTANCE. The code layout differs slightly between the two but the error correction remains the same.
This code is made for Excel. It simulates the behavior of a data channel's convolutional coding, though by necessity it concentrates on simple examples. Two rate 1/2 systems are provided; both with three stages, one for generator polynomial (111,110), and the other for (111,101). The code was written to improve understanding of the Wikibooks page A Basic Convolutional Coding Example, but might also be of elementary use for students without other software. The code concentrates on random errors of the type caused by Gaussian noise sources. Blank work sheets can be found in the drop-box below:
* Blank Work Sheets for (7,5) and (7,6) Configurations
* Viterbi Trellis Worksheet for configuration (7,6)Viterbi Trellis Worksheet for configuration (7,5)

## The Simulator

For each of the two configurations, rudimentary options have been provided. No user form has been included here, the author preferring to modify the settings directly in the top procedure's code. The branch metrics make use of HAMMING DISTANCE as opposed to CLOSENESS. A version using CLOSENESS can be found on an adjacent page.
* User mode settings allow various combinations of inputs and errors to be applied.
* Both coders produce two bits of output for every one bit of input. The message input (display stream m) can be specified by the user, manually or generated randomly to any given length. The decoder output message is distinguished from the original as m*.
* The user can run one cycle or many. Long cycle averaging is often useful. A message box summarizes the BER (bit error rate) results across all cycles. The user can output the metrics and basic streams for one chosen cycle to the worksheet.
* The coder output is modified to include errors. This simulates the effect of random noise in a transmission channel. The user can set specific errors in exact positions or apply random errors throughout to a specified bit error rate. Recall that error bit positions apply to the output of the coder and that the number of bits there will be double that of the message input.
* The display streams are labeled. The user can display the metrics and streams for a cycle. The streams are:
** m is the original message input to the coder.
** c is the coded output from the coder without any errors.
** r is the received version of the coder output with the applied errors.
** m* is the decoder output, the recovered message.

## The VBA Code

The code is provided below in one complete module. Copy the code into a standard module. Set options in the top procedure RunCoding, then run that procedure to obtain a summary of error correcting results. The process will clear Sheet1, so be sure that no other essential work is located there. As an example of setting the options, assume that the intention is to test the performance of the 7,5 configuration with both random inputs and random errors to BER 0.01. Proceed as follows:
* Set nCodePolyGen= 75 to select the 111,101 configuration,
* nModeNumber = 8 for random inputs with random errors,
* nAutoCycles = 100 for the average of 100 blocks,
* nLenAutoInput = 500 to use five hundred bits in each input block,
* nNumCdrFlushBits = 3 to add flushing bits at end of each input block,
* sngBER = 0.01 to apply one percent errors,
* Other options can be ignored for this task.
* Run the procedure RunCoding. Output for the first cycle will be displayed on sheet one, and a summary for the changed BER across the decoder will appear on a message box when the run is complete. Re-save the code or press the editor's reset button between runs with new parameters.

### The Module

Modification 14/Aug/18; removed column numbers restriction. User responsibility now. Code Functional 11/Aug/18. Modification 11/Aug/18; corrected accumulated errors and procedure ColourTheErrors. Modification 10/Jan/18; corrected error in name of procedure to run. Modification 03/Nov/17; added back path edge values stream to sheet display. Modification 01/Nov/17; corrected errors in coding. Modification 31/Oct/17; added back path coloring.

## See Also

* A Basic Convolutional Coding Example: Detailed working for an error correction configuration. Describes the subject material for which the simulator was made.
* Viterbi Simulator in VBA: To display metrics in terms of closeness instead of Hamming Distance as is the case on this page.

## External Links
# Generate Random Dictionary Words
## Summary

This VBA code module works only in MS Word. It generates a pseudo random list of dictionary words, listing them in the immediate window of the VBA editor.

## The VBA Code

* Copy the code listing into a standard VBA module in MS Word and save the file as a docm type. Set the user options in the top section and run the procedure to produce the list.
* The variable Num sets the number of words as output, and the variable TypoLen should be set to the approximate length of the sought-after words.
* There is no ready-made collection of English words called a Dictionary in MS Word; the term is used for sets of user-constructed word lists. To access a list of English words, an indirect method needs to be used. A random word is first generated equal in length to TypoLen. The spell checker then fails to recognize the word so generates suggestions, this time as a proper collection. Provided that there is at least one such suggestion in the spell check collection, a random choice is made from among them for the output list. The loop continues until the chosen number of words has been produced.

### The Code Module
## External Links
# Transfer Data to the Worksheet
## Summary

This page gives an overview of transferring data from VBA to the worksheet. The code module includes procedures for transfer of both one and two dimensional arrays to the sheet. Brief code line notes are also included for single line and block transfers.

## Contents of the Module

* The module works best if kept intact. The top procedure has code lines to run the other procedures. Just de-comment the code that you intend to run, and add comment marks to the lines that are not intended to run.
* The code procedures include normal or transposed transfers from two dimensional arrays.
* In addition, the handling of one dimensional arrays has been included. They can be transferred into a column or a row.
* The placement position can be specified in every case.
* Note that the selected worksheet will be cleared on each run, so select a worksheet for output that will not remove essential data.
* ConvColAlphaToNum and ConvColNumToAlpha are included to convert worksheet column references between the alpha and numeric formats.
* Additional procedures have been added for clearing and formatting text and the sheet.

## External Links
# Worksheet Common Utilities
## Summary

The procedures on this page are made for Microsoft Excel, and include commonly used worksheet utilities.

## The VBA Code
#### Modifications to Code
### Does Worksheet Exist?

Before making a worksheet or referring to one that is assumed to exist, it is best to be certain one way or the the other. This routine returns True if there is already a worksheet with the parameter's name.

### Add a Named Worksheet

This routine adds a worksheet with a specified name. First make sure however that the worksheet name is not in use; see SheetExists.

### Changing Column References

At times it is useful to have routines to change the column alpha reference style to a numerical one, and vice versa. These procedures to that.

### Next Free Row or Column

These procedures find the next free column or row. One set selects the cell in question while the other set simply return its position. Examples exist for both columns and rows, and in the absence of a chosen parameter, column 1 or row 1 is assumed.

### Clear Worksheet Cells

This procedure makes a selective clear of the specified worksheet, depending on the parameter nOpt. The options as coded include, clear contents, (that is the text), clear formats, (the fonts and colours), and clear all, a combination of the two.

### Move Rows and Columns

At times it is useful to shift entire columns and rows of data by one place on the spreadsheet, and in any case the process can be repeated as often as is necessary. These procedures assume that the user has first placed the cursor in the column or row of interest. The columns feature is of particular use when bringing an external tabulation into the worksheet; the columns will almost certainly need to be rearranged to match those of the resident set. Food databases are notorious for their different formats, none matching the sequence of those on food labels. Hopefully, some day a product's food data could be entered all at once with a scanned image.

### Delete Various Worksheet Items

These procedures allow deletion of worksheets, rows, and columns. Before deleting a worksheet, it should first be confirmed to exist.

## See Also

{bookcat}

# VBA Code to Read ASCII Log Data from LAS File
## Summary

This page lists a VBA Sub procedure to read data from a LAS file. A LAS file is an industry-standard binary format for storing airborne LIDAR data. Sub procedure requirements:
* A LAS file must be located in the same folder where the macro-enabled Excel file is located
* The active worksheet must have in cell A1 the name of the LAS file

## Example Data to Test

You may create a ASCII file with extension LAS using the following example of data.
* Screenshot showing content of LAS file
* Screenshot showing data in LAS file

## Output on worksheet after running VBA Sub

* Screenshot showing Excel active worksheet after running VBA Sub
* Shows screenshot of Excel worksheet