VBA macro in Excel 2016 for Mac: SaveAs will not work with a CSV file format I am trying to run a VBA macro in Excel 2016 for Mac (v. 15.25) The macro works on Windows platforms with Excel 2016, and on Mac platforms with earlier than the 2016 version.
VBA for Modelers
Albright
How to Run a Macro in Excel (Run a VBA Code) Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac. Table of Content Close. Use VBA to author Power Query queries. Although authoring in the Power Query Editor is not yet available in Excel for Mac, VBA does support Power Query authoring. Here is some basic code you can adapt and use. Create a query and load its data. Here is a simple sample that adds a query that creates a list with values from 1 to 100.
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
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 2016 for the Mac, is a significant step backward in terms of VBA. For example, it initially didn’t even let you insert a module! Since then, some features have been added, including the ability to right-click a project to insert a module (or class module), but you still can’t insert a user form. (That 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.
Tables of contents:
·First edition
·Second edition
·Third edition
·Fourth edition
·Fifth edition
Errata:
·First edition
·Second edition
·Third edition
·Fourth edition
·Fifth edition
Visit the Cengagesite for our books.
Send e-mail to [email protected]
Albright is retired from the Kelley School of Business, Indiana University, Bloomington.
Back to home page
Updated: 7/1/2019
VBA for Modelers
Albright
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
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 2016 for the Mac, is a significant step backward in terms of VBA. For example, it initially didn’t even let you insert a module! Since then, some features have been added, including the ability to right-click a project to insert a module (or class module), but you still can’t insert a user form. (That 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.
Tables of contents:
·First edition
·Second edition
·Third edition
Vba Emulator For Mac
![For For](/uploads/1/2/6/6/126645913/166487401.jpg)
·Fourth edition
·Fifth edition
Errata:
·First edition
·Second edition
·Third edition
·Fourth edition
·Fifth edition
Visit the Cengagesite for our books.
Send e-mail to [email protected]
Albright is retired from the Kelley School of Business, Indiana University, Bloomington.
![Vba Vba](/uploads/1/2/6/6/126645913/521438039.jpg)
Back to home page
Vba For Mac Excel Spreadsheets
Updated: 7/1/2019