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