I'm using Office365 on several machines that all run Windows 10 Pro. I login to those machines using a Microsoft Account and I have all the syncing options that I can find set to sync. My OneNote stuff syncs as do my spreadsheets. Changes on one machine show up on another machine even in the same spreadsheet running concurrently on multiple machines. The multiple machine mode is not my habit; but, it does work except for references to the Personal.xlsb workbook stored in %APPDATA%.
My approach has been to write nearly all my general purpose VBA in the Personal.xlsb stored in XLSTART or in Normal.docm stored in STARTUP at the default locations in %APPDATA%. My current problem with this approach is that references in a generic spreadsheet to my Personal.xlsb do not stick when the generic spreadsheet is stored in OneDrive. I'm not opening the generic spreadsheet in a Web app. I am simply using Excel and opening the generic spreadsheet from the OneDrive folder.
I'll write a function in the Personal.xlsb and get all the formulas in a generic workbook to work via a reference in the generic workbook stored on OneDrive. Later, when I reopen the generic workbook the reference to the Personal.xlsb has been removed and the function calls result in a #NAME? error for all functions defined in the Personal.xlsb file. When I open the VBA Editor, the Personal.xlsb has been opened from the XLSTART folder in %APPDATA% as expected; however, Tools/References shows that the box next to the relevant project name in Personal.xlsb has become unchecked. If I recheck the reference to the project name in Personal.xlsb, everything works as intended until I save and reopen the file. This occurs on the originating computer and the others (not surprising because all machines are probably configured identically).
I'd be interested in knowing if there is something I can do to get this to work effectively. Alternatively, I'd appreciate knowing if there is a better way to maintain and develop code that can be applied among several machines that use the same Microsoft Account. What I'd like to do is implement a generally recognized Best Practice for developing VBA code and deploying it for use in spreadsheets stored in OneDrive and used on multiple machines. At this stage, I'm a single user; but, may end up deploying the VBA for multiple users in a single Private Network. Clearly, the multiple user issue is a problem for another day.
My approach has been to write nearly all my general purpose VBA in the Personal.xlsb stored in XLSTART or in Normal.docm stored in STARTUP at the default locations in %APPDATA%. My current problem with this approach is that references in a generic spreadsheet to my Personal.xlsb do not stick when the generic spreadsheet is stored in OneDrive. I'm not opening the generic spreadsheet in a Web app. I am simply using Excel and opening the generic spreadsheet from the OneDrive folder.
I'll write a function in the Personal.xlsb and get all the formulas in a generic workbook to work via a reference in the generic workbook stored on OneDrive. Later, when I reopen the generic workbook the reference to the Personal.xlsb has been removed and the function calls result in a #NAME? error for all functions defined in the Personal.xlsb file. When I open the VBA Editor, the Personal.xlsb has been opened from the XLSTART folder in %APPDATA% as expected; however, Tools/References shows that the box next to the relevant project name in Personal.xlsb has become unchecked. If I recheck the reference to the project name in Personal.xlsb, everything works as intended until I save and reopen the file. This occurs on the originating computer and the others (not surprising because all machines are probably configured identically).
I'd be interested in knowing if there is something I can do to get this to work effectively. Alternatively, I'd appreciate knowing if there is a better way to maintain and develop code that can be applied among several machines that use the same Microsoft Account. What I'd like to do is implement a generally recognized Best Practice for developing VBA code and deploying it for use in spreadsheets stored in OneDrive and used on multiple machines. At this stage, I'm a single user; but, may end up deploying the VBA for multiple users in a single Private Network. Clearly, the multiple user issue is a problem for another day.
Last edited:
