PDA

View Full Version : VBA Error Handling Help



jo15765
01-10-2012, 03:35 PM
I am running the following code, and let's say that I hit Mice instead of Ice for the varBook name

varBooks = Array("Fire", "Mice")
For Each varBook In varBooks
On Error Resume Next
Set WB = Nothing
Set WB = Workbooks.Open(Filename:="C:\Testing" & varBook)
On Error GoTo 0
With WB
WB.SaveAs Filename:="C:\Daily\"
WB.Close
End With
Call "RunTimE"


Instead of just skipping and moving on to the Function RunTimE" It will throw an error of "Object Variable or With Block Variable Not Set" highlighting the line of WB.SaveAs

The code debugs fine, but just won't take my error handling :help

mikerickson
01-10-2012, 07:42 PM
Move the On Error Goto 0 to after the End With.

jo15765
01-10-2012, 08:14 PM
Moving the On Error GoTo 0 to After the With blew up my procedure, it started spooling to print a large workbook (which was the last workbook that was opened) and I had to reboot. I ran the code twice to verify it wasnt just a fluke, and it happened both times.

Kenneth Hobs
01-10-2012, 08:47 PM
You need to use F8 to step through the code. Blowing up does not help us help you.

You have many issues with that code where it can go south. Relying on On Error to solve a problem is usually not the best approach.

GTO
01-10-2012, 08:49 PM
A bit of a stab, but see if this helps:


Option Explicit

Sub untested_example()
Dim varBooks As Variant
Dim varBook As Variant
Dim WB As Workbook


'// Include the file extension. //
varBooks = Array("Fire.xls", "Mice.xls")
For Each varBook In varBooks
On Error Resume Next
Set WB = Nothing
'// Include the trailing seperator //
Set WB = Workbooks.Open(Filename:="C:\Testing\" & varBook)
On Error GoTo 0

'// Test to see if we set a reference before working against it. Also, //
'// if C:\Daily is a folder, you need the WB's new name included //
If Not WB Is Nothing Then
With WB
WB.SaveAs Filename:="C:\Daily\"
WB.Close
End With
'// Ditch the quote marks //
Call RunTime
End If
Next
End Sub

Sub RunTime()
'code...
End Sub


Mark

jo15765
01-13-2012, 06:02 PM
I am still struggling on this...my coding works to perfection if varBook does exist. However, if varBook does not exist, the code will go crazy and automatically jump to Step_Three and try to open that workbook, and once that worbook is opened it will start spooling each worksheet in that workbook to print!!!

What in my code is wrong? What I am trying to do is add in error checking in Part_One, Part_Two and part_Three so that if a varBook is entered that does not exist it will stop executing that module and move to the next module.

So if varBook "Fire" does not exist in Part_One, that's fine, let's carry on and see if it exists in Part_Two and Part_Three.

Can one of the guru's point out what I have coded here wrong?!

Kenneth Hobs
01-13-2012, 06:18 PM
Check if the workbook exists or is open before you try opening it. Use Dir() to determine if it exists. Use this to see if it is open.

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

jo15765
01-13-2012, 06:24 PM
Would I need to change stName to varBook so that I could actually pass my variable to that function to check?

Kenneth Hobs
01-13-2012, 06:47 PM
As I said, you have several issues to address. Even this might not consider all but will get you closer. This is how I work, I do what I can and then fix what I forgot to consider.

Sub test()
Dim varBooks() As Variant, WB As Workbook
Dim vPath As String, vPath2 As String, fn As String

vPath = "C:\Testing\"
vPath2 = "C:\Daily\"
varBooks = Array("Fire", "Mice")

For Each varbook In varBooks
varBooks = varBooks & ".xls"
fn = vPath & varBooks
If Dir(fn) = "" Then GoTo NextVarBook
If IsWorkbookOpen(fn) Then
Set WB = Workbooks(varBooks)
Else
Set WB = Workbooks.Open(fn)
End If
If WB Is Nothing Then
MsgBox fn, vbCritical, "Error Opening File"
GoTo NextVarBook
End If

With WB
.SaveAs vPath2 & varBooks
.Close
End With

RunTimE
NextVarBook:
Next varbook
End Sub

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

jo15765
01-14-2012, 06:47 AM
Kenneth did you take a look at the sample workbook I uploaded, or is this based off of my sample code? I uploaded a workbook showing my code in it's entirety since my coding isn't working that way the guru's here can see the full picture and understand what I am actually trying to do (hopefully)

jo15765
01-16-2012, 08:05 AM
Let me also add to my post that each module in the sample workbook I provided runs issue free if run individually. But when trying to run them all together is the issue.