0

I've inherited an Excel workbook containing a heap of macros that is sent on to a bunch of end-users to interact with. For some users (presumably with specific versions of Excel), the workbook crashes on open for an unknown reason. This happened to me initially, and I "fixed" it by recompiling the VBA Project - it compiled without error, and from that point on the workbook opened and ran successfully.

I would like to improve stability of the workbook for my end users, but they're very non-technical so getting them to interact with the developer console is a bit of a non-starter. I know I can compile the project on startup using VBA (I followed the instructions here: Can I compile VBA on workbook open?) but this errors unless the user has trusted access to the VBA project model - and I'm not sure enabling this for a non-developer audience is an amazing idea. Is there a different way of doing this? Ideally everything would Just Work - but I'm thinking there may not be many other options other than what I've discussed above...

bigsim
  • 132
  • 8
  • 3
    One approach is to add a start up instruction tab. The instruction tab should have the instructions to enable macros. When the workbook is closed use vbas to hide all other tabs. When the workbook is open, unhide the normal tabs and hide the start up tab using VBA. – TinMan Jun 14 '23 at 03:39
  • 1
    The immediate thing to do is figure out what's causing the crash and try to fix that. – Tim Williams Jun 14 '23 at 05:41
  • 1
    What do you mean with "crashing": Runtime error, Excel hangs, Excel quits, Computer blocked? – FunThomas Jun 14 '23 at 06:12
  • Feels like a sensible solution @tinman - thanks! – bigsim Jun 15 '23 at 03:50
  • @FunThomas Excel quits suddenly, with no message – bigsim Jun 15 '23 at 03:51

2 Answers2

0

I know this is not what you want to hear, but I’d recommend turning this into a real vb or .net application.

Macros in office are getting harder and harder to run. Eventually they may not even be supported as Microsoft moves Office more and more into the cloud.

Since I can't comment yet, I just want to say I agree porting to a program can be a large undertaking, depending on the complexity of the macros. However, if they are important to the business, serious consideration should be made about their future.

John Jackson
  • 31
  • 6
0

I think that John Jackson gave a nice idea to produce a strong program, but it could be a long and difficult journey depending on your code and your knowledge.

I used for long time an application or program to convert my Excel file thought VBA code to produce an exe program executed by many people. The result was strong and made a great job for me, for many years. Later, I decided to translate my program on VB and the process was very, very, long. And, It was not an easy job. For an expert, VBA is a park to play, but VB is a kind of Jungle. Are you ready to put your foot in the jungle.

Finely, what do you need? Your question is not clear.

Usually it's better to present your code. After that, we will be able to help you.

If you don't present some clear examples, I think your question will be "Closed" soon.

Jean Allard
  • 1
  • 2