Dick Kusleika - Excel VBA Programming For Dummies

178 183

179 184

180 185

181 186

182 187

183 188

184 189

185 190

186 191

187 193

188 194

189 195

190 196

191 197

192 198

193 199

194 200

195 201

196 202

197 203

198 204

199 205

200 206

201 207

202 208

203 209

204 210

205 211

206 212

207 213

208 214

209 215

210 216

211 217

212 219

213 220

214 221

215 222

216 223

217 224

218 225

219 226

220 227

221 228

222 229

223 230

224 231

225 232

226 233

227 234

228 235

229 236

230 237

231 238

232 239

233 240

234 241

235 242

236 243

237 244

238 245

239 246

240 247

241 248

242 249

243 250

244 251

245 252

246 253

247 254

248 255

249 256

250 257

251 258

252 259

253 260

254 261

255 262

256 263

257 264

258 265

259 266

260 267

261 268

262 269

263 270

264 271

265 272

266 273

267 274

268 275

269 276

270 277

271 278

272 279

273 280

274 281

275 282

276 283

277 284

278 285

279 286

280 287

281 288

282 289

283 290

284 291

285 292

286 293

287 294

288 295

289 296

290 297

291 298

292 299

293 300

294 301

295 302

296 303

297 304

298 305

299 306

300 307

301 308

302 309

303 310

304 311

305 312

306 313

307 314

308 315

309 316

310 317

311 318

312 319

313 320

314 321

315 322

316 323

317 324

318 325

319 326

320 327

321 329

322 330

323 331

324 332

325 333

326 334

327 335

328 336

329 337

330 338

331 339

332 340

333 341

334 343

335 344

336 345

337 346

338 347

339 348

340 349

341 350

342 351

343 352

344 353

345 354

346 355

347 356

348 357

349 358

350 359

351 360

352 361

353 362

354 363

355 364

356 365

357 366

358 367

359 368

360 369

361 370

362 371

363 372

364 373

365 374

366 375

367 376

368 377

369 378

370 379

371 380

372 381

373 382

374 383

375 384

376 385

377 386

378 387

379 388

380 389

381 390

382 391

383 392

384 393

385 394

386 395

387 396

388 397

389 398

390 399

391 401

392 402

393 403

394 404

395 405

396 406

397 407

398 408

399 409

400 410

401 411

402 412

403 413

404 414

405 415

406 416

407 417

408 418

409 419

Greetings, prospective Excel programmer…

You no doubt have your reasons for picking up a book on VBA programming. Maybe you got a new job (congratulations). Maybe you’re trying to automate some of the repetitive data crunching tasks you have to do. Maybe you’re just a nerd at heart. Whatever the reason, thank you for choosing this book.

Inside, you find everything you need to get up and running with VBA fast. Even if you don’t have the foggiest idea of what programming is all about, this book can help. Unlike most programming books, this one is filled with information designed to include just what you need to know to quickly ramp your VBA programming skillset.

Go to any large bookstore (in person or online), and you’ll find many Excel books. A quick overview can help you decide whether this book is really right for you. This book

Is designed for intermediate to advanced Excel users who want to get up to speed with Visual Basic for Applications (VBA) programming.

Requires no previous programming experience.

Covers the most commonly used commands.

Is appropriate for recent versions of Excel.

Just might make you crack a smile occasionally.

If you’re using an older version of Excel, this book might be okay, but some things have changed. You’d probably be better off with the preceding edition.

Oh, yeah — this is not an introductory Excel book. If you’re looking for a general-purpose Excel book, check out either of the following books, which are both published by Wiley:

Excel 2019 For Dummies, by Greg Harvey

Excel Bible, by Michael Alexander and Dick Kusleika

These books are also available in editions for earlier versions of Excel.

Notice that the title of this book isn’t The Complete Guide to Excel VBA Programming For Dummies. This book doesn’t cover all aspects of Excel programming — but then again, you probably don’t want to know everything about this topic.

If you consume this book and find that you’re hungry for a more comprehensive Excel programming book, you might try Microsoft Excel 2019 Power Programming with VBA, also published by Wiley. And yes, editions for older versions of Excel are also available.

To make the content more accessible, I divided this book into six parts:

Part 1 , Starting with Excel VBA Programming

Part 2 , Employing VBA with Excel

Part 3 , Programming Concepts

Part 4 , Communicating with Your Users

Part 5 , Putting It All Together

Part 6 , The Part of Tens

Sometimes, I refer to key combinations — which means you hold down one key while you press another. For example, Ctrl+Z means you hold down the Ctrl key while you press Z.

For menu commands, I use a distinctive character to separate items on the Ribbon or menu. For example, you use the following command to create a named range in a worksheet:

Formulas ⇒ Defined Names ⇒ Define Name

Formulas is the tab at the top of the Ribbon, Defined Names is the Ribbon group, and Define Name is the Ribbon tool you click.

The Visual Basic Editor still uses old-fashioned menus and toolbars. So Tools ⇒ Options means choose the Tools menu and then choose the Options menu item.

Excel programming involves developing code — that is, the instructions VBA follows. All code in this book appears in a monospace font, like this:

Range("A1:A12").Select

Some long lines of code don’t fit between the margins in this book. In such cases, I use the standard VBA line-continuation character sequence: a space followed by an underscore character. Here’s an example:

Selection.PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False

When you enter this code, you can type it as written or place it on a single line (omitting the space and underscore combination).

It's a cruel world out there. It seems that some scam artist is always trying to take advantage of you or cause some type of problem. The world of computing is equally cruel. You probably know about computer viruses, which can cause some nasty things to happen to your system. But did you know that computer viruses can also reside in an Excel file? It's true. In fact, it’s relatively easy to write a computer virus by using VBA. An unknowing user can open an Excel file and spread the virus to other Excel workbooks and to other systems.

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