Here's the reality: if your VBA macros only work when you open a file in the desktop app, then moving that file to SharePoint hasn't moved your workflow, it has split it in two. The web version of Excel doesn't run VBA, full stop. That's not a bug or a settings issue; it's a fundamental limitation of the browser-based experience. When you open the same file through OneDrive on your desktop, the code works because you're back in the full application. So you're not dealing with a sync problem yet. You're dealing with a compatibility problem that will keep surfacing every time someone clicks "Edit in Browser."
Now, about your real question: can multiple users update that file at the same time and have everything sync properly? In most cases, yes, but only under specific conditions. If everyone opens the file in the desktop app and the file lives in a SharePoint document library or a synced OneDrive folder, Excel's co-authoring engine will merge changes from different users in real time. That works well for data entry, formula edits, and formatting. But here's the catch: VBA macros are not part of that collaborative handshake. If your macro writes to cells, refreshes connections, or changes the structure of the sheet, co-authoring can become unreliable. You might see conflicts, or worse, the macro might run on a stale version of the file and overwrite someone else's changes. That's likely the source of the sync issues you've seen.
So what can you do? First, decide whether the macro is essential to the file's purpose. If it is, then you need to stop treating SharePoint as a place to run the file and start treating it as a storage and distribution hub. Keep the master file in SharePoint, but have users download a local copy, run their macros, and save changes back manually. That works, but it introduces version control headaches. A better path is to move the logic out of VBA entirely. Use Power Automate for file-level actions, Power Query for data transformation, or Excel's built-in dynamic array functions for calculations that don't require procedural code. If the macro is doing something truly complex, consider building a simple app in Power Apps that writes data back to a SharePoint list, then have Excel read from that list. That way, the file itself stays macro-free and fully collaborative.
The honest take here is that VBA and cloud collaboration are two different generations of tools, and trying to force them together will always produce friction. You can have a file that runs macros, or you can have a file that multiple people edit at the same time without issues. You rarely get both. The practical move is to identify what the macro actually accomplishes and ask whether that task needs to happen in real time, or whether it can be automated outside the spreadsheet. If it can, do that. If it can't, keep the macro file as a desktop-only tool and use SharePoint purely for distribution. Either way, stop expecting the web version to catch up. It won't, and your workflow shouldn't depend on it.