Is it possible to have users update the 32-bit VBA code to 64-bit? RRS feed

  • Question

  • Hello,

    I've have a bunch of macro-enabled workbooks that were written using a 32-bit version of Excel. When the company moved to Office 365 and a 64-bit version of Excel, the code no longer worked - I have solved that problem. However, the solution is to go into each workbook and update the VBA to the 64-bit version. Since I can't have each user do that, I'm not sure how to best handle it. Additionally, there are hundreds of users with multiple version of these workbooks so me doing it manually is not a possibility either.  

    While the users can use the new version of the workbook, they still need to extract the data from the worksheets in the old version. If I disable the macros, when they open the old version they will not be able to see the sheets, because it used the xlSheetVeryHidden command. Also, by disabling the macros all the user-defined functions no longer work. So if they wanted to just do a copy-paste values, they would not get any values because the functions are not available. 

    My goal/hope is to be able to somehow easily update VBA without user intervention (other than clicking a button) and/or be able to make a copy of the worksheet they need the data from that remains in the old version of the workbook.

    Thank you in advance for any help and suggestions!



    Monday, August 24, 2020 3:16 PM

All replies

  • If it's purely about updating the code three approaches you could consider, all of which could be fully automated with minimal user involvement including batch process all workbooks in a given user folder + subfolders:

    1. Copy all sheets in the current workbook into a template workbook that contains the updated code. If sheets include code behind object-modules that's not good for x64 would need to copy the sheet contents (rather than sheet itself) into sheets in the template workbook that contain the code. Save the current wb as .bak and the template with original name.

    2. Remove all code in the current workbook and replace with the new code. Remove existing normal & class modules and forms and import the replacements. Would need to update code in ThisWorkbook and sheet modules if/as required by writing to the code modules directly. This approach would require user macro security settings to 'Trust access to the VBA project' 

    3. Remove all code from the current workbook (save-as xlsx) and install an addin to do same. If all users are on the same network all could use the same single copy of the addin loaded over the network each time at startup.

    Each approach only briefly outlined, each has pros & cons or may not be viable in your overall scenario.
    Friday, August 28, 2020 8:48 AM
  • I never mix code and data.  I always put all my code in addin (.xlam).  The addin has a menu and I can select workbook with data (.xlsx).  Create addin to perform all functions on data.  Save workbook with data and code (.xlsm) as .xlsx workbook to remove all code.  I use Custom UI Editor to create menu. 
    Friday, August 28, 2020 4:38 PM