+ Reply to Thread
Results 1 to 6 of 6

VBA-created form won't run on Mac

  1. #1
    Registered User
    Join Date
    01-07-2008
    Posts
    38

    VBA-created form won't run on Mac

    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:

    Please Login or Register  to view this content.
    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?
    Attached Files Attached Files
    Last edited by chucklod; 06-18-2020 at 05:02 PM. Reason: Adding the attachment

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA-created form won't run on Mac

    Quote Originally Posted by chucklod View Post
    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.
    Rory

  3. #3
    Registered User
    Join Date
    01-07-2008
    Posts
    38

    Re: VBA-created form won't run on Mac

    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.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA-created form won't run on Mac

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

  5. #5
    Registered User
    Join Date
    01-07-2008
    Posts
    38

    Re: VBA-created form won't run on Mac

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

  6. #6
    Registered User
    Join Date
    01-07-2008
    Posts
    38

    Re: VBA-created form won't run on Mac

    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

    Please Login or Register  to view this content.
    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:

    Please Login or Register  to view this content.
    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
    Last edited by chucklod; 06-18-2020 at 04:58 PM. Reason: correct splelings

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Acessing dynamically created form controls
    By jwolfenstein in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-12-2017, 10:55 PM
  2. Programmatically created form with name conflict
    By Jacafio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2015, 12:53 PM
  3. [SOLVED] Re-Created Form
    By ruthdyson in forum Access Tables & Databases
    Replies: 10
    Last Post: 07-11-2013, 09:40 AM
  4. Help Modifying a form created with VBA (4 new fields)
    By simjambra in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-13-2009, 12:24 PM
  5. Created a form
    By lpierce in forum Excel General
    Replies: 3
    Last Post: 12-03-2008, 11:25 AM
  6. Replies: 1
    Last Post: 09-01-2005, 04:05 PM
  7. [SOLVED] How to set Tab-to-next-field in a custom form (created with VBA)
    By jenelle in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1