Jim McGimpsey is a MS MVP and a Mac specialist. I just found his web page today. OK, I'll make it 4 of us using VBA on Mac! Looks like we need some activity around here, so I have a few to add to the list when I remember them. Right now I'm converting Excel VBA code from Office 2000 to Office X on my new machine and hitting plenty of problems.
Learn 200+ Excel shortcuts (Mac and PC) with this easily searchable list. Excel Shortcuts List for Mac and PC (Searchable) This App is not compatible with Smartphones and Tablets.
(Yeah, I'm too cheap to get Office 2004 yet). The LoadPicture method above hits me hard because I use it to update images in UserForms. Haven't found any useful workaround yet although I've read that it's been fixed in Office 2004 SP1. Right now I have to use conditional complilation to avoid compiler errors: VBA#If Not Mac Then UserForm.Picture = LoadPicture(picturename) #End If/VBA Next up. The InStrRev function is not recognized and gives a compiler error.
For this one I just created a new function to handle it under both Mac and Win: vbaFunction RevSearch(ByVal string1 As String, ByVal string2 As String) As Integer RevSearch = 0 Do While InStr(string1, string2) RevSearch = RevSearch + InStr(string1, string2) string1 = Right(string1, Len(string1) - InStr(string1, string2)) Loop End Function /vba There are probably more elegant solutions, but I have a lot of ground to cover. Another one: Application.GetOpenFileName always seems to fail for selecting multiple files. Right now I'm using a very cludgy workaround that goes like this: i) identify a directory using Application.GetOpenFileName in single file mode. Ii) use this directory in a Application.FileFind search. This has to be done with conditional compilation as FileFind will cause a compilation error on Windows. Iii) list the FoundFiles in a ListBox and allow the user to select files from this list. (BTW, I'm using this because I already have some code in there to allow the user to match filenames with comments stored in the spreadsheet before opening the files, so this was just reusing that code.) Pasting arrays into worksheets.
This one's a little random. vbaDim MyData as Variant, n as Integer ReDim MyData(1 to lengthoffile, 1 to 1). Do While blah-blah Input #1, MyData(n, 1) n = n + 1 Loop Sheets('My Sheet').Cells(1, 1).Resize(UBound(MyData, 1), 1) = MyData/vba.often fails with a Type Mismatch where file #1 just contains numeric data. It seems that Mac VBA is not as smart as WIN VBA in determining types for Variant.
The fix here was to change the Input to: vbaInput #1, TempData MyData(n, 1) = Val(TempData)/vba This forces VBA to recognize the Variant array as numeric. Another limitation is on dates calculus. Excel on Windows has a range between 1/1/1900 and while at Mac its from 1/1/1904 through. Not that this may impact too much but its always usefull to know. The date difference is not a vba issue, rather it is a difference in the way apple and msft handle dates. Likewise, there are differences in the coded representation of certain characters, e.g., as i recall, breaking space.
Also, the separator used in paths. One can conditionalize vba code to asccount for some differences, such as those i listed supra. For some others, i guess one is outta luck. The problem with Application.GetOpenFileName for multiple file selections can be worked around. The key piece of code is: VBA FileList = MacScript('return choose file with multiple selections allowed') /VBA which is a call to AppleScript. Maybe I'll write a quick KB article once I have the details worked out. Interestingly, if you open the Excel Dictionary in Script Editor, GetOpenFileName can be accessed from AppleScript.
The dictionary shows no way of selecting multiple files. BTW, there are three reasons for going after this workaround. I) The workaround above is too cludgy.
Ii) Application.FileFind takes too long to do its job. Iii) Application.FileFind appears to fail in the trial copy of Excel 2004 that I have.
If two controls set each other's values, you can get into problems. For example: vbaPrivate Sub Control1Click Control2.Value = x. End sub Private sub Control2Click Control1.Value = y. End sub/vba The problem is that, in setting a control's value, you automatically call that control's Click code. Windows VBA is pretty smart about this, but Mac VBA seems to get stuck in loops, usually resulting in Excel unexpectedly quitting. So be very careful how you set up this kind of code. Call SubInModuleX.
Unlike Win VBA, Mac VBA does not always seem to like calling a public Sub in another module unless you use vbaCall ModuleX.SubInModuleX/vba Seems very random. Several possible error codes including 'Sub Not Defined' and 'Illegal Use of Property'. Hey, but the good news is that I now have probably about 70% of my Win VBA code running in Office X now. So I'm getting closer. Line Input and Input into arrays Boy, does Excel X hate this one (which works fine on Excel 2000): vbaDim TempData as variant, n as integer ReDim Tempdata(1 to whatever) open newfile for input as #1 Line input #1, TempData(1) for n = 2 to whatever input #1, TempData(n) next n/vba The Line Input and Input statements give a 'Compile Error: Variable required - can't assign to this expression' or a 'Can't assign to array' error, or just cause Excel to quit.
![Mac switch user Mac switch user](/uploads/1/2/5/5/125503715/774872019.png)
The solution? Input or Line Input into a non-array variable, and then assign the array element to the first variable.
Frequently Asked Questions Question: In Microsoft Excel, my question concerns formatting numbers in a particular cell. For example, the cell says: ='Gas price: $' & CHOOSE(gas.deck, B1, B2, B3) & 'per mmbtu' But returns $3 when the cell is formatted to two decimals. If the price were $3.25, the correct price would show. Any help would be greatly appreciated. Answer: Even though your cell is formatted with a number format, your formula is returning text not a numeric value so the number format will not be applied. You will need to apply the format to the number inside of the formula.
You could try using the DOLLAR function to apply the format as follows: (you will need to remove your $ sign because the DOLLAR function will insert one automatically) ='Gas price: ' & DOLLAR(CHOOSE(gas.deck, B1, B2, B3)) & 'per mmbtu' This formula should now return something like: Gas price: $3.25per mmbtu.