I am converting my Excel 2000 VBA macros to run on the Mac using Office 2016 for the Mac and I want to end up with a macro that will run whatever platform it is on. With the help of Ron de Bruin’s excellent functions I am able to test on which platform the macro is running, have surmounted the problem of file separators and tested whether files exist before opening them but my macro falls down with ChDir. When a new workbook is made it does not necessarily save the file in the same directory as it’s host. I do not want to hard code the directory path but be able to move my workbook with associated macro to any folder and let it make and save files there.
I have Excel 2016 for Mac. But somehow, it is missing math functions such as round, roundup, ceiling, etc. The Excel is default installation. Any idea why, and how can I get those functions, please?
![Free Free](/uploads/1/2/5/3/125399673/302266478.png)
The macro below sometimes works but often saves the file in an entirely different place using the path from a recently saved file. I cannot determine the logic but that is probably not important. I need a Change Directory function that works.
Perhaps this is a bug in 2016 Office for which I have to await a solution. At the end of my test macro I have a solution but it is inelegant when all I want to do is load and save files from the host directory. (The message boxes are there due to the lack of debugging tools in 2016 Office for Mac) Sub Test Dim Path As String Dim F1File As String Path = ThisWorkbook.Path ChDir Path Workbooks.Add Range('A1').Select ActiveCell.FormulaR1C1 = 'To Test Macro' 'Just to add some data to the blank workbook MsgBox 'Path= ' + Path ActiveWorkbook.SaveAs Filename:='Test2.xls' 'The following of course saves in the correct place F1File = Path + '/' + 'Test2.xls' MsgBox F1File ActiveWorkbook.SaveAs Filename:=F1File End Sub regards. Marked your reply as helpful as although the article does not refer to the ChDir function I note Ron has added a note to the end that his function does not work in Office 2016. This reinfoces my opinion that Change Directory has no effect on the default directory in 2016 and we have to hope that Microsoft will fix it. I would register this with Microsoft if I knew how. In the meantime I have deleted ChDir from my macros and make no attempt to alter the default directory.
I have added the following (substituting whatever the filename may be for Test2.xls): #If Mac Then FileSep = '/' #Else FileSep = ' ' #End If Path = ThisWorkbook.Path ActiveWorkbook.SaveAs Filename:= Path + FileSep + 'Test2.xls'.