+ Reply to Thread
Results 1 to 9 of 9

64bit Excel corrupts xlms with userform when saving it second time

  1. #1
    Registered User
    Join Date
    04-09-2021
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    5

    64bit Excel corrupts xlms with userform when saving it second time

    Hi,

    I'm in big trouble with a VBA project.

    Situation: xlsm file, incuding two userforms. UF1 includes a multipage (8 pages) with about 80 option buttons, 90 labels, 60 text boxes, 20 check boxes and 10 command buttons, using two worksheets (sheet #1 86 columns, sheet #2 just a few constant configuration data). UF2 has about 150 labels and one command button
    Works absolutely flawlessly with 32bit Excel versions (2016, 2019). No activeX controls.

    With 64bit Excel (2016 as well as 2019)
    - I can open and run everything perfectly
    - after having it saved, I can open it again and use it
    - after having it saved 2nd time, I can open it, but Excel quits immediately when in UserForm_Initialize() any setting of a control's property is being performed, not depending on wether the setting is performed within the initializing routine or via an extra sub. (starting the userform manually shows an error window, claiming the well known error 400) When - with this corrupted file and still within 64bit Excel - I reopen it skipping makro execution, edit anything at any line (for instance adding and instantly removing a colon), save and quit, I again can open and use the file two times.

    Opening and using even this "64bit maltreated" file with 32 bit Excel never is a problem. But, unfortunately, the final user has 64bit Excel only. It's quite urgent, they need it for a (medical) scientific project -- and in the current situation, they even can't check the "alpha" and "beta" versions, so further development is pretty impossible.

    It's a non profit project, I don't get a cent for it, just a smile.

    Any help at the horizon? Would be great.

  2. #2
    Registered User
    Join Date
    10-21-2019
    Location
    lugo
    MS-Off Ver
    2016
    Posts
    20

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Should provide sample...

  3. #3
    Registered User
    Join Date
    04-09-2021
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    5

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Hi,
    some more info here:

    Code in Workbook:

    Please Login or Register  to view this content.
    The last of the red colored commands let it crash. Removing this line simply moves the crash to the line above. But, again: Adding a dot or any character at any position, removing it immediately, saving the file -- everything runs smoothly. Saving the file again (no difference between saving by VBA-command or manually by Excel menu) - and it will crash when reaching the red lines.
    The VBA Editor doesn't claim any problem, running within the editor causes no crash.

    I am absolutely helpless.
    Last edited by njotha; 04-11-2021 at 03:41 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,174

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. As you are new, I'll do it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    10-21-2019
    Location
    lugo
    MS-Off Ver
    2016
    Posts
    20

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Just to start:
    Please Login or Register  to view this content.
    should be Me.Text_eingeloggt.Value = ...
    Following, where is LoginName declared?. It's a variable or a Control. If you have a control with that name, it gonna crash.

    You should check that "Option Explicit" is active on the top of the UserForm code, and then perform a "Compile Workbook" before running the code. That way you could detect declaration errors.

    If you have the Option Explicit, and there are no errors, try exporting the UserForm, get it removed from the workbook, save, close and then reimport the Userform.

  6. #6
    Registered User
    Join Date
    04-09-2021
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    5

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Thank you for your fast reply.

    Me.Text_eingeloggt is a label, simply showing the name of the person who logged in in the userform. So ".Value" is not being offered. Even ".Caption" doesn't change anything, and removing it simply brings the crash to the previous line (and so on).
    "Option explicit" is active, "Compile" doesn't recognize any error.
    Exporting/re-importing the *.frm doesn't help.
    And it's definitely a problem of 64bit Excel, reproducable on different machines, 2016/2019 doesn't matter. There are neither additional controls nor any formulas in the worksheet. And, furthermore, there is no declaration, no API-call in the code. which had to be changed for 64bit.

    For me, it very much looks like a bug in Excel 64bit -- any workaround would be welcome. It's a pity that Berlin Charité uses 64bit Office only, but I can't change it.

    Many thanks again for your rapid reaction, your patience -- and for coping with my horrible English. I've learned it at school only, some 60 years ago, three lessons/week and during three school years only.

  7. #7
    Registered User
    Join Date
    10-21-2019
    Location
    lugo
    MS-Off Ver
    2016
    Posts
    20

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Can you rip the code not related to this thread, and any worksheet with personal data and upload just the basic .xlsm file with the userform that is crashing? Error should be elsewhere, as code shown is quite simple to just crash with these procedures. By the way, is there any reference loaded, or any add-in in the testing machines?

  8. #8
    Registered User
    Join Date
    04-09-2021
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    5

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    Hi,

    The code totals to about 3.500 lines, but nothing highly sophisticated, 99% as simple as what you've seen here... And no, no API calls, no declarations incompatible with 64bit, no AddIns except those standard AddIns which Excel adds when being installed, i.e. same standard MS AddIns for the 64bit and the 32bit versions.

    The Problem is not to be found within my code, I'm sure, it is some problem with the file saving procedure of Excel 64bit.

    The difference when using it with the 64bit version in fact is: Save it after having it opened in Excel's macro editor and performing a "null" change (add and remove a space, for instance), close the file -- and it runs perfectly when you open it again.
    Save it with Excel's standard worksheet's menu function file - save or within the VBA code with ThisWorksheet.Save, ThisWorksheet.SaveAs, or .SaveCopyAs produces a corrupted file which makes Excel 64bit crash silently and without any message.
    I even tried adding a 5 seconds wait command after the save command. Doesn't help, Excel leaves an in some way corrupt file. But in fact it is not corrupt -- open it skipping the macros, open the VBA editor, add/remove what you like to, save within the VBA editor -- and you can use it again.

    But, if you like to try it your own, just pass me a pm address, and I will send you this (pre-alpha) file, it's about 220KB. There is no secret content in it.

    I think my workaround will be lending them a notebook with Excel 32 bit, it seems to be the one and only possibility to let the users perform the needed usability and "find all 386 mistakes"-tests

    Thanks again for your as kind as fast reaction. I'm on my way to become an Excelatic, which is quite worse than being a lunatic...

  9. #9
    Registered User
    Join Date
    04-09-2021
    Location
    Berlin, Germany
    MS-Off Ver
    2016
    Posts
    5

    Re: 64bit Excel corrupts xlms with userform when saving it second time

    I let my file be checked by some analyze tools - no issues were found. And, sorry to say, none of your really valuable hints helped.

    Solution of the problem (an internal Excel problem, indeed) was:
    1) in Excel's VBA Editor, I pushed whole multipage content to the clipboard, cleared the multipage, copied all contents back to it
    2) exported all modules and userforms
    3) saved the file as .xlsx without any makro
    4) deleted the original file
    5) imported everything back into the xlsx, saved it as xlsm

    ... and now it runs hazzle free with excel 64bit, and I can go on developing (and amending, as I am a pretty lousy & chaotic programmer) it further.

    Many thanks to all of you, in special audeser!

+ 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. Paste Picture to userform image control using Excel VBA 64bit win10
    By soar73 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2021, 12:55 PM
  2. [SOLVED] How to resize the Userform based on screen resolution in Office 64bit and Windows 64bit
    By king05 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2018, 04:30 PM
  3. How to read custom props on closed file (64bit Win Server, 64bit Excel)
    By soundneedle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-14-2015, 01:25 AM
  4. One Excel 2010 user corrupts docs after saving
    By Inigo Montoya in forum Excel General
    Replies: 7
    Last Post: 01-10-2014, 08:30 PM
  5. Saving file as CSV corrupts when using code
    By OWA in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2013, 12:53 PM
  6. Replies: 3
    Last Post: 11-23-2012, 01:57 AM
  7. Replies: 2
    Last Post: 10-19-2012, 10:16 AM

Tags for this Thread

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