just4kix
asked on
VBA Macros + Shared Workbook + Excel 2010 = :(
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
You wrote:
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
@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.
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.
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.
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
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
ASKER
Thanks for the feedback and warnings about this feature. I will definitely keep it in mind for future projects.
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.
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
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
Have you used them extensively before? I've heard they are prone to corruption.