VBA-created form won't run on Mac

[chucklod]

I'm having a problem with running a form created using VBE (VBProject.VBComponents) in Excel 2011 on Mac OS X 10.13.6 (High Sierra). I have enabled a reference to 'Microsoft Visual Basic for Applications Extensibility 5.3', and trusted VBA project, so those aren't the issue.

I refer you to John Walkenbach's sample code at https://www.thespreadsheetguru.com/t...lete-userforms. On my Mac, the unchanged code runs fine until it reaches:

    'Show This Form
        VBA.UserForms.Add(FormName).Show

There it halts with "Run-time error '5': Invalid procedure call or argument"

I have googled for other code to do similar things (using VBA to create a form and run it) and they all use the same command shown above. I've tried it under Catalina and Excel 2019 with the same results. I have tried it under Windows 7 and Excel 2007 and it works just fine.

So, is there some further adjustment(s) I need to set on my Mac to run this? Or is this an error on the part of Microsoft development, that is, leaving a property in the dictionary when porting over to the Mac without implementing the code to support it?

To me it looks like only early binding works on the Mac - that is, define the form in the editor and you can run it by name, but create it in VBA code and you can't.

Do you know of a solution that allows me to run JWalk's code on the Mac?

[romperstomper]

To me it looks like only early binding works on the Mac - that is, define the form in the editor and you can run it by name

There is no userform designer in current Mac versions of Office.

[chucklod]

Yup, that's definitely true. I've got Office 16.17 there's no forms editor.

I'm developing in 2011 and wondering why they took that away in later versions.

[romperstomper]

They rebuilt the VBE for Mac, but never got around to finishing it, hence its current sorry state!

[chucklod]

I'm not surprised. I have a laundry list of complaints about 2011, and I avoid 2016/2019 since they're even worse.

[chucklod]

Okay, I have a solution. This is a fairly long description of the problem and solution to provide enough detail for someone else to implement it, should they wish.

It's based on something I saw VAX VMS programmers do years ago. Lotsa years ago. There were things DCL (the console scripting language) could do and things it couldn't do. If I remember right, it had something to do with variable substitutions in scripts. To overcome limitations, they would write a script that would write another script that actually did the job. They called it "two deep" and some of us called it "too deep." Anyway, I think one of them routinely wrote three deep.

Anyway, scripts creating scripts was routine. So I devised a Mac VBA solution that mirrors that.

Here's the problem: as I mentioned in my first post, it's an issue of early vs late binding. If the userform is defined when you write the code, you can use its properties normally, like

myUserForm.Show
      or
Hide myUserForm

In Windows' Excel you can define it after you write the code and using the proper command, run it by name, as in the following:

VBA.UserForms.Add(FormName).Show

On Mac, that functionality does not work. Even if the userform will be created by the time the code is run, "myUserForm.Show" will not compile when you enter it if it doesn't already exist.

So, the solution is to write a script that refers to AFTER it's created. That describes what I devised. I used the John Walkenbach (https://www.thespreadsheetguru.com/t...lete-userforms) routine as a starting point and added four others by Chip Pearson (http://www.cpearson.com/Excel/VBE.aspx) which add and remove both code modules and subroutines.

Did I need to do this? Perhaps not, but I have had clients ask questions about it (creating forms on the fly based on needs) and I wanted to prove it could be done. It can, but is it worth it? I'm not sure yet. There are several problems.

1) There is a known bug in Excel VBA that complicates creating a userform programmatically: that of giving it your desired name. It seems to work fine the first time, but after that it is erratic. What happens is that your form is created with the name UserformX where X is a sequence number assigned at create time, and when you try to rename it to your preferred name, it throws an error. According to what I've read, the only sure solution is to shut down Excel and start it back up again, and then your rename will be effective. But subsequent attempts may have the same difficulty. My code tells the create routine what name I want to use, but to tell the calling routine what name it actually used. That means the calling routine knows what the form name is but subsequent programs won't. Yuk.

2) If the VB editor is active and has windows open, there can be flickering as the userform window and code windows are built/activated. The solution is to hide the VBE while this process runs, using segments in the original Walkenbach routine (which are omitted from this code for brevity's sake).

3) After the process is over, if the VBE is active, those windows will be visible again. If you don't want those cluttering the workspace, you need to make them not visible, another VBE call.

4) In light of 2) and 3), it really speeds up the operation if you don't have the VB editor active while creating and running a form in this fashion.

5) For a simple form such as this one, the supporting code for the controls is simple. For more complex forms with numerous controls, the code gets complicated and lengthy. You can compensate by putting the routines that do the actual work in the main code module(s) and call them from the controls' events.

6) If this process is so complicated, why bother? Just create the userform in Windows (since Excel for Mac after v2011 does not have a userform editor) and migrate it to your Mac.

But if, after all that, you still want/need to do it, the code in the attached workbook shows how. To demonstrate it, open the workbook and click the button on the worksheet that says (... wait for it ...), "Click me". It pops up a windows that resembles the original InputBox. Type something or cancel and a message box will report what you did.

One of the reasons I got started down this path is that pixels on Mac are smaller than those on Windows, making the type rather hard to read. You will get eye- or neck-strain if you don't do something about that. I implemented a solution devised by Ron de Bruin (https://www.rondebruin.nl/mac/mac022.htm) that enlarges the form, controls and fonts on the fly. So I wrote alternative userforms for InputBox and MsgBox that call the enlarge routine on initialization. I also can control the background color and the font style, button placement and indeed add more buttons if I want. My clients love it but portability can be an issue. So, this solution might help me out when it comes up again.

You are free to use the code, since I can't claim ownership, and the folks I've named above seem happy to share.

Good luck, and if you'd like more information, contact me via this forum.

Chuck L