Computer Programming in Excel (VBA) Part 3: Functions ...

A SunCam online continuing education course

Computer Programming in Excel (VBA) Part 3: Functions, Procedures, and String

Variables

by

Kwabena Ofosu, Ph.D., P.E., PTOE

Introduction to Computer Programming in Visual Basic for Applications (VBA) A SunCam online continuing education course

Abstract

This course is the third of a four-part series on computer programming in Excel Visual Basic for Applications (VBA), tailored to practicing engineers. In this course, topics on functions and procedures, and working with string variables are covered. A tour of the VBA object library is also presented. Several examples relevant to engineering are used to illustrate and demonstrate the concepts and methods learned in this class. Two mini-projects are used to demonstrate the programming concepts and methods in situations encountered by practicing engineers.

Computer Programming in Visual Basic (VBA) ? Part 1 and Part 2 are not required prerequisites to this course. It would however be helpful to understand the basic principles of computer programming as well as the fundamentals of the Excel VBA language as presented in Part 1 and Part 2 of this series.

Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE

Page 2 of 91

Introduction to Computer Programming in Visual Basic for Applications (VBA) A SunCam online continuing education course

TABLE OF CONTENTS

Abstract ........................................................................................................................................... 2 List of Tables .................................................................................................................................. 5 1. PROCEDURES AND FUNCTIONS ......................................................................................... 6

1.1 Procedures............................................................................................................................. 6 1.2 Sub Procedures...................................................................................................................... 6 1.3 Naming a Procedure.............................................................................................................. 7 1.4 Event Procedures .................................................................................................................. 7 1.5 Function Procedures............................................................................................................ 12 1.6 Built-in Functions ............................................................................................................... 14 1.7 Constants............................................................................................................................. 16 1.8 Fifth VBA Project ............................................................................................................... 17 2. WORKING WITH STRINGS AND DATES........................................................................... 47 2.1 String Variables .................................................................................................................. 47 2.2 Concatenation ..................................................................................................................... 47 2.3 String Length ...................................................................................................................... 49 2.4 Left Function....................................................................................................................... 50 2.5 Right Function .................................................................................................................... 51 2.6 InStr Function ..................................................................................................................... 52 2.7 Mid Function....................................................................................................................... 53 2.8 Reversing a String............................................................................................................... 54 2.9 Upper Case and Lower Case Conversion ........................................................................... 54 2.10 Basics of Dates.................................................................................................................. 55 2.11 Date Formats ..................................................................................................................... 57 2.12 Current Date...................................................................................................................... 58 2.13 Components of a Date....................................................................................................... 58 2.14 Subtracting Dates .............................................................................................................. 60 2.15 Time .................................................................................................................................. 60 2.16 Type Conversion Functions .............................................................................................. 61 2.17 Sixth VBA Project ............................................................................................................ 63

Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE

Page 3 of 91

Introduction to Computer Programming in Visual Basic for Applications (VBA) A SunCam online continuing education course

3. OBJECTS.................................................................................................................................. 82 3.1 Object-Oriented Programming............................................................................................ 82 3.2 The VBA Object Browser................................................................................................... 83

4. CONCLUSION......................................................................................................................... 90 REFERENCES ............................................................................................................................. 91

Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE

Page 4 of 91

Introduction to Computer Programming in Visual Basic for Applications (VBA) A SunCam online continuing education course List of Tables

Table 5: Type conversion functions.............................................................................................. 62

Copyright 2012 Kwabena Ofosu, Ph.D., P.E., PTOE

Page 5 of 91

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download