Chapter 1 Introduction to VBA Development in Excel

Introduction • Excel is a powerful modeling tool. • The goal of this course is to teach how to develop applications in Excel using VBA. • It should be noted it is important to document your application. • You should clearly indicate input cells so that other users will know which cells they should enter their own inputs in, and which they should leave alone.

Developing Applications for the Less Technical Personnel • • • Developing the spreadsheet model for less technical people will probably be incomprehensible to the user. The user might know what the model outcome is but not be aware how the outcome is achieved. You job becomes one of developing a user-friendly model (hidden from the user) with a front end and back end. The front end will present the user with dialog boxes or some other means for allowing them to define their problem. The application will take these choices, build the appropriate model, optimize it if necessary, and present the back end to the user: a nontechnical report, with accompanying charts as an example. VBA provides the program language which allows us to create efficient models. This is not necessarily an easy task or completed quickly. Some models are easier to complete than others. Developing a good model requires: a logical mind; willingness to experiment; take full advantage of online help; plenty of practice; and above all perseverance.

Basic Spreadsheet Modeling Concepts • • • Spreadsheet Model is composed of inputs, decision variables, and outputs. Model inputs are given values that are fixed, at least for the purposes of the model. Decision variables are values that a decision maker has control over. Module outputs are the ultimate values of interest; they are determined by the inputs and decision variables. Example: Manager needs to place an order for a certain product. This product will go out of date fairly soon, so this is the only order for the product. Inputs are the fixed cost of the order, the unit variable cost of each item ordered, the price charged for each item sold, and the salvage value for each item, if any are left in inventory after the product has gone out of date. The decision variable is the number of items to order. The main output is the profit or loss from the product. In order to obtain the profit the following will have to be calculated: total ordering cost, revenue from sales, and the salvage value from any leftover items. Spreadsheet Applications are composed of front ends and back ends. Using this format, you can create applications for the inexperienced user where they are not concerned with the underlying code only the results.

Basic Spreadsheet Modeling Concepts • Spreadsheet modeling is the process of entering the inputs and decision variables into a spreadsheet. • Relate the variables by means of formulas to obtain the outputs. • Getting all of the spreadsheet logic correct and producing useful results is a big part of the battle. • Another part is the use of good spreadsheet habits. • Generally, other people will probably be reading and trying to make sense out of your spreadsheet model. • These readability features will help: – A clear, logical layout to the overall model – Separation of different parts of a model across multiple worksheets – Clear headings for different sections of the model and for all inputs , decision variables, and outputs – Liberal use of range names – Liberal use of boldface, italics, larger font size, coloring, indentation, and other formatting features – Liberal use of cell comments – Liberal use of text boxes for assumptions, lists, or explanations

Visual Basic Versus VBA • • Visual Basic and VBA are not the same. Visual Basic is a programming product that runs without Excel. VBA is included with Microsoft Office. VBA language consists of a backbone programming language, with typical programming elements you find in all programming languages: – – – • • • Logical If-Then-Else constructions Arrays Subroutines Variable types Form construction (interface design) Any application software package (i. e. Excel, Word, Access) or non-Microsoftware package can be used by VBA to develop applications programmatically. Excel objects consist of ranges, worksheets, and charts to name a few. VBA for Excel will recognize these objects and is capable of manipulating them with code. Visual Basic cannot be used to strictly manipulate Excel objects since they are not known by the programming language. VBA can also be used with Word, Access, and Power. Point. Each version of VBA shares the same backbone language, but each requires you to learn how to manipulate the objects in the specific application