Entering
the AutoSum Function with a Macro
If the AutoSum function
is recorded as a macro, it gives undesirable results in that
it will work with only a sum range the same size as the one
recorded. This macro will work with any size sum range.
How to
Save the Personal Macro Workbook
when
Excel is Open
The Personal macro workbook, PERSONAL.XLS, is a hidden file, and you
normally have to exit Excel to save the changes just made.
This macro allows you to hide and save the file
without exiting Excel.
A Macro
to Open a New Workbook, then Hide
and Save
it
A macro to open a new file,
adds data to the file, hides the file and saves it. This is useful
when setting up hidden sheets for a user with default type
data.
Macros
to Place the Contents of a Folder
on
the Worksheet - Dir()
These macros place the current
path, the contents of the folder and the file count on the worksheet. The
macro accepts wildcards to filter the folder's contents. This an Excel 97 file for
download and ready to use, along with instructions on how to set up your own file.
Tbe code works in Excel 2000 and XP.
Different
Navigation Methods in Excel Macros
A primer on how to move
the cell pointer in Excel macros.
Breaking
a Macro Command into Two or More Lines
In many cases a macro command
line extends beyond the width of the screen and you desire
to break the macro into two or more lines in order to see it all the
screen at one time. This article explains how to accomplish
this use the step-by-step approach.
Breaking
a Macro Command into Two or More Lines-
The
Short Version
This is a shortened version
of the preceeding article. This is written for those who are somewhat comfortable
writing macros.
Editing or
Viewing a Recorded Macro
This article shows you how
to find your macro in one quick and easy step. You will jump directly to the macro,
without regard to the module it is on.
How to Determine
the Macro Name Assigned
to a Macro
Button
You have a macro button
with a descriptive name, however you wish to to see what is assigned to
the button and then wish to jump directly to that macro.
VBA Code
to Determine if a File Exists
This code is short and straight
forward and when inserted into your macros that open files, can help prevent these
macros from crashing if the file does not exist.
How to Stop
a Looping Macro
This exercise provides one
method of stopping a looping macro. It provides other tricks used in working
with macros that process cells.
A Macro to
Determine Which Version of
Excel is
Running
This macro displays a message
box with the version of Excel that is running. It also displays the active version
of Windows
A Macro to Better Refresh Pivot Tables
This macro will ensure your pivot tables refreshes by
recognizing all changes in the data, both additions
and deletions. It recognizes the new database range,
refreshes the pivot table and re-sorts the pivot table itself.
Download
Macros to Select Ranges
This file is a small collection of simple macros to
select automatically select (highlight) Excel database
and related ranges. Download this file and learn these simple
macros. Once understood, simply copy and paste the
macro code into your workbook.
Download
Macros to Hide and UnHide Worksheets
This file is a small collection of simple macros to
Hide and Unhide worksheets. It also contains examples of
the xlVeryHide command to make the hidden worksheet
invisible in the Format Sheet UnHide menu. Download
this file and learn these simple macros. Once understood,
simply copy and paste the macro code into your workbook.
Download
A Number Pad for Use with the Mouse
This is a keyboard free number pad. Click on the number digits with the mouse
for entering numbers. A useful file for learning some Excel concepts. Possible questionable
value as a real tool, but it's a fun way to learn something new.
Download
Perform an Action on a Range
The natural tendency in writing macros is to select a range and then
perform an action on the range, perhaps to format it. But you can write
a macro to just peform the action without selecting the range
or otherwise moving the cursor. This file shows both macros along
with other instructional pointers.
Download
Validate the First Character in a Cell
A custom VBA function to test if the first text character in a cell is
is in the allowed list. In this example data is entered in Column A and the Function
and its result is in Column B.
Download
Evaluation Cells in a Range
These two macros take the contents of a cell in a range, performs action
on the contents (in this case multiplies the value by 2) and places the
result in the corresponding cell to the right. One method acts on each cell
the other acts on the entire range.
Download
Hide Rows with Zero Value
This macro starts at the top of a column, reads the cell contents, then hides
the entire row if the value is zero. It automatically stops at the bottom
of the column.
Download
Convert Trailing Minus Signs
Some downloads place the minus sign (for a negative number)
after the number. Excel will treat this as text. These macros
move the minus sign to the left of the number,
making it a true negative value.
Download
Add and Name New Worksheets from a List
You may have a list of names in which you need to create a new
worksheet for and then name the new worksheet with the name in
the list. This macro reads the name in a cell adds and names the new
sheet and repeats itself until the cursor reaches the bottom of the list.
In the right circumstances this macro is a great time saver.
Download
|
