Wiley 978-0-7645-5660-9 Datasheet download pdf

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23

Primer in Excel VBA

This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or

who are inexperienced with programming using the Visual Basic for Applications (VBA) language.

If you are already comfortable with navigating around the features provided by Excel, have used

the macro recorder, and have a working knowledge of VBA and the Visual Basic Editor (VBE), you

might want to skip straight to Chapter 3.

If this is not the case, this chapter has been designed to provide you with the information you need

to be able to move on comfortably to the more advanced features presented in the following

chapters. We will be covering the following topics:

❑

The Excel macro recorder

❑

User-deﬁned functions

❑

The Excel Object Model

❑

VBA programming concepts

Excel VBA is a programming application that allows you to use Visual Basic code to run the many

features of the Excel package, thereby allowing you to customize your Excel applications. Units of

VBA code are often referred to as macros. We will be covering more formal terminology in this

chapter, but we will continue to use the term macro as a general way to refer to any VBA code.

In your day-to-day use of Excel, if you carry out the same sequence of commands repetitively, you

can save a lot of time and effort by automating those steps using macros. If you are setting up an

application for other users, who don’t know much about Excel, you can use macros to create buttons

and dialog boxes to guide them through your application as well as automate the processes

involved.

If you are able to perform an operation manually, you can use the macro recorder to capture that

operation. This is a very quick and easy process and requires no prior knowledge of the VBA

language. Many Excel users record and run macros and feel no need to learn about VBA.

However, the recorded results might not be very ﬂexible, in that the macro can only be used to carry

out one particular task on one particular range of cells. In addition, the recorded macro is likely

COPYRIGHTED MATERIAL

1 2 3 4 5 6 ... 61 62

Summary of Contents

Page 1 - COPYRIGHTED MATERIAL

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAThis chapter is intended

Page 2 - Using the Macro Recorder

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1ProceduresIn VBA, macros are referr

Page 3

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAthe Project Explorer. Rig

Page 4

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1There are many other objects that y

Page 5 - Running Macros

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAFigure 1-11Figure 1-12Whe

Page 6

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Figure 1-13Figure 1-14To change the

Page 7

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAFigure 1-15Figure 1-1615

Page 8 - The Visual Basic Editor

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Staying in the Customize dialog box

Page 9

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAof a worksheet. Versions

Page 10 - Chapter 1

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Private Sub Workbook_BeforeClose(Ca

Page 11 - Other Ways to Run Macros

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAHere, we have created a f

Page 12

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1to run much more slowly than the co

Page 13 - Primer in Excel VBA

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Figure 1-20The InvoiceAmount() func

Page 14

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAreturn the corresponding

Page 15

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1be recalculated if you change a val

Page 16

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAapplication, you need to

Page 17

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Figure 1-21If you want to refer to

Page 18 - User Deﬁned Functions

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAThe preceding code is und

Page 19

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1You refer to the property of an obj

Page 20

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAproperty. The Text proper

Page 21

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1This is far more efﬁcient than the

Page 22 - The Excel Object Model

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAworksheets cells to no co

Page 23

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBA❑Next, think about when y

Page 24

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1View ➪ Object Browser, press F2, or

Page 25

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAFigure 1-23Immediate wind

Page 26

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Here, the Value property of the Act

Page 27

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAAn easy way to execute a

Page 28

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1If you want more information on but

Page 29 - Getting Help

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAFigure 1-27Values 16 to 6

Page 30

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1rather than a function. This is per

Page 31

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAInputBoxAnother useful VB

Page 32 - The VBA Language

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1If (SalesData <> ""

Page 33

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAstatement or one of its v

Page 34

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Call the macro MonthNames1, because

Page 35

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1at the top of the module and, there

Page 36

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAit is deﬁned. We can incr

Page 37

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1If a procedure in the module declar

Page 38 - Variable Declaration

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAData type Storage size Ra

Page 39

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1The following can be a trap:Dim Col

Page 40

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBA(A strongly typed languag

Page 41

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1This example creates a new empty wo

Page 42 - Variable Type

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAIf you ﬁnd this confusing

Page 43

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Another difference is that, while I

Page 44

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAIf Answer = vbYes ThenThi

Page 45 - Object Variables

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAFigure 1-3your macros, ho

Page 46

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Case "Pears": Price = 18C

Page 47 - Making Decisions

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBADo . . . LoopTo illustrat

Page 48

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1can, therefore, change any referenc

Page 49

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAMsgBox "Sorry, Only

Page 50

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1FilePath = ActiveWorkbook.FullNameF

Page 51

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAPublic Sub FileList()Dim

Page 52

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Message = Message & "Upper

Page 53

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAYou can think of a two-di

Page 54

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1You can declare the required size a

Page 55

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAIf you anticipate this pa

Page 56

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1Figure 1-4The same dialog box can b

Page 57

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1The code in ErrorTrap1, after execu

Page 58 - Runtime Error Handling

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAMessage = Message & &

Page 59

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1TestForName calls the NameExists fu

Page 60

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAIt is possible to assign

Page 61

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Chapter 1If you needed to resynchronize the

Page 62

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IMLWY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23Primer in Excel VBAFirst, you can run VBE by

Comments to this Manuals

No comments

Wiley 978-0-7645-5660-9 Datasheet

Browse online or download Datasheet for Software manuals Wiley 978-0-7645-5660-9. Wiley Excel 2003 VBA Programmer's Reference User Manual

Summary of Contents

Comments to this Manuals

Related products and manuals for Software manuals Wiley 978-0-7645-5660-9