Solved

VBA Macros + Shared Workbook + Excel 2010 = :(

Posted on 2013-01-25
14
7,432 Views
Last Modified: 2013-10-24
Hi,

I have run into a dilemma where a built apreadsheet solution with VBA incorporation throws up errors once the workbook is 'shared' to allow multi-user editing. The purpose of the workbook is to allow 200+ users to access it at anytime, edit it, save the file and exit it.

Without the sharing feature enabled within Excel, the solution works great. With sharing enabled, it falls over. I am getting 404, runtime and other various errors. I believe this is due to the convoluted nature of the sharing component within Excel. Reading a tonne of forum posts online, it appears that when sharing a workbook, the VBA component is extremely restrictive (i.e not viewable by default) but also also can make execution of macros challenging.

Is there a workaround for this? My code uses event changes and custom procedures. I'm really stuck and need to resolve this immediately.

Any help is greatly appreciated.

Thanks
0
Comment
Question by:just4kix
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 total points
ID: 38820020
A great many Excel features are simply unavailable in shared workbooks.  While VBA is not blocked entirely, and VBA code that tries to use features that are unsupported in shared workbooks will fail.

My advice is to never try to use shared workbooks to allow multi-user support.  If you really need to allow multiple users in at once, then you should use a database.  Trying to do this with shared workbooks is, in my opinion, asking for trouble.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38820276
I know this isn't what your asking, but I'm with Matt.  Shared workbooks will be more pain then they are worth.

Have you used them extensively before?  I've heard they are prone to corruption.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38821466
Hi, ust4kix.

Ditto.

If you absolutely, positively have to give your users an Excel solution and there aren't too many daily updates and all users are LAN-based then the following, frankly silly idea might be a runner...
(1) Users open a non-shared version from their personal network directory.
(2) When a user opens their file, a macro reads the centralised list of transactions and updates their file.
(3) When the user updates/adds a record, a macro adds an entry to the centralised list of transactions (a simple text file in a generally accessible area on the LAN. (Optionally, the macro could check for updates since the last time it accesses the file.
(4) Every night, a centralised process creates a new master file by taking the previous night's version and applying all the changes in the centralised list. The new master is then copied to each user's personal directory ready for the following day. A new, empty centralised list is created.

Regards,
Brian.
0
 

Author Comment

by:just4kix
ID: 38822650
It's really a shame that there is no easy answer for this and to be honest I am disappointed in Excel acting in this way. Being such a widely used software application across the globe, one would think that the flexibility of complete VBA execution in Shared Workbooks would be a common functional requirement. It appears not.

Thanks for the feedback and yes as per the reply above, the network worksheet/data write was my plan B. I was kind of hoping I wouldn't need to do it though as it now will involve much more time, logic and code.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38822872
You wrote:

one would think that the flexibility of complete VBA execution in Shared Workbooks would be a common functional requirement.

I think you're missing the point.  It's not a matter of "complete VBA execution".  Rather, it is that certain parts of the application are off limits entirely for shared workbooks.  It's not that certain parts of the VBA object model are turned off--it's that certain features of the product itself are.
0
 

Accepted Solution

by:
just4kix earned 0 total points
ID: 38824513
I've requested that this question be closed as follows:

Accepted answer: 20 points for matthewspatrick's comment #a38820020
Assisted answer: 0 points for just4kix's comment #a38822650

for the following reason:

When Sharing workbooks to allow multi-user editing, core parts of basic functionality in Excel is effectively restricted.

Any VBA code to clearly access these components will evidently fail and result in errors.

The options are to;
1) Remove the problem VBA code
2) Opt for an alternative solution
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 26

Expert Comment

by:redmondb
ID: 38824514
matthewspatrick not only suggested what's probably the best workaround.but also explained the Excel issues. The OP is obviously upset about Excel's functionality but that's not a valid reason for requesting that the points be reduced to 20.
0
 

Author Comment

by:just4kix
ID: 38824587
@redmondb - Thanks for your opinion. And it is only that. I am actually so upset that I am considering counselling.

Jokes aside the answer which he provided explains what the problem may be and recommended and I quote "My advice is to never try to use shared workbooks to allow multi-user support". This is not backed up by any fundamental fact and is purely his opinion. Sharing workbooks with multi-user support is a default feature of this product and I would not want one reading this post to be completely deterred by this statement and/or functionality in the acceptance of this being a solution.

My answer (not point awarded mind you) allows a forum reader reading this post to quickly reference the available options in this given circumstance and does not deter the user away from Shared workbooks completely. His answer is not worth the original points and it is a one sided answer (deterrent) to this feature. Shared Workbooks with different VBA elements to mine may work perfectly fine in various other situations.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38824661
Thanks for the response, just4kix. The situation is even worse than you think - this question is simply the latest one of the many that include warnings by world-class Excel experts (including MS MVP's) about the dangers of shared workbooks. I have yet to see a contradictory response by a person of that calibre.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38825193
just4kix,

You are correct that it is my opinion that using shared workbooks is asking for trouble.  That said, as Brian notes in http:#a38824661, that opinion is widely shared.

I happen to be a Microsoft MVP, which does not make me perfect nor does it mean you should take my advice on faith, but I can tell you that it is the consensus among MVPs that shared workbooks is not Excel's forte.  That also happens to be the consensus among the top Excel Experts here, at MrExcel.com, and at other forums I have spent time in.

Why?  Part of it is that shared workbooks cannot use many of the features that power users love about Excel.  However, probably the greater part is that shared workbooks are very, very prone to corruption.

It is not guaranteed that shared workbooks will become corrupt, but they do become corrupt at a vastly higher rate than non-shared workbooks, and it is my considered opinion that this greater risk makes using shared workbooks a Very Bad Idea.

Just about the only use for shared workbooks that most Excel experts will support is to enable the "track changes" feature.  However, even then, the overwhelming majority of Excel experts I know would still say not to use workbook sharing to enable multi-user support.

Now, am I trying to deter people from using shared workbooks?  Yes!  Do people have to listen to me?  Absolutely not.  But if anyone asks me about using this feature, I answer honestly: I wouldn't recommend it.

I love Excel.  It is awesome at lots of things, good at others, so-so on some, and bad on a few.  Shared workbooks falls into that last category.  That is my opinion, but I am not alone.

Patrick
0
 

Author Comment

by:just4kix
ID: 38826095
Thanks for the feedback and warnings about this feature. I will definitely keep it in mind for future projects.
0
 

Expert Comment

by:JECoyne
ID: 39553023
I am new to Experts Exchange and do appreciate all the helpful feedback.  I was trying help a small group of users not have to manually merge 3-5 copies of the same simple spreadsheet.  Because of off the possible issues it might create, I'll probably just build them a simple database as suggested.  I appreciate the enthusiastic and informative responses.
0
 

Expert Comment

by:JECoyne
ID: 39597367
To all commenters,

I trust the sage advice of those who have attempted to use known features that simply don't work as anticipated.  I am building the functionality I needed into Access database to accommodate the 3-5 user input need.  For the 200 user solution, Access may be acceptable, but I would consider SQL Server due to the potential volume. My Access front end sits on a SQL Server back-end database and I have not encountered any issues.

I just want to share my experience in the hope that it assists another member of this forum. I'm still very new to EE but have quickly learned I have access (no pun intended) to some of the best technicians available and truly value their input.

Thanks
Jeff
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now