Downloads
The files here are compressed into Zip files, which "shrinks" them down to roughly one third their original size. They can be uncompressed with any standard "unzipping" program, such as WinZip or PKUnzip. If you don't have an unzipping program, go to the PKWARE site to download a shareware copy of PKZIP: http://www.pkware.com (This is shareware, so do the right thing and register your copy!) Zip files must be uncompressed before using them. Excel cannot open a compressed zip file. The PKWARE web site has a nice introduction to the "theory" of zip files. These files are combination Excel97 / Excel95 / Excel5 files. Although this increases the file size (almost doubling it, in fact), it allows you to download and use these files regardless of your version of Excel (version 5 or later). The file sizes listed are the sizes of the ZIP files. The actual XLS files, when uncompressed, are approximately 3 or 4 times as large. (E.g., DynaRange.ZIP is 6K, and DynaRange.XLS is 22K) To save space on your hard drive, do a "Save As" to save the file in your Excel version, rather than a combination format file. (E.g., DynaRange.XLS is 22K in combination Excel97/95/5 format, but is only 15K in Excel97 format.) I am still adding downloadable files to my web site. If you see something on my site and want a workbook example, please send me an email and I'll be glad to send you some example code.
|
| Activate Excel | This download contains a bas module file with the code described on the Activate Excel page. | ||
| Alert | The best way to display diagnostic
messages to users and developers. Overcomes the limitations of
MsgBox, Debug.Print, and Application.Statusbar. Recommended
for intermediate and advanced developers. | ||
| AltKeyList | Creates a list of the ALT key menu accelerator keys (8 KB). | ||
| AppEvent | Illustrates the use of Application
level events. (10 KB) | ||
| Arr2Col | Converts a NxM array to a single
column or row. (19 KB) | ||
| ArraySupport | This file contains a bas module file containing all the code on the Functions For VBA Arrays Page. You can download a full workbook with these procedures and demonstration code here. | ||
| Automation AddIn Project Files | This contains all the VB6 project files to create the Automation Add-In described on the Creating An Automation Add-In For Excel page. | ||
| Calendar | A Calendar workbook. Allows you
to have special days (birthdays, holidays, etc) automatically
highlighted. (36 KB). NOTE: This file requires that
both the Analysis Tool Pack and the
Analysis Tool Pack VBA add-in libraries
be loaded. Otherwise, you'll get run time errors. | ||
| Case Convert | This add-in will allow you to perform a variety of text conversions (UPPER CASE, lower case, Title Case, Sentence case, etc) on a range of cells. Install the Add-In, and then choose Text Convert from the Excel Tools menu. | ||
| Cell View | This add-in
allows you to see the characters and their codes in a cell. This
makes it easy to find unprintable characters such as tabs that don't
display but may affect formulas. | ||
| Clock | This workbook shows how to create and manipulate Shape objects on a worksheet to create an analog clock that ticks every second. (17 KB). | ||
| CloneFolder | This page describes code that will make a
copy of a folder, including all files and subfolder within the
source folder. All files and subfolders of the source folder are
copied to the destination folder, and the subfolder hierarchy
(subfolders within subfolder within subfolders) is preserved in the
destination folder. | ||
| Collections And Dictionarys Module and Workbook | These downloads contain the code for
the procedures described on the Collection And Dictionary
Procedures page. | ||
| COM Add-In Example | This contains all the VB6 project files for the COM Add-In for PowerPoint and Excel described on the Writing COM Add-Ins In VB6 page. | ||
| COM Add-In Installer | This
workbook automates the installation of COM Add-Ins, writing all the
appropriate registry keys and values to install a COM Add-In,
described on the Writing COM Add-Ins In VB6 page. | ||
| Compare | This Add-In utility allows you to compare the contents of two workbooks. This was written by Myrna Larson and Bill Manville. | ||
| Daylight Savings Time | This download is a bas module file containing the code on the Daylight Savings Time page. | ||
| Deleting Duplicates With Filter | This contains the bas code module containing the DeleteDuplicatesViaFilter function described on the Deleting Duplicates With Advanced Filter page. | ||
| DirTree.xls and DirTree.xla | These files contain the code, in workbook or XLA Add-In format, to create a listing of subfolders and files of a specified folder, as described on the Create A Folder Or File Tree page. The DirTree.xla is distributed as a setup exe package. | ||
| DistributedDates | A file illustrating the formulas on
the Distributed Dates page.
(10KB) NOTE: This file requires
the Analysis Tool Pack | ||
| This contains the example workbook
for the DLLOfComAddin function described on the DLLOfComAddin page. You can
download only the bas module
file here. The bas module is completely self-contained. It can
be imported and used in a VBA project with no modification or
additional code. | |||
| DynaRange | Using defined names to refer to
dynamic ranges. (6 KB) | ||
| Empty Folder | This download contains a VBA module with the code described on the Empty Folder page. | ||
| EventSeq | Demonstrates the sequence of all
Excel97 events. (14 KB) | ||
| These downloads contain
the bas module and a complete workbook illustrataing the code on the
Get/Set File Times page.
| |||
| FirstLast | Extracting first and last names from full names. (6
KB)
| ||
| Folder And File Tree Module and Workbook | These files contain the code described on the Creating A Folder/File Tree List page. | ||
| FormPosition | Excel typically
displays forms in the center of the screen. This is usually
fine for data entry and dialog forms. However, in many cases
it is desirable to display a form in relation to a specific
cell. This is not a simple a task as it seems because the top
and left coordinates of a UserForm based on the same coordinate
system as the Top and Left coordinates of a cell. To properly
calculate the Top and Left coordinates of a UserForm, you have to
take in to account the window state (normal or maximized) of the
Excel application window, and the Workbook window, and their
relative positions, in addition to whether the formula bar is
visible, what command bars are displayed, and how they are
positioned. Needless to say, these calculation can get
rather complicated. Fortunately,
I've done the work for you. (34KB) | ||
| FormatMessage | This download contains the modFormatMessage code module that contains the code on the FormatMessage page. | ||
| GetInfo | The GetInfo
function, written in VBA, allows you to retrieve nearly any property
of any object in the Excel object model directly from a worksheet
cell. Using this one function, you can eliminate dozens of
custom UDFs to get application and object properties. | ||
| GetSetProps
Module GetSetProps Workbook | The
GetSetProps Module download contains the modGetSetProps code module
and the CPropType class module used in the procedures described on
the Truly Global Varaibles In
VBA page. The GetSetProps Workbook download contains a fully function workbook with test and demonstration procedures for the function described on the Truly Global Variables In VBA page. | ||
| GetSpecialFolders | This is the code module that contains F_7_AB_1_UserProfileFolder and F_7_AB_1_GetSpecialFolder, that are used to retrieve the names of user-specific folders such as My Documents. | ||
| Greenwich Mean Time and Local Time Module and Workbook | These download contain the code to convert between Greenwich Mean Time (GMT) and local times. The functions will also convert between the various Windows Time formats. | ||
| Hidden Name Space | This zip
file contains a bas code module containing the functions described
on the Hidden Name Space page. | ||
| HiLite | Highlights the background color of the current selection.
(8 KB) NOTE: The functionality of this workbook is replaced and enhanced by the RowLiner add-in. | ||
| ImportBigFiles Sub ImportBigFile Function | These are the Sub and Function versions of the ImportBigTextFiles procedure on the Importing Large Text Or CSV Files Into Excel page. | ||
| Latitude | Latitude and Longitude data for 1200 US
Cities. (62 KB) | ||
| Latitude, Longitude, and Great Circle Distances | This workbook contains formulas for creating Great Circle Distances using latitudes and longitudes. | ||
| Lists | Working with 2 lists with duplicate entries. (11K) | ||
| MergeMaps | This isn't related to Excel,
but might be useful to users of MapPoint 2002. These modules can be
executed in any application that executes VBA. It will merge the
routes from any number of MapPoint maps in to a single "master"
map. The VBA is fully documented, and self explanatory. Of
course, this requires MapPoint 2002 (version 9). These
procedures are described in an article at http://www.mp2kmag.com/articles.asp?ArticleID=72
. The complete web source for MapPoint
information can be found at www.mp2kmag.com . If you use
MapPoint, you need this site. | ||
| MinMax | Various Minimum and Maximum
formulas. (6 KB) | ||
| Names | Returning File, Workbook, and Worksheet names to a cell.
(6 KB) | ||
| NoBlanks | Eliminating blanks from a list of data. (7 KB) | ||
| Rank | A totally new Ranking workbook, which has all the formulas
described on the Ranking Data In Lists
page. (16 KB) | ||
| OpenSafe | This add-in allows you to safely open workbooks that contains VBA code (macros) without risk. It adds a item to your File menu called "Open Safe". NOTE: The level of protection provided by this add-in is higher in Excel 2002 than Excel 2000. NOTE: Opening workbooks that contain VBA macros always entails risk, so I do not guarantee that this add-in will eliminate all possible risks. | ||
| Overtime | A file illustrating the formulas on the Working With Overtime Hours page. ( 8 KB
) | ||
| PowerPoint Slide And Shape Renamer | PowerPoint Slide And
Shape Renamer In PowerPoint, there is no way in the User Interface to assign names to Slides and Shapes. Both Slides and Shapes support a Name property, but PowerPoint provides no mechanism to assign these names. You must use code. The PowerPoint Renamer COM Add-In provides a method to assign names to Slides and Sheets. This download contains both the COM Add-In DLL file and the Help File, which describes installation and use. | ||
| ProgressReporter | A DLL file that allows you to display a simple progress indicator. Click here for more details. | ||
| QSortInPlace | This download contains the bas module file with the functions described on the Sorting Array With QSort page. | ||
| QSortObjectsInPlace Workbook | This download contains all the code on the Sorting Arrays Of Objects page and the example QSortObjectCompare function described on the QSortObjectCompare Example Function page. Download either the Code Modules or a fully functional workbook. | ||
| Reader | Reads cells back to you through your
sound card. Adds "Reader" to Tools menu. You should make
this into an Add-In. (272 KB, including sound files.) All
files must reside in same directory. Yes, that's my voice reading
the numbers. | ||
| RegistryWorx DLL Component | This ActiveX DLL component, described on the RegistryWorx page, provides VBA-friendly functions for working with the System Registry. | ||
| System Registry Module And Workbook | These download include the code that is described on the Functions For Working With The System Registry page. | ||
| RowLiner | This add-in allow you to have Excel automatically display row and column view lines. See the RowLiner page for more details. | ||
| SaveCopyAndZip COM Add-In | This add-in is a front end to both PKZip and WinZip. It
allows you to save a copy of any open workbook as a zip file. It
does not alter the existing workbook in any way. See this page for more details. The COM
Add-In version does not require either PKZip or WinZip, and also
adds additional security protection with password protection and
data encryption of the zip file. | ||
| SetParent | Normally, a userform floats above, and independently of, the Excel application window and the worksheet windows. This means that when you move the application window or a sheet window, the form does not move with the window; it stays in its original location on the screen. You can use a series of Windows system calls to set the form as a "child" window to the worksheet window. When you do this, the form will move along with the application window and the sheet window. See the SetParent page. | ||
| ShellAndWait | This file contains the example
workbook that illustrates the ShellAndWaitSimple and
ShellAndWaitEnhanced functions described on the ShellAndWait page. You can also download
just the modShellAndWaitSimple bas
module or the modShellAndWaitEnhanced
bas module. Each of these modules is self-contained. No additional
code or changes are required. Visual Basic 6 versions of ShellAndWait are also available: Download the complete ShellAndWait VB6 Project. Download just the modShellAndWaitSimpleVB6.bas file. Download just the modShellAndWaitEnhancedVB6.bas file. The VB6 modules are entirely self-contained. Either or both may be imported to a VB6 project with no changes or additional code. | ||
| SubClassing The ActiveWindow | This
workbook illustrates in summary fashion how to use subclassing. The
actual subclassing of the desired window is done with the
SSubTmr6.dll available free from www.vbaccelerator.com/codelib/ssubtmr/ssubtmr.htm.
(VBA is just to slow to do the subclassing itself -- it can't cope
with the flood of messages sent by Windows.) The
workbook illustrates how to use to SSubTmr6.dll to receive the
messages that Windows sends to the ActiveWindow. The code looks for
the WM_VBSCROLL (vertical scroll) message and raises events
indicating when the user scrolls up or down, by either a line or
page. This workbook also illustrates how to create your own custom
events in a object module, how to raise those events with the
RaiseEvent statement, and how to receive those events in another
object module. The code comments describe how to install and configure SSubTmr6.dll on your system. Note that this DLL is required. The code won't work without it. Subclassing is a broad and complicated topic, and this workbook does not attempt to explain the entire topic. It assumes you know what subclassing is, and that you are familiar with windows handles (HWnds) and the Windows API procedures that work with them.
| ||
| StockWeb | Illustrates using Excel to download stock price
information from the web. (22 KB) | ||
| Symbolize | This workbook will display a form that allows
you to view and insert special symbols such as currency symbols into
your workbook. It works much like the "Insert Symbol" tool in
Microsoft Word. (21KB). This works only in Excel97 and
Excel2000. It will not work with Excel95 or earlier
versions. You can download a better version of this, written as a COM Add-In here. Note that COM Add-Ins are supported only by Excel 2000 or XP. They will not work in earlier versions of Excel. | ||
| SumEvery | Summing Every Nth Value in a list. (6 KB) | ||
| Temporary Files | This file contains the VBA procedures on the Working With Temporary Files page. | ||
| Test Excel Shutdown | This
download contains the VB6 project files, the DLL, and an example
workbook for the methods described on the Testing Excel Shutdown page. | ||
| TLIUtils | Provides VBA procedures for working with type
libraries, via the TypeLib Information object. Documentation of the
available procedures is available in the VBA code modules. | ||
| Unique | Counting Unique items in a list. (7 KB) | ||
| VBA Shortcut Keys | This zip file (6 KB) contains a workbook which lists
all of the keyboard shortcuts available in the VBA Editor. I think
it is complete. If you find that I've missed something, send
me an email. For a list of keyboard shortcuts within Excel, download
Excel Keyboard Shortcuts. | ||
| VBE Menus | Adding menu items to the Visual Basic Editor (15
KB) | ||
| X-Ray | I wrote this game several months
ago. It is similar to a game called "Black Box" that I used to
play in Junior High. You try to find targets by shooting paths
into a grid, and watching whether those paths hit the targets, exit
out of the grid, or are reflected back to their original cell.
Complete details are in the workbook
file. (100 KB) This works only in Excel97 and
Excel2000. It will not work with Excel95 or earlier
versions.
| ||
| XLKeys | This workbook lists all of the shortcut key combinations in Excel (8KB). Updated 23-April-2001. | ||
| XLCAI | This download has a sample COM Add-In written for Excel in Office 2000 Developer Edition. It illustrates the basic programming for a COM Add-In, including how to write menu controls, how to handle modeless forms and how to make functions in the COM Add-In available to worksheet cells (via VBA code). The actual download doesn't really do anything, so it will be of interested only to those who want to write COM Add-Ins. You must have the Developer Edition of Office 2000 to view the code. (35KB). I appreciate the assistance of Stephen Bullen of Office Automation Ltd. for clarifying a few technical matters. | ||
| XLConst | This workbook lists all the symbolic constants in Excel and their numeric equivalents. The list can be built on the fly using the TypeLib Information DLL. Note that to run the code in this file, you must have the "Trust Access to Visual Basic project" set to True. Go to the Tools menu, choose Macros, then Security. On that dialog, choose the Trusted Sources tab, and check the "Trust access to Visual Basic project" option. | ||
|
For more information, see Stephen Bullen's Office Automation and John Walkenbach's The Spreadsheet Page have an extensive library of files available for free download. I recommend you see their sites for more example workbooks.
|
||
|
|
||
|
|