Wikibooks

Visual Basic for Applications

Introduction

edit

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

edit

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

edit

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

edit

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

edit

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

edit

 

A printable version of Visual Basic for Applications is available. (edit it)

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

edit

Arrays (2D)

edit

Charts

edit

Clipboard

edit

Codes and Ciphers

edit

Error Handling

edit

Files and Folders

edit

Fonts and Formats

edit

Games

edit

Hashing Strings and Files

edit

Logs and Text Files

edit

Message and Input Boxes

edit

Numbers

edit

Range

edit

Sorting

edit

Sound

edit

Statistics

edit

String and Array Shuffles

edit

Text Backup and Validation

edit

Times, Dates, and Waits

edit

User Forms

edit

VBA Editor Projects

edit

Viterbi Project

edit

Procedures for MS Word

edit

Worksheet Transfers

edit

Worksheet Utilities

edit

ASCII Log Data from LAS File

edit
edit
Retrieved from "https://en.wikibooks.org/w/index.php?title=Visual_Basic_for_Applications&oldid=4111835"