A summary of VBA development experience: Designing a user interface with a Range object

Read the "Excel Professional Development", the biggest shock is the author of the Pursuit of VBA technology and the design of Excel works of art. Inspired by the author, he also intends to summarize some of his experiences in daily development, as a reserve of knowledge and to share it on the Internet.

Excel formulas, functions, and the formatting of their own cells provide a great convenience for the design of the UI, which is detailed in the Excel Professional Development section. In this book, the predefined names are also discussed. It is mentioned that the predefined names are an important part of the design of the user interface of the worksheet, including predefined named constants, named ranges, and named formulas. This article will focus on the role of named areas in UI design to make a summary.

1, according to the specific data requirements can be named in different regions, to ensure the independence of each part of the information, convenient data validation or regional verification requirements, such as in the sales analysis of customer information and sales information are named, respectively, analysis of two parts of the data or information.

In this way, determine if the relevant named range is selected to take some functions related to the region:

Application.intersect   Application.Union

2. Each column item can be indexed in an enumerated type within a named range, so that the UI changes uniformly when changed.

3, flexible use relative reference to absolute reference.

4, using the Protect method of the worksheet to protect the UI, prevent user actions inadvertently input bad data, breach of consistency.

5. Use the constant variable that sets the header row and data row to public.

6, the use of the number of rows into the properties of the worksheet, easy to call.

PS several special tips for VBA:

1, if the VBA does not support the condition short circuit;

2. Open the file using Excel from a different process.

 with Application    False     truetrue     End with

A summary of VBA development experience: Designing a user interface with a Range object