VBA Functions
- Identifying the Newest File in a
Directory
A function that returns the name of the newest file in a directory. - Retrieving the Computer Name or Logged-in User
Name
API functions that return the name of the computer, or the name of the logged-in user. - The Versatile Split Function
Several useful functions that use VBA's Split function. Requires Excel 2000 or later. - Determining the Drive Type
A VBA function that accepts a drive letter, and returns a string that describes the type of drive. Uses a Windows API function. - Playing a Sound Based on a Cell's Value
A worksheet function that will play a WAV file, based on a condition you specify. - A VBA Function to Get a Value From a Closed File
This function uses an XLM macro to perform an otherwise impossible task. - Getting a List of Installed Fonts
A simple technique to retrieve a list of installed font names. - Searching Using Soundex Codes
Demonstrates how to use a custom function (SOUNDEX) to search for names by their approximate sound. - Does a Worksheet or Workbook Have Code?
Custom functions to determine whether the code module in a particular workbook or worksheet contains any VBA code. - Determining if a Range is Contained in a Range
A VBA function that that returns True if a range is a subset of another range. - A Custom Function For Relative Sheet References
A VBA worksheet function that lets you refer to worksheets in a relative manner. - Determining the Data Type of a Cell
A VBA worksheet function that returns a string that describes the contents of a cell. - Using the GetSetting & SaveSetting Functions
An easy way to store application settings between sessions. - Determining When a File Was Created
Using a Windows API call to determine a file's creation date and time. - Some Useful VBA Functions
Listings of six simple, but useful functions that you can use in your VBA projects. - A Color Picker Dialog Box
A function that displays a dialog box that lets the user select a color. Uses a class module.
- Multifunctional Functions
A technique that makes a single worksheet function serve multiple purposes. - Extracting the n-th Element From a String:
A handy custom worksheet function that extracts a specified element from a text string. - Determining the last non-empty cell in a column or row:
Two useful VBA functions. LASTINCOLUMN returns the contents of the last non-empty cell in a column; LASTINROW returns the contents of the last non-empty cell in a row. - Undoing a VBA Subroutine:
An example of a VBA routine that can be undone with the Edit Undo command. - Looping Through Ranges Efficiently in Custom Worksheet Functions:
A technique that lets you create more efficient custom worksheet functions by limiting the range references to the sheet's active area only. This makes it feasible to use a complete row or column reference in a custom function - such as MYSUM(A:A). - Getting a List of File Names:
A VBA function that returns an array containing filenames that match a filespec in a particular directory. - Identifying Unique Values in an Array or Range:
a VBA function that accepts either a worksheet range object or a VBA array. The function returns either (a) a variant array that consists of just the unique elements in the input array or range, or (b) a single value - the number of unique elements in the input array or range. - Determining the Video Resolution:
Using a Windows API call to determine the user's video resolution.