|
About This Site
This page tells a bit about the site, along with acknowledgements, and some
legalese.
Activating Excel From Other Programs VBA
If you are doing cross-application
program (such as automating Excel from a Word document or template, you
may find it useful for the user's convenience to activate Excel and set
keyboard focus to the active sheet. The AppActivate statement is not
entirely reliable. It sometimes properly activates an application, but
sometimes returns an error. It also requires that you know the window's
caption prior to calling AppActivate. The procedure on this page uses a
few Windows API calls to reliably activate Excel and set keyboard focus
to the Active Sheet.
Adding Menu Items To VBE VBA
Adding menu items to the Visual Basic Editor (VBE) isn't as easy
as it is with standard Excel toolbars. You've got to do everything though
code, including using a class module. This page describes the procedures
for adding your own menu items to the VBE menus.
Additional Resources For Excel Users Excel
VBA
This page describes additional resources where Excel users and
VBA programmers can find more detailed technical information.
Alert
VBA
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.
Analysis Tool Pack Excel
VBA
The Analysis Tool Pack is an add-in module that is provided free with Excel, and
provides dozens of addition worksheet functions related to Finance, Statistics,
and Engineering. This page describes how to install the Analysis Tool Pack
add-in module, and call its functions from VBA procedures.
Application Events VBA
Application Events are similar to standard Worksheet and Workbook events, but
occur at the Application level. All Workbook and Worksheet events can be
handled at the Application level, as can events such as adding or opening a new
workbook, or installing an add-in module. This page describes how to use
Application level events. If you're new to event procedures in general,
you may want to start with Event Procedures .
Array To Column Excel
This page describes formulas that you can use to transform an MxN range of data
into a single column or row of data. This can be very useful for charting
applications.
Array Formulas Excel
Array formulas are one of the most powerful tools in Excel. Many of the formulas
on the =EXCEL web site are array formulas. This page describes what array
formulas are, how they work, and how to use them.
Automation Add-Ins VBA
This page describes how to write Automation Add-Ins in Visual Basic 6.
Blinking Text VBA
Some users have expressed a need for blinking text. Excel doesn't natively
support blinking text, but you can make a cell's text blink by using theOnTime method.
Browse Folder VBA
This page describes two methods to display a "browse for folder" dialog box.
CALL Function Excel
The page contains procedures for calling functions from a DLL directly from a worksheet cell.
This page appears compliments of Laurent Longre.
Cell References In A Range VBA
This page describes a syntax and method for referencing various cells in a range
without using the Offset property. This syntax is both faster and easier
than either the Cells or Offset methods. This page
appears compliments of Alan Beban.
Cell Values And Displayed Text Excel VBA
When you are working in Excel, either
with formulas or with Visual Basic For Applications (VBA) code, it is
important to understand the difference between the actual value of a
cell and what is displayed on the screen. The actual value of the cell is
what Excel stores internally and what it uses in formulas and
calculations. This is not necessarily the same as what you see
displayed on the screen or printed on your reports. It is important
that you understand the difference between the two -- otherwise, your formulas
may not work as expected.
Cell View
Excel VBA
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.
Changing The Case Of Text VBA
Excel does not have a format option to display text in upper or lower case.
This page shows some VBA code that allows you to change a range of cells to
upper or lower case, or to change the case of text automatically as it is
entered.
Class Modules VBA
This page is an introduction to class modules in VBA.
Clipboard VBA
This page describes working with the Windows Clipboard from within VBA.
Cloning A Folder VBA
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.
Code Modules And Code Names VBA
A frequent mistake of beginning VBA is programmers is that they put their code
in the wrong code module. This page discusses the different types of
modules, and what code should and should not be placed in which modules.
It also discusses the Codename property of a module, and how to use (and misuse)
this property.
CodeNotes Excel VBA
This page has some general notes about the formulas and code included on the
web site, as well has hints for copying it from HTML on the pages to your own
Excel worksheets and VBA modules.
Collections And Dictionaries VBA
This page contains procedures for working with Collection and Dictionary object,
including sorting procedures.
Color Banding With Conditional Formatting Excel
You can use the Conditional Formatting tool in Excel97 and 2000 to make your
worksheets look like accounting ledgers or computer "green bar" paper,
with alternating bands of colors. By using Conditional Formatting
rather than manually formatting the cells, the color bars will remain intact
after you sort a worksheet range.
Colors VBA
Excel does not have any built-in worksheet functions for working with
the colors of cells or fonts. If you want to read or test the color of a
cell, you have to use VBA procedure. The Colors page describes several functions
for counting and summing cells based on the color of the font or
background.
COM Add-Ins, Writing COM Add-In For Office In VB6 VBA
This page is a step-by-step guide to creating COM Add-Ins in Visual Basic 6 for
multiple Office applications. The page includes a downloadable fully functional
COM Add-In for Excel and Word, with all VB6 project files.
Conditional Formatting Excel
This page describes how to use the Conditional Formatting tool,
which was added to Excel in Excel97.
Conditional Formatting Colors
VBA
Unfortunately, the Color and ColorIndex properties of a
Range don't return the color of a cell that is displayed if Conditional
formatting is applied to the cell. Nor does it allow you to determine
whether a conditional format is currently in effect for a cell. In order to determine these, you need code that will
test the format conditions. This page describes three VBA functions that
will do this for you.
Creating An Add-In
VBA
This page describes how to create an Excel add-in.
Date Add Excel
It is sometimes useful to store years an months as numbers -- e.g., 5.06 for 5
years, 6 months. This page describes how to use the DOLLARDE
and DOLLARFR functions to add years and
months.
DateDif Excel
All Excel versions since Excel5 have had a worksheet function called
DATEDIF, which allows
you to find the difference between two dates, in days, years, or months.
Unfortunately, this function wasn't documented until Excel2000. This page
describes the syntax for the DATEDIF
function.
Date Intervals Excel
This page describes several formulas for working with date intervals. Specifically, it address the questions of whether a date
falls within an interval, the number of days that two intervals overlap, and the
how many days are in one interval, excluding those days in another
interval.
Dates And Times Excel
VBA
One of the most common tasks in Excel involves working with dates and
times. This page contains extensive information about how dates and times
are stored in Excel, how to add and subtract them, and dozens of other
interesting formulas. This is one of the most popular pages on the web
site.
Date Time Entry
VBA
When you have a large number of dates of times to enter into a worksheet, you
may want to be able to enter them without using the ":" or
"/" separator character -- e.g., type 1234 to get 12:34. This
page describes the VBA code that allows you to do this.
Daylight Savings VBA
Neither Excel, VBA, nor the Windows API library provide much support for working
with Daylight Savings Time (DST) and Standard Time (STD). This page describes
several procedure that you can use to work with DST. The rules used in this code
to determine whether a date is within DST are the rules used in the USA and
Canada. European and other countries have their own rules regarding when DST
begins and ends. You can fairly easily adapt the code provided in these
procedures to use the DST rules in effect in your locale.
Default Property Of A Class
VBA
This page describes how you can specify a property of a class to be the
default property -- that is, the property that the compiler uses if a
property is not specified in code.
Debugging VBA Code
VBA
This page provides an introduction to the debugging and
diagnostic tools available in VBA.
Defined Name
Shortcut Key
VBA
Excel provides many shortcut keys, but
one that is sorely lacking is a shortcut to the
Defined Name box on the formula bar. You can use the macro on this page,
assigned to a
keystroke, to get directly to the name box.
Deleting Rows VBA
Many worksheets are used to stored lists of data. This page describes some
techniques for deleting duplicate rows in a list of data.
Diagnosing Startup Problems In Excel
Excel
Occasionally, you may encounter errors when starting
Excel that make it difficult or impossible to continue working. This
page describes steps you can take to diagnose and fix errors that occur when
Excel starts up.
Differences Between Functions And Macros VBA
As a VBA programmer, you'll need to know the difference between SUB procedures
and FUNCTION procedures. This page describes the difference, and the
limitations of each type.
Direct Connection To MSNEWS
When you use the MS Newsgroups, it is generally advantageous to connect directly
to the MSNEWS server, rather than going through your local news server.
This page explains why. This page was written by Leonard E Meads, and
appears on this web site with his generous permission.
Distributed Dates Excel
Often, Excel is used as a platform for scheduling applications. You may
need to determine how many working days in an interval fall in each month or
year in that interval. This page shows you how! For example, if a
project starts on 19-Jan and ends on 8-June, these formulas will determine
how many working days fall into each month.
Document Properties VBA
This page contains some VBA procedures for retrieving or setting the Built-In or
Custom properties of a Workbook.
Downloads Excel
VBA
The Downloads page lists the example workbooks that you can download from the
web site.
Duplicates Excel
VBA
When you have lists of data, it is often useful to identify the duplicate or
unique items in the list, or to compare two lists to one another. This
page describes a variety of techniques to do both. This is one of the most
popular pages on my web site.
Empty Folder
VBA
This page describes code to delete the contents of a folder, but leave the
folder intact. By default, it will send the contents (all files and
subfolders) to the Windows Recycle Bin, but you can specify that you want to
permanently delete the contents of the folder.
End User License Agreement (EULA) Questions And Answers
The EULA is the contract between you and Microsoft that governs how you may use
Office. This page answers a number of frequently asked questions about the
EULA.
Enum
Data Types VBA
This page introduces the Enum data type for VBA.
Error Handling VBA
This page describes the basics of error handling in VBA.
Event Procedures VBA
Event procedures are VBA procedures that are executed automatically when a
specific event occurs, such as changing a cell value, changing the selection, or
saving a workbook. This page describes the event that occur in workbooks
and worksheets and charts. For events at the application level, see
the Application Events page.
Excel
This is the main home page for the =EXCEL web site.
Excel Functions Excel
This page contains a large number or general use worksheet formulas, organized
by category. This page is no longer updated, but there's still alot of
interesting stuff to be found.
Excel Macros VBA
This page contains a large number or general use worksheet formulas, organized
by category. This page is no longer updated, but there's still alot of
interesting stuff to be found.
Excel Links
This page has links to some other Excel related sites, and to a few interesting
non-Excel sites as well.
Excel Pages Excel
VBA
This Page.
File Dates And Times
VBA
Neither Excel nor VBA provide a mechanism for retrieving or
setting the times associated with a file: Create Time, Last Accessed Time, and
Last Modified Time. This page provides two functions, GetFileDateTime and
SetFileDateTime, that provide this functionality. These functions are
VBA-Friendly wrapper functions for the GetFileTime and SetFileTime Windows API
functions. The GetFileDateTime and SetFileDateTime function handle the data
format conversions and the translation between local time and GMT time.
Find Method Of Range In VBA
VBA
You can use VBA to find data in a range of cells using
the Find method of a Range. Unfortunately, there is no FindAll method that
will return a Range object that contains references to all the cells in which
the searched-for text was found. This page provides such a procedure. Another
shortcoming of the Find method is that it does not support wildcard strings.
This page provides another method, WildCardMatchCells, which will return a Range
object referencing each cell whose contents match the provided wildcard string.
The wildcard string may be any string that is valid to use with the Like
operator.
Folder Tree
VBA
This page describes VBA code to create a tree-like listing of subfolders and
files within a specified folder.
Inserting Cells And Filling A Series
VBA
This page describes a VBA procedure that you can use to insert rows or cells and
fill in missing elements of a series of numbers. Suppose you have a partial
series of numbers in a range of cells. This code will detect missing
entries in the series, insert the appropriate number of rows or cells between
the existing entries in the series, and fill those cells with the appropriate
values.
First And Last Names Excel
VBA
A frequent task in Excel is to split full names into the first-name, last-name,
and middle-name components. This page describes both worksheet formulas
and VBA procedures to do this.
FormPosition
VBA
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.
GMT And Local Times and Windows Time Formats
VBA
There is no built-in way in VBA or Excel to convert between local time and
Greenwich Mean Time (GMT, also called UTC or Zulu Time). This pages describes
methods to convert times between local and GMT, as well as to convert between
the various time formats used by VBA and Windows. You can download a bas code
module or a complete workbook that contains the conversion procedures.
Inserting Procedures Name Automatically
Into VBA Code
VBA
This page describe a procedure named InsertProcedureNameIntoProcedures and
supporting function that you can use to automatically insert procedure names in
to procedures with a CONST statement.
Keyboard
Shortcut To Formula Bar
VBA
Depending on your option settings, you
may not have a keyboard shortcut to set focus to the Formula Bar. This pages
describes a method to create a keyboard shortcut to the Formula Bar.
Games! Excel
VBA
Yes, Excel can be fun.
GetInfo
Excel
VBA
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.
Headers And Footers
VBA
One of the big shortcoming of Excel is that you (still!) can't put cell values
in the page headers and footer, nor can you put the full file name of the
workbook. This pages has the VBA code to do both, and more.
Hidden Name Space Excel
VBA
The "Hidden Name Space" refers to an area of memory that you can use
to hold defined names that are available to all workbooks, and which are
available to workbooks even after the workbook which created the name is
closed. This page appears compliments of Laurent Longre.
Holidays Excel
VBA
This page has a number of formulas and procedures for working with holidays that
don't fall on the same date each year, such as Memorial Day or
Thanksgiving. This is especially useful for scheduling workbooks.
Importing And Exporting Text Files VBA
The VBA code on this pages is used to import data from a text file directly into
the active worksheet, and to export a range from a worksheet to a text
file. It lets you use any field delimiter you want.
Indirect Excel
The Excel INDIRECT function is much more useful than the Help files may
indicate. This page describes some of the interesting things you can do
with this under-appreciated function.
Interact Excel
This is the entry page to the Interactive Pages. If you've got Excel2000
and IE4 or better, you can see some of the formulas on this site in
action. Enter your own data, and watch the formulas do their
magic.
Julian Dates Excel
This page describes various Excel formulas for working with Julian dates (dates
in YYDDD format), including converting them to and from standard Excel dates,
and adding days to Julian dates.
Last Update Time Of Cell Excel
VBA
The formulas on this page allow you to record in one cell the last time that
another cell was changed.
Latitude And Longitude Excel
VBA
Working with geographic coordinates is fairly simple using the techniques
described on this page. There is also a formula for computing the Great
Circle Distance between two points.
ListBox Utilities VBA
This page contains about a dozen VBA procedures designed to make the MSForms
ListBox easier to use. The module includes procedures for moving sets of list
items up or down in the list box and a sorting functions.
Lookup Formulas Excel
This pages describes some formulas to handle looking up data in lists that
VLOOKUP can't handle.
Make Multiple Directories
VBA
The VBA function MkDir allows you to only create the right-most directory in a
path specification, and all the directories on the left must already exist. The
MakeDirMulti function described on this page allows you to create a subdirectory
nested as deep as you like, and all appropriate subfolders will be created as
required.
Menus In Excel Excel
VBA
This page is an introduction to creating custom menu items in Excel97 and 2000,
both manually and with VBA
code.
Name Box
VBA
If you use defined names in your workbooks, you have probably
been frustrated by the fact that the Name dropdown box just isn't wide enough to
fully display the names. This page describes some code you can use to increase
the width of the defined name dropdown box.
Named Ranges Excel
VBA
You can use a defined name to refer to a range or formula in your workbook,
which makes your workbook much more maintainable. Defined names can also
be used to circumvent some of the limitations in Excel's Data Validation and
Conditional Formatting tools. Learn all about defined names and names range on
this page.
Nested IF Functions Excel
It is a well known, and often irritating, limitation in Excel that you cannot
nest more than 7 levels of IF statement. This page describes a technique
using defined names that lets you get around this limitation.
New Posters' Guide Excel
VBA
Many people learn about my web site through my participation in the Excel
related newsgroups on Usenet. If you're new to the Excel newsgroups,
please take the time to read these helpful hints and tips for new posters.
No Blanks Excel
This page has an interesting formula for extracting only the non-blank values
from a list of data, and returning them to another range, without the blank
cells.
Optimizing VBA Code VBA
As your VBA projects get larger and more complex, it becomes more important that
your code is written to be as efficient as possible. Learn about several
techniques you can use to improve the speed (and reliability) of your VBA
code.
Ordinal Numbers In Excel Excel
VBA
Ordinal numbers are those which indicate order in a series, such
as 1st or 2nd or 3rd. This page describes how to create those suffixes with
either a worksheet formula or a VBA function.
Parsing Telephone Numbers
VBA
A frequent task in Excel is to split full telephone numbers into the area code,
local number, and extension components. This page describes VBA procedures to do this.
Pivot Tables Excel
Pivot tables are an exciting and powerful tool in Excel. This Introduction
was written by Harald Staff.
Preventing Duplicates On Entry VBA
Learn how to use Excel's Data Validation tool to prevent users from entering
duplicate values into a list of data.
Pricing Formulas For Bracket And Progressive Models
Excel
In many business, especially manufacturing and wholesaling, the unit price of an
item may determined by the number of units purchased. A widget may cost
$2.00 when 1 to 9 are purchased, but may cost $1.50 when between 10 and 99 are
purchased, and so on, with greater discounts given to larger quantities.
There are two methods to determine the total cost of the order. The
simpler is when the the unit price is the same for all units in the order, and
depends only on the number of units ordered. For example, this would be
the case if 50 widgets were purchased each at $1.50, where the $1.50 unit cost
came as a result of the total quantity of 50. Here, the unit cost can be
determined by a simple VLOOKUP
function (as will be shown later), and the total cost of the order is simply
unit-cost times quantity, or $1.50 * 50 = $75.00.
However, it becomes more difficult when the unit price is distributed across
the order. For example, suppose that in a order of 50 widgets, the first 9
are priced at $2.00, and the remaining 41 are priced at $1.50. In this
example, there is no single unit cost. The total cost of the order is
($2.00 * 9) + ($1.50 * 41) = $79.50. While it is simple enough to do
this example by hand, it becomes more complicated when there are a large number
of quantity ranges, each with a different unit price.
This page describes formulas for Excel that can be used to determine these
costs. We'll call the first method "Bracket Pricing", in which
the total order is based on a single unit-cost, and we'll call the second method
"Progressive Pricing" in which the price of each unit sold is based on
where it falls into various "quantity intervals".
Programming To The VBE VBA
This page describes some of the objects, properties, and methods of the Visual
Basic Editor, and some procedures you can use add and delete code modules and
procedures, all programmatically with VBA code.
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.
Random Numbers In Excel VBA
This page describes procedures for getting arrays of random Long
integers between a specified minimum and maximum. The page includes a
function that will return an array of integers with no duplicate
entries.
Ranking Data In Lists Excel
People often use Excel to store lists of data which need to be ranked.
For example, you may use Excel to store information about your bowling team, and
you want to determine the ranks for players and scores. Excel provides a
worksheet function called RANK
to do some fairly simple ranking, but this function has some limitations.
This page describes some more advanced formulas for ranking data.
Recursive Programming And The FileSystemObject
VBA
Recursive programming, in which a procedure calls itself, it a
powerful programming technique. This page describes recursive programming using
the FileSystemObject to create a directory tree listing.
Recycling Files
VBA
This page describes how to send a file to the recycle bin.
Registry
VBA
If you need to store information from one Excel session to the next, such as
user preferences or application configuration data, you can store that data in
the System Registry. This page describes about 10 VBA functions the are
used to read and write keys and values to the System Registry. The procedures
presented here should give you full control over the registry. It is assumed
that you are familiar, at least at a conceptual level, with the system Registry.
Referencing Worksheets From Formulas Excel
VBA
This page describes some VBA procedure that you can use to
refer to the first, next, previous, or last worksheet in a workbook.
These functions are intended to be called directly
from worksheet cell, not from other VBA procedures. Because they make use
of the Application.Caller
property, they will not work unless they are called directly from worksheet
cells.
Relative References Excel
This is an explanation of the differences between absolute and relative
reference styles in Excel formulas. Learn about the differences, and
understand when you should use one or the other.
Returning Arrays Excel
VBA
The page describes how to return an array of values from a VBA function.
Learn to make your custom functions behave like array formulas.
Rounding Errors In Excel97 Excel
VBA
This page describes the common causes of apparent numeric errors in Excel: actual versus
displayed values, and approximation of rational numbers in IEEE double precision floating
point variables. The IEEE format is described at the bit level.
RowLiner
This pages describes the RowLiner add-in,
which allows you to display row and/or column leader lines on your worksheet.
SaveCopyAndZip Add-In
This add-in is a front end to both the PKZip and WinZip programs. It
allows you to save as a zip file any open workbook. It does not alter the
existing workbook in any way. You must have either PKZip or WinZip installed on
your machine.
Scheduling Procedures With OnTime VBA
You may need to design your Excel workbooks to run a procedure
periodically, and automatically. For example, you may want to refresh data
from a data base source every few minutes. Using VBA, you can call upon
the OnTime method of the Excel Application object to instruct Excel to run a
procedure at a given time. By writing your code to call the
OnTime method by itself, you can have VBA code automatically execute on a
periodic basis. This page describes the VBA procedures for doing
this.
Search Excel
VBA
The Search page allows you to search for specific words or phrases on the web
site. Together with the Topic Index page, this page
lets you find what you need to know.
SetParent
VBA
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.
Shortcut Keys Excel
VBA
This page has three downloadable workbooks, listing the Excel keyboard
shortcuts, VBA Editor keyboard shortcuts, and the ALT key menu accelerator
keyboard shortcuts.
ShowAnyForm
VBA
This pages describes how to show a UserForm that is to be determined at
runtime. It utilizes the often forgotten about VBA.UserForms object and
CallByName to access a form and its controls using string variables
whose values are set a run time.
Site Stats
Who are the readers? The Site Stats page tells
you about who you guys and gals are, and where you come from.
Sorting By Color Excel
VBA
If you have color-code cells in your worksheet, you find that at
times it is useful to sort rows by the colors of the cells. That is, sort
all the reds at the top, followed by the blues, followed by the yellows, and so
on. Unfortunately, Excel
provides no such tool. You have to do it manually. This page describes
how to do it.
Sorting Worksheets In A
Workbook VBA
In some applications, it may be useful to
have the worksheets in alphabetical order. For example, if you have
a worksheet for each employee on a team and each employee has their own
worksheet, you may want these sheets in alphabetical order. You could do
this manually, but if you have more than a few sheets, it would be easier
to automate the task. Excel does not have a built in tool to
do this, but you can use some fairly simple VBA code accomplish
this.
Special Characters In Excel
Excel
Using a typical computer
keyboard, you can directly type in approximately 94 different characters,
such as letters and number other symbols like punctuation marks.
However, there are many other symbols available. In a typical font
such as Arial, there are some 200 different symbols available, including
the British Pound sign �, the Euro Currency symbol �, and the copyright
mark �. These symbols cannot be directly entered with a single key
on a typical (US) keyboard. This page describes how to work
with these special characters in Excel. You can download an add-in file
that makes it easy to view and insert symbols, even if you don't know the code
numbers.
Status Bar
VBA
This page describes a few quick hints about working with the Excel status bar,
including how to clear your message from it after a set amount of time.
Support Excel
VBA
When you're new to Excel, and even when you're not, you'll find that you need
answers to questions. This page describes how to get answers from various
sources on the internet.
Suppressing Events In A UserForm VBA
The Application.EnableEvents property does not apply to events of controls on a
UserForm. This pages describes how to write your own EnableEvents property for a
UserForm.
Test Excel Shutdown
VBA
This page describes a method using a COM Add-In to detect and
take action when the Excel application itself shuts down.
Testing The State Of The SHIFT, CTRL, and ALT Keys
VBA
This pages contains 3 VBA functions to test whether the SHIFT, CTRL, or ALT keys
are pressed. For each key, you can test whether only the Left, only the Right,
either the Left or Right, or both the Left and Right keys are pressed.
Time Zones In VBA VBA
Excel and VBA do not provide a direct way to work with either Time Zones or
Daylight Savings Time. This page describes how to read this information
from Windows. Also, you can download a workbook containing a class module
which simplifies all of the Windows API calls, allowing you to access a few
simple properties.
TextBox VBA
If you use Userforms in VBA, you almost certainly use the TextBox control.
Unfortunately, there is no property that allows you to restrict entry to certain
characters, such as numeric only. This page describes some VBA code that
restricts data entry to numeric (and supporting) characters only.
Timed Close VBA
You may find that your application should close a file if there has been no
user activity on that workbook for a specified period of time. That is, if the
user hasn't accessed the open workbook for so many minutes, save and close the
workbook. This can be achieved using event procedures
and the OnTime method.
Tools
For Excel Excel
VBA
This page contains descriptions and links to the "must have" tools for
Excel users and developers. These are tools written by others, which I use
every day. All are free to download and use.
Topic Index Excel
VBA
This page lists, alphabetically, all of the topics covered on the web site, each
linked to the page where that topic is discussed. Together with the Search
page, this page lets you find what you need to know.
UnSelecting A Cell Or Area Excel
VBA
This page describes VBA procedures to remove a cell or area from a
non-contiguous selection of cells or ranges. For example, if you've
selected A1, A3, A5, and A7, and you want to remove A5 from the selection, this
code can do it for you.
Using Variables (Properly) In VBA VBA
This page describes the "do's and don'ts" of declaring variables and
objects in VBA code, in order to make your code as efficient and solid as
possible.
Versions Of Exce Excel
l
This pages describes some of the changes between the various versions of
Microsoft Excel.
Wait For File Close
VBA
If you are working in a networked environment, you may
need to open and modify data in another workbook. If that workbook is open
by another user,
you will be to open it, but only in read-only mode. You won't be able to
update the workbook. This page describes code that you can use to pause your
process to wait for an open file to be closed.
Week Numbers Excel
VBA
Some business applications use week
numbers to categorize dates. For example, a business may report
sales amounts by week, and identify each period as "9912",
representing the 12th week of 1999. While this may be convenient in
some applications, you need to be careful when using week
numbers. When does week 1 start? On the first day of the year?
As the first full 7 day week of the year? The first week having more than
3 days? Moreover, what day does a week begin on? Sunday or
Monday? This page describes both worksheet formulas and VBA
functions for working with week numbers.
What's New? Excel
VBA
Here's where you'll find what's new on the web site -- new topics, new pages,
and new downloadable files. Check this page often to be sure you don't
miss anything important.
Who The Hell Is Chip?
This page answers your questions about who I am and why I do this.
Word Count
VBA
This page describes a macro to count the number of words in worksheet.
Working With Lists Excel
This page describes a number of formulas for using SUM, MIN, MAX, and AVERAGE
functions to ignore zero values, or negative values, or values that fall outside
of an interval. Also included are formulas to reverse the order of a
column or row, and to transpose a column or row.
Working With Overtime Hours In Timesheets Excel
This page describes a number of formulas for working with regular and overtime
hours in worksheets, including excluding time taken for meal breaks. This
is a frequent task in Excel, and this page brings together in one example many
of the other formulas found in the date and time related pages.
Zooming And Centering On A Range VBA
This page describes VBA procedures that you can use to zoom the screen on a
range of cells, and to center a cell or range of cells in the center of the
screen.
|