These Excel UDF Macros (User Defined Functions) can be downloaded and attached to a speadsheet in a matter of minutes. Once in place, they can be used in any Excel formula. Other Keyliner routines, such as Excel CityStateZip Parse Functions use these modules.
Installation and Use
Source-code can be downloaded from the Keyliner public GDrive site (formerly, this article listed the code, but the code became too ungainly to post in a blog). You are welcome to use this code in your personal or commercial projects.
1. Download Excel Macros from link:
(You do not need to register as a user to download)
Keyliner' Public GDrive
2. Save the downloaded files to any directory, such as:
3. Launch Excel and Enable Excel Macros.
Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.
- Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
- Click Trust Center, Trust Center Settings
- Macro Settings
- Enable All Macros
- Older versions of Office have similar settings.
4. Launch Excel to a blank sheet and import the VB code using these standard Excel steps:
Click the "orb", Excel Options.
On the left, choose "Popular"
Click [x] Show Developer tab in the ribbon
5. Press Alt-F11 (Starts the VBA / Macro Editor)
6. On the tree-diagram, illustrated below, select your sheet ("Book1"), then:
- Other-Mouse-Click (Book1),
- Choose "Import File"
- Browse and select the previously-saved module, e.g. C:\Data\Source\CommonVB\A800_UtilStrings.bas
- You will see the code appear under the "Modules" folder (not yet in the illustration below but will be when you complete these steps)
A. In Cell A1, type an example string, with leading, trailing and extra internal spaces. Press Enter when done:
example: " Now is the time "
B. In cell B1, type this formula:
Results: "Now is the time" (with interior spaces trimmed)
Excel UDF (User Defined Functions)
Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
Using VBA to Send Email