Forum menu
Another trick, to stop it prompting the user to save the workbook you've just opened is:
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Then add "Application.DisplayAlerts = True" to the error handler as well.
Also, in the error handler you can add debug e.g.
Msgbox("Oops, error was " & Err.description)
Another trick, to stop it prompting the user to save the workbook you've just opened is:
I did that with wbSource.Saved = True - is that way any better / worse / different to this one?
(WhyTF it wants to save an unchanged sheet in the first place is beyond me...)
And you can speed it up by setting application. Calculation to manual at the top and back to automatic at the bottom, especially if you're shifting stuff that is then used in other formulae.
Sorry, what's that do? (Yes, I should Google but I'm eating...!)
Application.Calculation controls whether excel recalculates whenever anything changes or not. So if you set it to manual you can make changes without any overheads, then set it back to automatic at the end of your routine and it'll do all the sums once. I think the values are xlcalculationmanual or somessuch but the options pop up in the vba editor when you're typing so I'm sure you'll work it out 😉
I did that with wbSource.Saved = True - is that way any better / worse / different to this one?
Not uses that one myself. With Excel / VBA there are normally many ways of doing any one thing, so you just settle on what you've used before....
I found a more elegant way of doing it than both of these. You can add SaveChanges:=False to the .Close statement and it releases without prompting.
Anyway. Cooking with gas now, got the thing doing mostly what I want, and hooked it into an event handler so it automatically fires the script when the key field is updated. Chuffed with that.
The only major glitch I've got outstanding is that the data file is accessed via a UNC pathname which is actually a Sharepoint server. On some clients, the users are prompted for their Windows credentials if they open the data file manually via Sharepoint. On those clients, the script fails. If they open the file, supply credentials and then close it again, it caches the credentials and everything works again. This seems to be a bug / configuration error on some PCs and I can't readily work out how to get it to prompt for credentials. Hmm.
Anyway. Cooking with gas now, got the thing doing mostly what I want, and hooked it into an event handler so it automatically fires the script when the key field is updated. Chuffed with that.
If you have a function called by an event handler, always a good idea to add in Application.EnableEvents = False at the top and undo it at exit (and in the error handler). This will stop any unintended recursion eg if the function over writes a cell, which triggers a 'cell change' event etc...
On some clients, the users are prompted for their Windows credentials if they open the data file manually via Sharepoint. On those clients, the script fails.
You'll be able to ask the OS in VBA and determine this in advance - have a google, then yuo can trap it and prompt the user before hand.
always a good idea to add in Application.EnableEvents = False at the top and undo it at exit (and in the error handler).
Exactly what I did. I was doing it as a wrapper every time I wrote to that cell, figured it was more elegant to just put one at the start and end of the main sub, then got legged up when I tripped an Exit Sub call earlier in the routine (right when I was showing a colleague, for added yuks).
You'll be able to ask the OS in VBA and determine this in advance - have a google, then yuo can trap it and prompt the user before hand.
Yeah, I think I need to be on a symptomatic PC to test it properly, Google's giving a lot of conflicting information.
Whilst the crew are in, is there any way to search for a sheet in a workbook by name? This workbook has about 60 sheets and they aren't in alphabetical order.
Workbooks.<sheetname> innit.
then got legged up when I tripped an Exit Sub call earlier in the routine
best to use "Goto EarlyExit" and only have a single exit point
and then define
EarlyExit:
clean up stuff
Exit Sub
ErrHandler:
Clean up more stuff
End Sub
Workbooks.<sheetname> innit.
You'd have to trap that as it may not work.
Something like..
Public Function FindWSIndex(ByVal Name as String) As Integer
For FindWSIndex = 1 to Worksheets.count
if Worksheets(FindWSIndex).name=Name then
Exit function
Endif
next n
# We failed
FindWSIndex=0
End Function
Ta, will consider adding it cos it's not my document 🙂
best to use "Goto EarlyExit"
I was always taught goto = bad, is that not the case in the 21st Century?
You can also do
Public function findws (name as string ) as worksheet
Dim ws as worksheet
For each ws in thisworkbook.worksheets
If ws.Name = name then
Set findws = ws
End if
Next ws
End function
That (or something like it, free styling avain) will return the worksheet object so you can work with it directly.
I was always taught goto = bad, is that not the case in the 21st Century?
People used to stay that to stop kids writing spaghetti code in BASIC on their Spectrums. Like all tools, it can be used or mis-used. Otherwise the people who made the language (who are bigger geeks than us, most likely) wouldn't have taken the time to put it in there.
Anyone who gives out hard and fast rules about programming doesn't understand it properly.
Oh..wait.. shit.