Open VBA Editor. Windows shortcut. Get over 200 Excel shortcuts for Windows and Mac in one handy. And I run Exceljet with my wife, Lisa. Copy Folder in Microsoft Office for the Mac with VBA. Use VBA Kill to delete files. Use VBA to Delete all files in folder. Loop through Files in Folder on a Mac (Dir for Mac Excel) Make it easy to create a list of files from a folder add-in TIP. Merge Excel workbooks on a Mac.
- Visual Basic Editor is a code editor for VBA. It’s a separate application but you can only use it with Excel. You need to have the developer tab on the ribbon to access it.; You can also use the keyboard shortcut (Alt + F11).; It also stores the macros that you recode with the macro recorder.
- Written by Puneet for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel for Mac. Visual Basic Editor is the only way to write a VBA code in Excel.
- With the three functions above it is easy to test what you want in your VBA projects. Note: Read this page about the version numbers: Mac Excel version and Mac Office language settings. New MACOFFICEVERSION conditional compiler in Office 2016 In Mac Office 2016 they add a new conditional compiler constant named MACOFFICEVERSION.
iliace
Well-known Member
- Joined
- Jan 15, 2008
- Messages
- 3,543
- 365
- 2016
- 2010
- Windows
I have some budget tools that we run in our organization using Excel. Some of these are heavily macro-driven, and to further complicate things, some of those use Windows API to achieve some of the functionality. I recently discovered that a large number of users in our company are using some kind of Mac-based system, with the corresponding version of Office for Mac installed. This was news to me. This created huge compatibility headaches for our users, as they were not able to use their own workstation to run our tools, and had to line up at a limited number of Windows workstations to complete the Budget process.
I have not yet been able to get a Mac-based workstation to test some of our tools, so I don't know all of the specifics, but want to see if anyone has any insight into cross-platform development and porting Windows-reliant spreadsheets to Mac versions of Excel.
Problem #1: some of our tools rely on Windows API. We are using libraries such as kernel32.dll, user32.dll, shfolder.dll, and advapi32.dll. My assumption is that none of this works on Mac operating systems. Is there some equivalent of achieving these functions (GetUserName, GetComputerNameEx, GetSystemMetrics, SHGetFolderPath, etc) to recover some of this functionality? We can definitely nix some of it, but certain ones would be hard to live without, given our current setup, without reinventing the wheel.
Problem #2: this one is less of a concern, but are there any known VBA compatibility issues, using strictly built-in VBA and Excel object model, when porting Windows version to Mac? Particularly curious whether VBA Forms would still work.
Problem #3: even less of a concern, but are there any known compatibility issues with native Excel functions, when porting Windows to Mac? I am interested in a broad range of things, from worksheet functions to Worksheet properties (e.g. protection), to file formats, and so on.
If anyone has any insight, or if there are online/book resources available that address these issues, I would love to know!
Thank you for looking, and thanks in advance for any help or advice.
I’ve been trying to maintain compatibility between Mac and Windows versions of Excel/VBA apps for a while now, and it’s a pain.
These notes refer to versions 16.14 of both Excel and Powerpoint.
Here are some of my recent threads on answers.microsoft.com
Passing a user-defined object to a sub As Object passes an object with all Null properties. Passing As MyClass works. Methods work, just not properties.
Interoperability between Excel 2016 for Mac and Powerpoint 2016 for Mac is very frustrating.
To get VBA to copy a picture of a chart to a slide, I’ve tried four different ways.
Only in the latest Insider Build 16.14 does the obvious Copy / Paste method work but even that requires copying and pasting as an image in Excel first and then copying amd pasting the static picture.
1) Manual copy and paste works, as it does in Windows. I want to automate this for what could be 100 slides.
2) The CopyPicture method fails for all objects. Range gives error 1004 CopyPicture method of Range class failed. ChartArea gives error 438 Object doesn’t support this property or method. Chart.Copypicture the result when pasting is a shape with text “The picture can’t be displayed”. (Update 8-May-18: Excel 16.14 (180507) Chart.Copypicture now works, includes a TIFF format in the copied data. And to save a picture of a range as a file, we can now do range.copypicture followed by adding a temporary chart by chartobjects.add and then chartobject.chart.paste of the picture, then chart.export as PNG)
See my post on answers.microsoft.com.
To work around this, create the picture in Excel:
3) There is no Slide.PasteSpecial method in Mac Powerpoint VBA. Slide.Paste takes NO data type argument.
4) ApplescriptTask can be used to execute the Paste in Powerpoint. Thanks to Ron de Bruin for help here.
This code is in the VBA:
Save this code in a file eg “CopyPasteScript.applescript” in the special folder
/Users/myusername/Library/Application Scripts/com.microsoft.Excel
5) Another method is to export the Chart as a picture and then import it in PowerPoint
6) In practice, the above method is agonisingly slow, so what I really do is write a VBA script with lines that create a presentation, add slides, set the title text, and call AddPicture using the file names created.
Then I create a temporary presentation in PowerPoint, import that VBA code, execute it, and close the temporary presentation without saving it.
7) Before Chart.Export started to work in Excel 16.14 or later, I did try the ugliest workaround, and it works but it’s really a kludge.
Excel Vba For Beginners
7.1 Create a temporary workbook with a sheet for each slide and copy/paste the title and chart image into it. Add needed properties to the .AlternativeText of the pictures.
Vba For Excel For Macbook Pro
7.2 Save the workbook as html.
7.3 Open the tabstrip.htm file, read the text, split it at “<a href=” and extract the tab names.
7.4 for each tab file name, open its htm file, read the text, split it at “<v:shape id=” and parse out the picture file names and properties.
Vba For Excel For Mac Spreadsheet
7.5 Now I have the picture file name, add a line to the PowerPoint VBA file I create in (6) above to add a slide using the right title and picture filename.
Horrible, isn’t it?