Excel Macros and VBA
  Back to List of Topics
  List of Topics


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.


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.


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.


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.


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.


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.


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.


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.


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.


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.