Pausing A Macro To Get A User-selected Range. Category: General VBA | [Item URL] This tip describes how to pause a VBA macro so the use can select a range.
The trick is to use the InputBox function of the Application object. NOTE: Do not confuse this with VBA's InputBox function. Although these two functions have the same name, they are not the same. The Sub procedure listed below demonstrates how to pause a macro and let the user select a cell. Sub GetUserRange() Dim UserRange As Range Output = 565 Prompt = "Select a cell for the output. " The input box is shown below. Specifying a Type argument of 8 is the key to this procedure. By the way, it's not necessary to check for a valid range selection. WARNING: Make sure ScreenUpdating is turned on. Excel Tips Excel has a long history, and it continues to evolve and change. In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. All Tips Browse Tips by Category Search for Tips Tip Books Needs tips?
Events In Excel VBA. Events And Event Procedures In VBA This page describes Events and Event Procedures in VB and/or VBA.
Event programming is a very powerful tool that you can use within your VBA code to monitor user actions, take appropriate action when a user does something, or monitor the state of the application as it changes. If you are programming with your own custom classes, you can extend the functionality of these classes by defining and raising your own custom events, broadcasting the event message to any object that is listening for events from your class. Events and Event Procedures were introduced in Excel97. Earlier versions of Excel do not support events. An Event is an action initiated either by user action or by other VBA code. The events and their procedure declarations are fixed. There are many events that are defined in a Worksheet object, the Workbook object, and the Excel Application object itself. A Class module. There is no built in object to catch Application events. Developer FAQ - Subroutines. Category: General VBA | [Item URL] Note: This document was written for Excel 97 - 2000.
What's the difference between a VBA subroutine and a macro? Nothing, really. The term macro is a carry-over from the old days of spreadsheets. These terms are now used interchangeably. What's a procedure? A procedure can be either a subroutine or a function. What is a Variant data type? Variables that aren't specifically declared are assigned as a variant type, and VBA automatically converts the data to the proper type when it's used. What's the difference between a Variant array and an array of Variants?
A Variant is a special data type that can contain any kind of data -- a single value, or an array of values (that is, a Variant array). Dim X as Variant X = Array(30, 40, 50) A normal array can contain items of a specified data type, including non-typed Variants. Dim X (0 to 2) as Variant What's a type definition character? VBA lets you append a character to a variable's name to indicate the data type.