Excel Vba Reference

Visual Basic Applications for Excel is a programming language well suited to beginners. It provides many of the Visual Basic programming facilities through the Excel application. Thus, students who have access to Excel can gain familiarity with Visual Basic without having to step up to the full blown program. With VBA, programmers have the power to customise Excel applications that would be impossible to achieve with Excel alone. VBA can often provide a faster, and sometimes easier-to-implement solution, than could be achieved with Excel alone. In particular, VBA gives you the power to automate all sorts of Excel tasks. For example, you could create an Excel workbook, add data to it and format it automatically using VBA. Excel is almost ubiquitous in the business world, and is amongst the most popular software applications ever used. These reasons make VBA for Excel an excellent choice as an introductory programming language.

I have tried to avoid pedantic use of technical programming and to concentrate instead on the understanding of core programming concepts. VBA is a large language, although much can be achieved by using only a small portion of its features. This book aims to give you a solid grasp of core principles and techniques, and an awareness of how to discover other language features so that you may learn them if and when required.

This book is ideally suited to undergraduate Business Information Technology students, although it could benefit students taking courses in Computer Science, Software Engineering, Business Studies and many other academic disciplines. It could also be beneficial to any student, without a technical computing background, looking for an introduction to programming. It could also be suitable for those who have used Excel and wish to acquire advanced knowledge about it. Some knowledge of Excel is required, but not of computer programming.

The book begins with an introductory chapter on computing. Readers who already have some understanding of computing software might skip parts, or all, of this chapter. It looks at the historical development of hardware and software and briefly describes the evolution of VBA and Excel and introduces readers to some of the jargon they will encounter in the world of programming. The chapter also outlines the advantages of using VBA for Excel.

Chapter 2 provides a general introduction to macros, and explains how to record and invoke macros without using the VBA language environment. It also looks at other ways of executing macros from Excel, such as by linking macros to buttons.

Chapter 3 introduces the VBA for Excel development environment and shows how to write and execute VBA macros. Once this chapter has been mastered, you will be able to distinguish how all the various parts of a VBA program and Excel go together to produce the working application.

Chapter 4 is about Excel objects. Object-oriented programming provides a way of developing software so that the relationship between the concepts of the real world system that is being modelled can be closely preserved within the software. It is important to introduce the concept of objects in the VBA language at an early stage, so that you can manipulate them when writing VBA macros. The VBA/Excel model contains objects such as workbooks, cell ranges, cells, and charts.

Chapter 5 deals with VBA variables and arrays. It gives many examples of how VBA supports both numeric and character data and introduced the mathematical operators that VBA recognises. This chapter also looks at user-defined variables.

It is sometimes desirable that some line(s) of code in a program are only executed if some condition is met. This is called selection and is the subject of Chapter 6. Logical operators for combining conditions will also be studied in this chapter.

Chapter 7 looks at the control structures for iteration - the repeating of lines of code in a program until some condition is met.

Chapter 8 discusses a range of testing and debugging facilities that are available in the VBA environment. The interactive debugger provides the tools to enable the student to know how to eliminate logical program errors. The chapter will also outline a test rationale and strategy for ensuring correctness of VBA programs, and look at VBA statements that control user input and other types of program error.

In Chapter 9 we look at functions. Excel users will be aware of the availability of a library of 'built-in' functions, such as those that find the total, average, or count the values of a column of data. These functions are also available to the VBA programmer, eliminating the need for coding repetitious tasks. VBA programmers can create their own functions and add them to the built-in function library of VBA, facilitating reuse and speeding up subsequent program development.

Chapter 10 is concerned with using forms in VBA programs, so that the reader will be able to create customised dialog boxes. Being a Windowsdevelopment system, VBA relies heavily on graphical screen objects, in this chapter, the reader will learn how to create and program windows components in an Excel application. This chapter will also look at other ways to improve the Excel user interface.

Writing this book has been a challenge and a pleasure. I hope that you find the same challenge and pleasure in learning to program in VBA.

Keith Darlington,

Summer 2003

0 0

Post a comment