Dick Kusleika - Excel Macros For Dummies

1 iii

2 iv

3 1

4 2

5 3

6 4

7 5

8 7

9 8

10 9

11 10

12 11

13 12

14 13

15 14

16 15

17 16

18 17

19 18

20 19

21 20

22 21

23 22

24 23

25 24

26 25

27 26

28 27

29 28

30 29

31 30

32 31

33 32

34 33

35 34

36 35

37 36

38 37

39 38

40 39

41 40

42 41

43 42

44 43

45 44

46 45

47 46

48 47

49 48

50 49

51 50

52 51

53 52

54 53

55 54

56 55

57 57

58 58

59 59

60 60

61 61

62 62

63 63

64 64

65 65

66 66

67 67

68 68

69 69

70 70

71 71

72 72

73 73

74 74

75 75

76 76

77 77

78 78

79 79

80 80

81 81

82 82

83 83

84 84

85 85

86 86

87 87

88 88

89 89

90 90

91 91

92 92

93 93

94 94

95 95

96 96

97 97

98 98

99 99

100 100

101 101

102 102

103 103

104 104

105 105

106 107

107 108

108 109

109 110

110 111

111 112

112 113

113 114

114 115

115 116

116 117

117 118

118 119

119 120

120 121

121 122

122 123

123 124

124 125

125 126

126 127

127 128

128 129

129 130

130 131

131 132

132 133

133 134

134 135

135 136

136 137

137 138

138 139

139 140

140 141

141 142

142 143

143 144

144 145

145 146

146 147

147 148

148 149

149 150

150 151

151 152

152 153

153 154

154 155

155 156

156 157

157 158

158 159

159 160

160 161

161 162

162 163

163 164

164 165

165 166

166 167

167 168

168 169

169 170

170 171

171 172

172 173

173 174

174 175

175 176

176 177

177 178

178 179

179 180

180 181

181 182

182 183

183 184

184 185

185 186

186 187

187 188

188 189

189 190

190 191

191 192

192 193

193 194

194 195

195 196

196 197

197 199

198 200

199 201

200 202

201 203

202 204

203 205

204 206

205 207

206 208

207 209

208 210

209 211

210 212

211 213

212 214

213 215

214 217

215 218

216 219

217 220

218 221

219 222

220 223

221 224

222 225

223 226

224 227

225 228

226 229

227 230

228 231

229 232

230 233

231 234

232 235

233 236

234 237

235 238

236 239

237 240

238 241

239 242

240 243

241 244

242 245

243 246

244 247

245 248

246 249

247 250

248 251

249 252

250 253

251 254

252 255

253 256

254 257

255 258

256 259

257 260

258 261

259 263

260 264

261 265

262 266

263 267

264 268

265 269

266 270

267 271

268 272

269 273

270 274

271 275

272 276

273 277

274 278

275 283

276 284

277 286

278 287

In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You might create a macro, for example, to format and print a month-end sales report. After you develop the macro, you can execute it to perform many time-consuming procedures automatically.

Macros are written in VBA, which stands for Visual Basic for Applications. VBA is a programming language developed by Microsoft and a tool used to develop programs that control Excel.

Excel programming terminology can be a bit confusing. For example, VBA is a programming language but also serves as a macro language. What do you call something written in VBA and executed in Excel? Is it a macro or is it a program? Excel’s Help system often refers to VBA procedures as macros, so this is the terminology used in this book.

You also see the term automate throughout this book. This word means that a series of steps are completed automatically. For example, if you write a macro that adds color to some cells, prints the worksheet, and then removes the color, you have automated those three steps.

You’re probably aware that people use Excel for thousands of different tasks. Here are just a few examples:

Keeping lists of things, such as customer names and transactions

Budgeting and forecasting

Analyzing scientific data

Creating invoices and other forms

Developing charts from data

The list could go on and on. The point is simply that Excel is used for a wide variety of tasks, and everyone reading this book has different needs and expectations regarding Excel. One thing most readers have in common, however, is the need to automate some aspect of Excel, which is what macros (and this book) are all about.

This book approaches the topic of Excel macros with the recognition that programming VBA takes time and practice — time that you may not have right now. In fact, many analysts don’t have the luxury of taking a few weeks to become expert at VBA. So instead of the same general overview of VBA topics, this book provides some of the most commonly used real-world Excel macros.

Each section in the book outlines a common problem and provides an Excel macro to solve the problem — along with a detailed explanation of how the macro works and where to use it.

Each section presents the following:

The problem

The macro solution

How the macro works

After reading each section, you'll be able to

Immediately implement the required Excel macro

Understand how the macro works

Reuse the macro in other workbooks or with other macros

The macros in this book are designed to get you up and running with VBA in the quickest way possible. Each macro tackles a common task that benefits from automation. The idea here is to learn through application. This book is designed so that you can implement the macro while getting a clear understanding of what the macro does and how it works.

I make three assumptions about you as the reader:

You've installed Microsoft Excel.

You have some familiarity with the basic concepts of data analysis, such as working with tables, aggregating data, creating formulas, referencing cells, filtering, and sorting.

You have an Internet connection so you can download the sample files.

Tip icons cover tricks or techniques related to the current discussion.

Remember icons indicate notes or asides that are important to keep in mind.

Warning icons hold critical information about pitfalls you will want to avoid.

Читать дальше