|Excel 2019 Programming By Example (Mercury Learning)
Author: Julitta Korol
Does anyone want to write in VBA and XML for Excel any longer? Yes, because it's one of the last areas of freedom where the talented user can do amazing things.
Excel is a fascinating piece of software, partly because it's easy enough for any computer user to use in a very basic way, has functions and features to do powerful data analysis, and once you add VBA into the mix the possibilities are scary.
This means if someone asks you to have a quick look at a problem they have with Excel, you can't be sure whether they've misunderstood the Sum function, or you'll discover a massive application written in thousands of lines of VBA code to do something obscure and specialized that you have no hope of understanding.
This book is a reworking of an existing title that began life several generations of Excel ago, but the examples and material have been updated for Excel 2019. It's problem is that it's essentially a book about learning to program as a general skill while working in Excel, rather than a book on making Excel fly by programming it in VBA.
The book opens with a chapter on getting started with VBA via Excel recorded macros. It's an understandable decision because that's the way many Excel power users get into VBA - record macros, then want to improve those macros. It's not that useful if you can already program, but for a major subset of the readers it's what's needed.
Chapters on the Excel programming environment, a quick reference to writing Excel code, and writing VBA procedures are all aimed at this audience, along with introductions the conditional statements, looping, arrays, collections, and testing and debugging.
Part II of the book is about manipulating files and folders, which seems a little odd as for most Excel uses you can rely on Excel itself to do most of the file handling. There are chapters on file and folder manipulation, using the Windows Script Host, and low level file access for sequential and random access files.
Julitta Korol then moves on to controlling other applications with VBA. There's a chapter on interacting with other apps that introduces the ideas and shows how to create automation objects, and this is followed with a look at using Excel with Access which is probably the most likely combination. From my experience of what Excel users are trying to do with automation, this section could have been much more detailed.
Part IV of the book is titled 'enhancing the user experience', and it begins with a chapter on event-driven programming that looks at workbook events and chart events. Other chapters in this section cover dialog boxes, custom forms, formatting workbooks with VBA. custom menus and ribbons, and printing and sending emails from Excel.
Part V looks at another likely use of Excel automation - data analysis. There's a chapter on using Excel tables, followed by chapters on pivot tables and charts, and getting and transforming data in Excel.
Two chapters on 'taking charge of the programming environment' come next, looking at programming the Visual Basic editor, and calling Windows API functions from Excel. I can't imagine most Excel VBA programmers would want to automate things such as deleting code from a module or accessing Windows API documentation, but the info is there.
The final part of the book is Excel and Web technologies, with chapters on HTML programming and Web Queries, Active Server Pages, and using XML.
I had mixed feelings about this book. I felt there wasn't enough on the actual aspects of VBA programming in Excel that my imagined reader would want, and some of what is included seems likely to be of limited interest. The book lacks the key part, a detailed look at the Excel object hierarchy and how to work with it. This is vital for building real applications, and some of the other topics are just nice to have extras. Having said that, the topics that are covered are explained reasonably well, and I think it would be a good addition for anyone trying to automate Excel.
|Last Updated ( Tuesday, 20 April 2021 )