Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Macros + Shared Workbook + Excel 2010 = :(

Posted on 2013-01-25
14
Medium Priority
?
9,904 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 93

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
 
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 93

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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