Brilliant Excel VBA programming : [what you need to know ...

Brilliant Excel

VBA Programming

Curtis Frye

PEARSON

Harlow, England ? London ? New York ? Boston ? San Francisco ? Toronto ? Sydney ? Auckland ? Singapore ? Hong Kong Tokyo ? Seoul ? Taipei ? New Delhi ? Cape Town ? Sao Paulo ? Mexico City ? Madrid ? Amsterdam Munich ? Paris * Milan

Contents

Introduction

1. Recording and running macros

Record a macro Run a macro Edit a macro Delete a macro

Record a macro using relative references Assign a macro to a keyboard shortcut Run a macro by clicking a shape

Add a macro to the Quick Access Toolbar Customise a Quick Access Toolbar button Save a macro-enabled workbook

Manage Excel 2010 security settings Change Protected View settings Change message bar settings Change data connection security settings Add a digital signature to a workbook

2. Starting with the Visual Basic Editor

Introduce object-orientated programming Display the Developer ribbon tab Display the Visual Basic Editor Set project properties

Create a code module Create a subroutine Create a function Add a comment to your code Run a VBA routine Rename a code module Delete a code module

Export a code module to a text file

xiii

1

2 3 4 6 7 9 11 12 14 16 17 19 21 22 24

27

28 31 32 33 34 35 36 37 38 39 40 41

3. Working with data and variables

43

Understand data types in Excel VBA

44

Declare a variable

45

Require variable declaration before use

46

Manage variable scope

47

Perform calculations using mathematical operators

48

Define a constant

49

Define a static variable

50

Define an array

51

Define a multidimensional array

52

Redefine an array

53

Define a dynamic array

54

Display an object type

55

Define an object variable

56

4. Managing workbooks and files

uv

Open a workbook

58

Open a text file as a workbook

59

Open a file the user selects

60

Save a workbook

61

Save a workbook in a different format

62

Detect if a workbook is open

63

Close a workbook

65

Create a new workbook

66

Delete a file

67

5. Managing worksheets

69

Add a worksheet

70

Delete a worksheet

71

Move a worksheet

72

Copy a worksheet

73

Hide or unhide a worksheet

74

Rename a worksheet

75

Protect a worksheet

76

Print a worksheet

77

6. Managing ranges

79

Activate a cell range

80

Select a cell range

81

viii

Select the active region Refer to cells using Of f set

Insert a cell range Delete a cell range Hide worksheet columns or rows Create a named range Resize a selected range Set the column width

Set the row height

7. Managing cells

Cut and paste a cell range Copy and paste a cell range Copy and paste values in cells using PasteSpecial Transpose a column into a row Create a cell comment Display a cell's comment Hide a cell's comment Delete one or all cell comments Fill a range of cells automatically Copy a range to multiple sheets Add a cell border Find a cell value Replace a cell value

8. Farmatting worksheets ancS worksheet elements Apply bold, italic and underline formatting

Change a cell's font Change a cell's font size Change a cell's font colour Change a cell's fill colour Change a cell's alignment Apply a cell style Apply a number format to a cell Clear a cell's format

9. Sorting and filtering data

Sort cell data using a single criterion

Create a multilevel sort

Sort using a customised list of values

82 83 84 85 86 88 89 90 92

93

94 95 96 98 99 100 101 102 103 105 106 109 111

113

114 116 118 119 121 123 125 127 128

131

132 136 140

ix

Turn on filter arrows using VBA code Apply a filter using a single criterion

Remove a filter

Display a list of unique values Filter data to display two values in a column Filter data to display three or more values in a column Filter data based on values in multiple columns

10. Managing charts

Create a chart Move a chart to a chart sheet Add a new data series to a chart

Format a chart's legend text

Format a chart's axis text

Export a chart as an image Create a Line sparkline Create a Column sparkline Create a Win/Loss sparkline Delete a sparkline

11. Using built-in functions and statements

Use the built-in Open dialog box Prevent screen flicker when running VBA code Suppress and restore alerts Calculate data using Excel worksheet functions Display a message box Get data from an InputBox Display the current date and time

Format a date

Remove spaces from a string Locate a portion of a string Concatenate strings

12. Managing code using logical constructs

Create a For...Next loop Create a For...Each loop

Create an if...Then...Eiseif statement Create a Case statement Create a Do loop

Create a Do...while loop

x

144 146 148 150 152 154 156

159

160 163 166 170 172 174 176 178 180 182

183

184 186 187 188 189 193 196 197 199 201 203

205

206 207 208 211 212 213

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

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

Google Online Preview   Download