Visual Basic for Applications

Introduction This set of pages is intended for those who have an existing interest in VBA coding. It provides working code that the author himself finds of interest. Many of the procedures have good generality, for example, code for logging data, returning a file address, hashing, and others. In most cases the code of each page can be copied into a standard VBA Excel module, though in many cases will work in other Office applications, with or without slight modification. VBA Editor Settings Because users' computers vary as to the Windows applications installed, it might be helpful to know which References have been set in the Tools menu of the authors' VBA editors. Authors who add to the work should update any of theirs to the list. These currently include: Visual Basic for Applications Microsoft Excel 14.0 Object Library Microsoft Office 14.0 Object Library OLE Automation Visual Basic for Applications Extensibility 5.3 mscorlib.dll Microsoft Scripting Runtime Microsoft Forms 2.0 Object Library Microsoft Speech Object Library On recently changing to 64 bit Office the hash file procedures in this series initially did not work. It was found essential to include the older .NET Framework 3.5 in MS Windows; (this includes version 3 and 2), and not just the more recent versions. Selecting this older version in Turn Windows features on or off solved the problem. In addition, it has been found most useful to set the ShowModal property of userforms to False. This allows the testing of code when the userform is still open, for example to step through the code line by line. This also allows keyboard interaction with the worksheet without closing the form. About API Declarations The original code listings with API's in this series were made and tested with Win7 and Win10 32 bit versions of MS Office 2010. Subsequently, with a 64 bit version of MS Office 2019, and Windows 10, it was found that the API's no longer worked. It seems that assurances are needed for the safety of the API code in 64 bit systems, and as such, need the inclusion of the word PtrSafe between the declaration words Declare and Function. At times, for pointers and handles, the long data type for return parameters needs to be changed into a LongPtr type, but it is less clear as to which ones are affected. This allows them to work in 64 bit versions of MS Office. Some pages on the net have included code for conditional declarations and compiling, to allow for both old and new, but this author has found that the default part of such structures fail for his new configuration. The API code on these pages will be updated for the Win10 Office 2019 64 bit combination, so those who intend to use older systems should check this point in the event of difficulty. HDMI Sound Issues in VBA Using a television as a monitor is a good idea, since it saves time and avoids the need for additional computer speakers. HDMI outputs carry high quality video and digital speech in the same cable, whereas VGA connections are of a lower resolution and need a separate audio lead. Some additional points might be of interest for those who intend to make use of the HDMI connection of their TV in this way. When HDMI is first selected as the connection method, the user might notice new problems: The screen display might overlap at the edges. This is most likely overscan. To correct it, switch off the TV set's overscan in its settings menus. Audio files and text readings might be missing the first few seconds of sound. This is likely a HDMI television issue, where the audio socket on the PC's back panel will be free of the problem but when playing wav files to the television via HDMI there is front end clipping. There is one obvious solution. Use a separate audio cable from the PC's rear jacks plus the usual video connection for HDMI or VGA. In each case Restart the PC and go to the Sound settings of the Control Panel. Make sure the rear jack of the PC is the default sound input. Go to the TV's own menu settings and make the sound input Analogue. Selecting Long Listings Some code listings or data sets can be very long on a web page. As such, just selecting the intended text can be quite time consuming. Most browsers however have a way to select all of a page's text right up to the end. Microsoft Edge and Firefox have right context menus with Select All. After selection, use copy to get the whole page. It is then an easier task to trim the pasted selection to its essential parts. Opera Browser has no right context function for Select All . Instead, use the key combination Ctrl+A. Interest Areas The following pages are offered as a starting point to get things going. Contributors are invited to edit and add to these pages, and to add new ones. I hope to fill in a bit more of the textual descriptions in the near future. Arrays (1D) for Characters /Character 1D Arrays/ Arrays (2D) /Array Data To Immediate Window/ /Array Data To WorkSheet (1D or 2D)/ /Array Output Transfers/ Charts /Charts from Arrays/ /Character Frequency Charts in Excel/ Clipboard /Clipboard VBA/ Codes and Ciphers /Simple Vigenere Cipher in VBA/ Error Handling /Error Handling/ Files and Folders /File and Folder Dialogs/ (32 and 64 bit) /Recursive File Listing of Folders/ /File and Folder Utilities/ Fonts and Formats /Font Utilities/ Games The Elusive Hiding Button Hashing Strings and Files /String Hashing in VBA/ /File Hashing in VBA/ /Folder Hashing in VBA/ /Running the FCIV Utility from VBA/ Logs and Text Files /Use Log Files from VBA/ Message and Input Boxes /Message Boxes/ /Input Boxes/ Numbers Problems Chosing Position for Randomize() /A PRNG for VBA/ /A Pseudo Random Character Table/ /Listing Prime Numbers/ /Big Number Arithmetic with Strings/ Range /Excel Sheet True Used Range/ Sorting Bubble Sort 1D Arrays Bubble Sort 2D Arrays on One Key Bubble Sort 2D Arrays on Multiple Keys Sound /Variable Beep from VBA/ /Play a WAV File from VBA/ /Make a WAV File from a VBA String/ /Read Aloud Strings and Text/ Statistics /Get Array Data Stats/ /Discrete Data Bin Stats/ String and Array Shuffles /The Knuth String Shuffle/ /Compare Shuffle Methods for Bias/ Text Backup and Validation /Backup Text Boxes on Close/ /Block Illegal Characters/ /Validate with the Like Operator/ Times, Dates, and Waits /Delays Past Midnight/ /Time Lapsed Between Dates/ /Date-Time String Formats/ User Forms /Avoiding Change Event Recursion/ /CommandButton Toggle/ /Styling User Forms/ VBA Editor Projects /Check if a Workbook has VBA code/ Get the VBA Project Code String Automatically Indent and Format VBA Code VBA Code to List Redundant Variables /Array Data To Immediate Window/ Viterbi Project Viterbi Simulator (CLOSENESS) Viterbi Simulator (HAMMING DISTANCE) Procedures for MS Word /Generate Random Dictionary Words/ Worksheet Transfers /Transfer Data to the Worksheet/ Worksheet Utilities /Worksheet Common Utilities/ ASCII Log Data from LAS File /VBA Code to Read ASCII Log Data from LAS File/ Related books Visual Basic Excel VBA Visual Basic for Applications Collection Visual Basic 100%