VBA for Modelers

Albright

Description: Description: Description: VBA 3e Cover

$Description: C:\Users\Chris\Dropbox\My Books\Web Site\Images\VBA4e_Cover.jpg$

VBA5e_Cover

Note to Mac users: There are evidently more of you than ever before, so you might wonder how (or whether) the material in my VBA book applies to Excel for the Mac. The short answer is that some does, and some doesn’t, and this depends on your version of Excel for the Mac. In the version before Excel 2011 (I forget what it was called), there was no VBA at all – period. Then Excel 2011 introduced a VBA environment, not as friendly or full-featured as the one for Windows, but better than nothing. Unfortunately, the most recent version, Excel in Office 365 for Mac, is a significant step backward in terms of VBA. It includes some features such as the ability to right-click a project to insert a module (or class module). However, you still can’t insert a user form, which rules out most of the second half of my book. As you can see from the rants at http://answers.microsoft.com/en-us/mac/forum/macoffice2016-macexcel/visual-basic-support-in-mac-2016/4afbba04-4435-4970-8b02-40da006a590e, serious developers find the VBA environment in Excel 2016 for the Mac woefully inadequate. Personally, until the Mac environment improves significantly, I plan to restrict my coding to Excel for Windows.

Breaking out of an infinite loop: It happens to all of us programmers. Before saving our work, the code gets stuck in an infinite loop, and we panic that we can’t save anything. My book suggests pressing Ctrl+Break to get out of the loop. Although this used to work, it’s not likely to work any longer. In fact, many (most?) keyboards no longer have a Break key. After doing a Web search, someone suggested a workaround that you can try: Keep pressing the Esc key. Then repeatedly click the Stop button on the Visual Basic Editor toolbar (the blue square), and eventually the code should stop. Actually, when I recently ran into the problem, I just kept pressing the Esc key, and eventually a dialog appeared where I could click an End button to stop the program.

Stock Query app: The Stock Query app in Chapter 32 no longer works, not even the revised “2017” version I posted here earlier. Evidently, Yahoo keeps changing its financial “historical quote” website, which keeps breaking my app. Try this one (but no promises): Stock Query.xlsm

Calendar control: This control continues to be problematic, but it appears that you can install a version using the files/instructions in this zip file: Calendar Control.zip

Tables of contents:

Errata: