PDA

View Full Version : Error handling not working



anne.gomes
06-04-2014, 06:09 PM
Hi,

I am trying to get my head around error handling, and it doesn't seem to work. I was wondering that is wrong with this code?



Sub OpenFile()
On Error GoTo errhandler1

Workbooks.Open fileName:="C:\Users\anneg\Desktop\Book8.xlsx"

Exit Sub

errhandler1:

MsgBox ("Failed to select" & "" & ActiveWorkbook.name)

Exit Sub

End Sub

GTO
06-05-2014, 02:21 AM
What does not work?

Bob Phillips
06-05-2014, 02:22 AM
What do you think is not working Anne? I don't have that file and when IU ran the code it jumped to the error handler. It did report upon the activeworkbook, which is meaningless in my view, but it did fire.

Personally, I wouldn't use an error handler here, I would check for it's existence and act accordingly, something like this



Sub OpenFile()
Const TARGET_FILE As String = "C:\Users\anneg\Desktop\Book8.xlsx"
Dim filename As String
Dim wb As Workbook

filename = Dir(TARGET_FILE)
If filename = "" Then

MsgBox TARGET_FILE & " not found"
Exit Sub
Else

Set wb = Workbooks.Open(filename:=TARGET_FILE)

'rest of code
End If
End Sub

BTW, you really should get out of the habit of throwing brackets around code unnecessarily, as in


MsgBox ("Failed to select" & "" & ActiveWorkbook.name)

It is not a problem here, but it is not always a no-consequence action, it might cause problems one day. Only use them when required.

anne.gomes
06-05-2014, 03:47 PM
Yes you are right, I really should. Thanks for the advice. Can you please help me with another problem?

I want to add to the chart title that already exists on 4 pivot graphs and add some text infront of the existing chart title. I have this code:



Option Explicit
Sub dotitles()

Dim StrNames(0 To 6) As String
Dim sh
Dim ch

StrNames(0) = "Easy Steel"
StrNames(1) = "Dimond"
StrNames(2) = "Fletcher Reinforcing"
StrNames(3) = "Fletcher Steel Ltd"
StrNames(4) = "Pacific Coil Coaters"
StrNames(5) = "Pacific Steel Group"
StrNames(6) = "Taurean Doors"

Windows("Basware Daily Report Data.xlsm").Activate
Sheets(Array("BU Aged Analysis WIP", "BU Queue Analysis", "Aged Analysis by BU", _
"test1")).Select

ActiveChart.HasTitle = True
For Each sh In Worksheets
For Each ch In sh.ChartObjects
If ch.Chart.HasTitle Then
ch.Chart.ChartTitle.Text = StrNames(0) & " " & ch.Chart.ChartTitle.Text
End If
Next
Next

End Sub



It doesn't work for some reason.

Can you please help?

Bob Phillips
06-05-2014, 04:52 PM
As before, worked for me. What do you get?

anne.gomes
06-05-2014, 05:09 PM
I changed the code:

But it give me an error,



Option Explicit
Sub dotitles()
Dim StrNames(0 To 6) As String
Dim Title
StrNames(0) = "Easy Steel"
StrNames(1) = "Dimond"
StrNames(2) = "Fletcher Reinforcing"
StrNames(3) = "Fletcher Steel Ltd"
StrNames(4) = "Pacific Coil Coaters"
StrNames(5) = "Pacific Steel Group"
StrNames(6) = "Taurean Doors"

Windows("Basware Daily Report Data1.xlsm").Activate
Sheets(Array("BU Aged Analysis WIP", "BU Queue Analysis", "Aged Analysis by BU", _
"test1")).Select

ActiveChart.HasTitle = True
For Each Title In Worksheets
If ActiveChart.HasTitle Then
Title.Chart.ChartTitle.Text = StrNames(0) & " " & Title.Chart.ChartTitle.Text
End If
Next


End Sub