Seeking Effective Approach to Using Personal.xlsb on Multiple Machines

[BCVolkert]

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.

 

[Legacy 456129]

A possible answer at superuser

Backup Excel personal.xlsb to OneDrive

I am using OneDrive for Business and I want to backup my Excel personal macro file; personal.xlsb. I have tried the following methods without success. Add folder to OneDrive - I cannot seem to lo...

superuser.com

I've never tried personal.xlsm but for local use. If it is not a file frequently altered, I'd make it local copies.

 

[BCVolkert]

Thanks. I saw that too and I may try it.

I enjoy writing procedures that I think or hope will be generally useful. I use a lot of these functions as I produce workbooks and Word documents for a wide variety of personal uses. Invariably, I find a need to add more functionality to this collection. This same collection of procedures is used in Word. Since change is constant for me, my Personal.xlsb and its .docm are frequently altered. Having a central place from which to access to access this code as a sort of procedure library is helpful.

Before I started using OneDrive it worked pretty well to use XLSTART for Excel and STARTUP for Word within %APPDATA% as this central place. As the code evolved, I could store the functions in my Personal.xlsb and Personal.docm (along side Normal.docm) or in something like MyBigProject.xlsb also stored in XLSTART. When I did something that someone else in my little world would need, I simply copied the procedure libraries to the %APPDATA% locations for those users when the code was mature enough. All was well because the libraries and any dependent workbooks could be similarly copied to their machines using a little .BAT script referencing %APPDATA%. Any preexisting dependent documents remained functional to the extent I did not introduce incompatibilities.

Within the family, we can share OneDrive folders holding dependent files and copies of the procedure libraries could also be there as a repository for updates from which all the %APPDATA% locations could be maintained. For stuff used beyond the family, copying the files to XLSTART and STARTUP was very effective. This was very simple to implement because it only required copying the procedure libraries from a LAN location to the %APPDATA% locations on the other users's machines using a .BAT file distributed by e-mail or triggered in an AutoExec/AutoOpen sequence. Excel and Word open the files in %APPDATA% by default and everything just worked.

I was hopeful that using OneDrive would take this to the next level because spreadsheets and other dependent files stored on OneDrive would be available to each user on each machine from which they accessed their OneDrive folders (home and work machines, laptops on the road, etc.). But storing the workbooks on OneDrive seems to have the introduced the complication of the references being broken as has been described by many users for several years.

Perhaps storing the functions in a workbook on OneDrive will work instead of within %APPDATA%. In other words replace Personal.xlsb in XLSTART with a "Group.xlsb" stored on OneDrive and have some sort of installation procedure for it. I suspect that OneDrive will still break the references. Another possibility is putting the functions into an add-in that somehow includes an update feature. I'm starting to wonder if OneDrive or Office introduces the problem when a workbook is saved to OneDrive. Perhaps, saving the file to a local location and then copying that file to OneDrive will bypass the process during which OneDrive, Excel, or Word breaks the references as the file is being saved to OneDrive.

I do appreciate your help and hope that this does not seem argumentative; but, I'm still looking for a solution or a Best Practice to avoid the issue and efficiently maintain my ever-evolving code and previously completed dependent work.

 

[Jon Peltier]

