Public variable in shared workbook

[Starkey]

Hey guys

I'm looking to build a workbook that'll be added to a shared network. I also plan to allow different users to access the workbook at the same time

The workbook will include VBA and in that a number of public variables. Am I right in thinking that each PC on the network would store their own set of values for the public variables and this won't be overwritten when the workbook is saved?

Eg when a user logs on there is a public variable that captures the ID of the users. This value will obviously be different for each user using the spreadsheet. Will a save of the workbook reset the value of the public variable to the same value for everyone logged on?

Hope that makes sense (bet it doesn't!)

Cheers
Starkey

 

[VoG]

I think that each user's public variable will be held locally.

I cannot test this right now but an interesting question

 

[Norie]

Starkey

Why not just try it and see?

I've tried to find something on this topic but there seems to be nothing.

I did come across one posted on another Excel/VBA forum that was basically the same.

It was posted in 2008 and never answered.

PS Have you checked all the other <strike>problems</strike> limitations of shared workbooks? I think code might be affected.

 

[tweedle]

The public variables are held within each instance of a shared workbook and persist through saves.
[made perfect sense, btw]

 

[Starkey]

Thanks everyone for coming back to me.

Unfortunately I can't test because I haven't the shared network in place but I'm reassured but tweedle's response

You've got me worried now norie, looks like I have to do some more reading up! I've thought of a way of preventing 2 users accessing the same record in the form and other than the public variable question I thought I had all bases covered!

Cheers
Starkey

 

[Norie]

Starkey

Sorry about that.

You can probably ignore me - I've kind of got a dislike of shared workbooks after a bad experience.

That was some time ago so I'm sure it's different now.

 

[Starkey]

Ha no worries. It worries the hell out of me but I'm going to bite the bullet as it'll be worth it.

The only thing that worries me is the strange behaviour at times when I run the ActiveWorkbooks.Save command when a form is open. Ive lost the values stored as public variables doing that before. But I know enough about coding by now to know that it's always something I've done wrong, it's never excel!

Cheers
Starkey

 

[Norie]

Good luck.

Just make sure you back up all your data or preferably try things out with dummy data/workbooks.