External Excel spreadsheets and assorted VBA

Could error trap location e.g.

If not location is Nothing then
do stuff
else
msgbox(“Can’t find blah”)
end if

Also, add an error handler to make sure you always undo ScreenUpdating as otherwise it will barf and leave the screen locked so at top of function

On Error Goto ErrHandler

at bottom add:

Exit Sub

ErrHandler:
On error resume next
Msgbox(“Oops, all gone tits up”)
Application.screenupdating = true

end sub

Oh, is ‘Nothing’ a thing? IDNKT. Is that the same as Null or something else?

And yeah, shoving the whole thing in an If clause is the obvious way of doing it, schoolboy error. In my defence, I’ve been trying to write this all day whilst being interrupted every ten minutes. I’m shirking from home tomorrow so should be able to be a bit more focused.

You probably need to handle wbsource errors in case it doesn’t exist eg
(After the open)
If wbsource is nothing then
Msgbox (“source missing”, vbokonly+vbexclamation, “File errror”)
Else
All your other stuff
End if

Oh, is ‘Nothing’ a thing? IDNKT. Is that the same as Null or something else?

Yep and you use “is” rather than “=” to test.

You probably need to handle wbsource errors in case it doesn’t exist eg

Good point, though it’s far more likely that a source change will be “we’re using a new file” rather than “we’ve deleted the old one.” Though ofc it needs the network connection to the source so that needs to be trapped.

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.