I understand (haven't tried it) that if the file with the UDF is in this folder, the links will work:

C:\Program Files (x86)\Microsoft Office\root\Office16\XLSTART

But that's the 32-bit folder, and it seems if you have 64-bit on another computer, the path should be

C:\Program Files\Microsoft Office\root\Office16\XLSTART

So the sync wouldn't work anyway

 

[BCVolkert]

Jon,

Thanks for looking into my inquiry. I just tried your suggestion with no real improvement and a loss of Excel functionality. Here is the step-by-step of my trial.

My world:

• I'm using Office365 64-bit / Windows 10 -- all updates current to the best of my knowledge
• Prior to your suggestion, my XLStart was located at the default location: %APPDATA%\Microsoft\Excel\XLSTART
• My functions are defined in %APPDATA%\Microsoft\Excel\XLSTART\Personal.xlsb in which I changed the VBA Project Name from VBAProject to BCVPersonal_xlsb
• Upon opening, references to UDFs in Workbook produce #NAME? errors
• After rechecking the reference to BCVPersonal_xlsb, the UDFs respond accurately
• Saving and reopening the workbook unchecks the reference to BCVPersonal_xlsb

Changes and Results:

• Per your suggestion, I moved XLStart including Personal.xlsb to C:\Program Files\Microsoft Office\root\Office16\
• Restarted Excel
• Personal.xlsb was open (View/Unhide shows Personal.xlsb as expected)
• Open Existing Workbook / References to UDFs produce #NAME? errors (as before implementing this suggestion)
• Existing reference to BCVPersonal_xlsb in Existing Workbook was unchecked (it had been checked prior to preceding save)
• Rechecked reference to BCVPersonal_xlsb in Existing Workbook / F9
• Formulae referencing UDFs in Existing Workbook are functional
• Save Existing Workbook (no error messages)
• Close Visual Basic
• Note -- C:\Users\Bruce Volkert\AppData\Roaming\Microsoft\Excel\XLSTART is still empty (as expected)
• Close Excel - offers to save changes to Personal Macro Workbook (as expected; but, no changes to BCVPersonal_xlsb were made)
  • Save: Fails -- Microsoft Excel cannot access the file 'C:\Program Files\Microsoft Office\root\Office16\xlstart\6C011000'
    • The file name or path does not exist (the path exists, the file name does not and I am viewing hidden files etc.)
    • The file is being used by another program (only Excel any by Browser are running)
    • The workbook you are trying to save has the same name as a currently open workbook (only one workbook and two hidden in the XLStart folder are open)
    • OK yields "You cannot save the Personal Macro Workbook in the startup folder. Save it elsewhere now and mot it to the startup folder later." This seems to be a loss of Excel functionality
  • Don't Save: Normal exit
• Restart Excel
• Personal.xlsb was open (View/Unhide shows Personal.xlsb as expected)
• Open Existing Workbook / References to UDFs produce #NAME? errors (as before implementing suggestion)
• Existing reference to BCVPersonal_xlsb in Existing Workbook was unchecked (it had been checked prior to preceding save)
• Rechecked reference to BCVPersonal_xls in Existing Workbook / F9
• Formulae referencing UDFs are functional -- Groundhog Day

My findings are:

• Storing the Personal.xlsb in C:\Program Files\Microsoft Office\root\Office16\XLSTART does cause the Personal.xlsb to be opened when Excel starts
• Rechecking the reference to the appropriate VBA Project in my Existing Workbook, does enable the references to UDFs to function properly.
• The Existing Workbook can be saved with the reference checked.
• There is a barrier to exiting Excel associated with storing the Personal Macro Workbook that is described above.
• Bypassing the error and reopening the Existing Workbook reverts to a condition in which the reference to the VBA Project is unchecked again.
• Storing the Personal.xlsb in C:\Program Files\Microsoft Office\root\Office16\XLSTART
  • did not produce an improvement, and
  • may have introduced a barrier to saving changes in the Personal Macro Workbook.

If the changes to files in the XLStart do not sync to other machines that can be acceptable because those changes between tranches of development can be migrated manually (i.e. sneaker net publishing). What seems totally ineffective to me is that all the references to the Personal.xlsb break every time even when multiple machines are not involved

• If that is important in some other context, it seems there should be a way of turning it off.
• It is not clear to me if this is a problem with Excel; or, with something OneDrive is doing behind the scenes or is triggering as Excel saves or opens the workbook.

 

[Jon Peltier]

Like I said, I hadn't tried this.
I shouldn't have let you move XLSTART from AppData to Program Files, just moved the files from one XLSTART to the other. When you start moving system and application folders around, things get weird.
Apparently, there also are permissions involved with working in Program Files.

Now that I'm more awake and can give it some rational thought, I think the problem with syncing to %APPDATA%\Microsoft\Excel\XLSTART\ is that each user has a different %APPDATA% path, so the links in Excel to the XLSTART files are broken. Syncing to C:\Program Files\Microsoft Office\root\Office16\XLSTART means each computer has the same path to the XLSTART.

So put all the pieces back the way they were. You can programmatically set the references you need when you open the workbook that has references.

You need to set a reference to Microsoft VBA Extensibility, and you need to trust access to the VB project.

Then this should be the Workbook_Open procedure in the workbook which needs to reference personal.xlsb. In my rudimentary tests, it seems to work fine.

Private Sub Workbook_Open()
  Dim vbProj As VBProject
  Set vbProj = ThisWorkbook.VBProject

  Dim bPersonalRef As Boolean
  Dim ref As VBIDE.Reference
  For Each ref In vbProj.References
    If ref.Name = "Personal" Then bPersonalRef = True
    Debug.Print ref.Name, ref.IsBroken
    If ref.IsBroken Then vbProj.References.Remove ref
  Next

  If Not bPersonalRef Then
    On Error Resume Next
    Dim wb As Workbook
    Set wb = Workbooks("personal.xlsb")
    If wb Is Nothing Then
      MsgBox "Personal.xlsb is not open"
    Else
      vbProj.References.AddFromFile wb.FullName
      If Err Then
        MsgBox "Couldn't add reference to Personal.xlsb"
      End If
    End If
  End If
End Sub

 

[BCVolkert]

Thanks, I gave it a go.

I did notice that after I moved XLStart my pinned shortcut to XLStart established when it was in %APPDATA% remained effective to the new location after the move. This should have been a clue for me and is certainly validation of your forecast of wierdness.

I agree that the resolution of %APPDATA% for each user will be different. In this case, I've been the only user of the group of workbooks having this problem so far. I suspect that there is a OneDrive facet to this problem that may be related to OneDrive's need to accommodate installation of Excel on various machines that may have differences. It's too bad that Excel does seem to have the capability of holding the reference via the contextual %APPDATA% rather than what appears to be the path of the moment to a particular user's folder or to some D:doc thing.

Having said All That. I used your code (substituting "BCVPersonal_xlsb" for "Personal") and it WORKED!

Now for the wierd part:

• Expecting a need for changes in other files having the same problem, I opened a second workbook that had been having the same trouble.
• Without having made the addition of Workbook_Open to the second workbook the UDFs worked there too !?!?!.
• Opened the first Workbook with the Workbook_Open code you provided -- still working (no real surprise).
• Deleted the Workbook_Open code. Save
• Opened
• UDFs not working -- no surprise
• Re-established the Workbook_Open code. Save
• Opened
• UDFs working -- no surprise
• Deleted the code between Private Sub and End Sub (leaving the Private Sub and End Sub lines in place). Save
• Opened
• UDFs STILL working -- BIG surprise

I repeated this a bunch of times in various permutations (i.e. second workbook open or closed, fully or partially commented out Workbook_Open .etc.) with pretty much the same surprising results. It does not make sense to me; but, I can pretty much predict when the UDFs will work or fail. So, there is more than randomness going on.

Jon,

I really appreciate your help.

I'm not sure if I still have a problem or not. The saving and reopening through the permutations was going pretty fast and I suspect syncing to OneDrive may be lagging. For the time being, the UDFs in my world seem to be working. So, I'm going to call it solved and move on to some other rat-killing I need to do. That'll give OneDrive some time to settle down and I'll see how things continue to perform. I'll come back here if trouble resurfaces or I identify a pattern that makes some sense.

Out for now. Thanks again.

Bruce

 

[Jon Peltier]

Wow, that is some really strange behavior.

 

[BCVolkert]

I know. If I were you, I probably would not believe it. I'll let you know what I figure out -- either way.

 

[BCVolkert]

Well I never really figured it out. My conclusion is that I need to maintain separate Personal.xlsb files on individual machines and issue an update from time-to-time that users install from a common location. An alternative approach is to use an .ini file on the common location that includes a version number. If the version number within the .ini file is greater than the version number of a local .ini file, an update procedure on the local machine copies the files from the common location to the local machine. But, that's a problem for another day.