How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

.....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg = VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" & FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

All the usual error and debug statements work the same in class modules.

There is a setting, which may be affecting you. Play with the settings at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg = VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" & FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class modules.

There is a setting, which may be affecting you. Play with the settings at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg = VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" & FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class modules.

There is a setting, which may be affecting you. Play with the settings at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg = VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" & FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

I get "d=9" as expected.
Then an empty Msgbox as h is still at its default "".

Not sure how you get 10, or expect an error.
What do expect the Str function to do ?

NickHK

"moonhk" wrote in message
ups.com...
Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class
modules.

There is a setting, which may be affecting you. Play with the settings
at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg =
VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" &
FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

Try this one. it seems like this is the way of + oprator to deal with both
expressions in vba.

Public Sub x()
Dim d As String, d1 As String, d2 As String _
, d3 As String, d4 As String
Dim s1 As String
On Error Resume Next
s1 = "9"
d = 1 + 9
d1 = 1 + s1
d2 = "1" + s1
s1 = "a"
d3 = 1 + s1
If d3 = "" Then
d3 = "err"
End If
d4 = "1" + s1
MsgBox "d=" & d & " : d1=" & d1 & " : d2=" & d2 _
& " : d3=" & d3 & " : d4=" & d4
End Sub

keizi

"moonhk" wrote in message
ups.com...
Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class
modules.

There is a setting, which may be affecting you. Play with the settings
at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg =
VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" &
FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

My excel prompted "Type Mismatch" Run time error 13 on d3 = 1 + s1

kounoike wrote:
Try this one. it seems like this is the way of + oprator to deal with both
expressions in vba.

Public Sub x()
Dim d As String, d1 As String, d2 As String _
, d3 As String, d4 As String
Dim s1 As String
On Error Resume Next
s1 = "9"
d = 1 + 9
d1 = 1 + s1
d2 = "1" + s1
s1 = "a"
d3 = 1 + s1
If d3 = "" Then
d3 = "err"
End If
d4 = "1" + s1
MsgBox "d=" & d & " : d1=" & d1 & " : d2=" & d2 _
& " : d3=" & d3 & " : d4=" & d4
End Sub

keizi

"moonhk" wrote in message
ups.com...
Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class
modules.

There is a setting, which may be affecting you. Play with the settings
at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg =
VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" &
FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

Hi NickHK

Sorry , should be MsgBox "d=" & VBA.Str(d).

My Question is d is string , why d = 9 + 1 is allowed. It should be
type mismatch.

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & VBA.Str(d)

End Sub

moonhk wrote:
Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class modules.

There is a setting, which may be affecting you. Play with the settings at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet, kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg = VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = "" Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\" & FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

That's VB/VBA's Type Coercion, called Evil Type Coercion by some. AFAIK,
this is because VB/VBA variables are not strongly typed, as they are in
other languages.
The code below seems completely wrong to me; that should result in a type
mismatch, but does not:
Dim d As String
d = "10"
d=d*d

OK, this makes sense
?cstr(9)+cstr(1)
91
But.....
?cstr(9)+cstr(1)*cstr(9)+cstr(1)
19
Given the result above, this makes sense
?typename(cstr(9)+cstr(1)*cstr(9)+cstr(1))
Double

Not an answer to your question, but that is how VBA deals with coercion.
For a related discussion, see :
http://groups.google.co.uk/group/mic...360954d46458ef

e.g. for why
?1="1"
True
?cvar(1)=cvar("1")
False

NickHK

"moonhk" wrote in message
ups.com...
Hi NickHK

Sorry , should be MsgBox "d=" & VBA.Str(d).

My Question is d is string , why d = 9 + 1 is allowed. It should be
type mismatch.

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & VBA.Str(d)

End Sub

moonhk wrote:
Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class
modules.

There is a setting, which may be affecting you. Play with the
settings at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update
debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet,
kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg =
VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = ""
Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\"
& FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub

My excel also throws error "Type Mismatch Run time error 13" when i comment
out On Error Resume Next, but it doesn't throw any error without that and
returns "d=10 : d1=10 : d2=19 : d3=err : d4=1a".

+ oprator in VBA seems to me to behave like in VB.NET when you set Option
strict to Off, as described below
http://msdn.microsoft.com/library/de...96ce56dd5f.asp

keizi

"moonhk" wrote in message
oups.com...
My excel prompted "Type Mismatch" Run time error 13 on d3 = 1 + s1

kounoike wrote:
Try this one. it seems like this is the way of + oprator to deal with
both
expressions in vba.

Public Sub x()
Dim d As String, d1 As String, d2 As String _
, d3 As String, d4 As String
Dim s1 As String
On Error Resume Next
s1 = "9"
d = 1 + 9
d1 = 1 + s1
d2 = "1" + s1
s1 = "a"
d3 = 1 + s1
If d3 = "" Then
d3 = "err"
End If
d4 = "1" + s1
MsgBox "d=" & d & " : d1=" & d1 & " : d2=" & d2 _
& " : d3=" & d3 & " : d4=" & d4
End Sub

keizi

"moonhk" wrote in message
ups.com...
Tried. Work. Thank a lot

Other Question, Below progam return 10. It should be error. Due to d is
string . Do you know why ?


Option Explicit
'Module : Module1
Public x As New Class1
Public h As String

Sub st()
x.x

MsgBox h
End Sub

Option Explicit
' class module : class1
Public d As String

Public Sub x()
d = 9 + 1
MsgBox "d=" & Str(9)

End Sub



wrote:
Thank . I will try.

NickHK wrote:
All the usual error and debug statements work the same in class
modules.

There is a setting, which may be affecting you. Play with the
settings
at
ToolsOptionsGeneralError Trapping to get the desired result.

NickHK

"moonhk" wrote in message
ps.com...
How to debug a class ? Like below module, Need to update
debug.print
statement in class. It is good method ?

Public mail As New clsMail

Public Sub Send()
On Error GoTo errHand
With mail
.init_me
.Process_File <== Program stop here
End With
Exit Sub
errHand:
MsgBox "Modules: Mail, Public Sub Send" & Chr(13) & _
VBA.Str(Err.Number) & " " & Err.Description, vbCritical

End Sub

clsMail

....
Sub Process_File()
Dim FN As String ' For File Name
Dim Msg As String
Dim lochkBillto As Integer
Dim k As Variant
Dim StateDate As Variant
Dim loStateDateText As String
Dim kfn As String
Dim loSheet As Variant

Dim ThisRow As Long
Dim MediaFileLocation As String
Msg = ""
Application.ScreenUpdating = False
'MediaFileLocation = "c:\YOURFOLDER\*.YOURFILEEXTENSION"
MediaFileLocation = Statement_Dir + "\*_*.xls"
'~~ MsgBox Statement_Dir
FN = Dir(MediaFileLocation)
Debug.Print "FN=" & FN
Do Until FN = ""
ThisRow = ThisRow + 1
'~~Cells(ThisRow, 1) = FN
k = VBA.Split(FN, ".", -1, vbTextCompare)
'~~ 454386_yyyymmdd.xls
StateDate = VBA.Split(k(0), "_", -1, vbTextCompare)
'~~MsgBox "StateDate " & StateDate(1)
kfn = StateDate(0)
Debug.Print "kfn=" & kfn
loStateDateText = StateDate(1)
Debug.Print "loStatDateText=" & StateDate(1)
'~~MsgBox "Bill to " & k(0)
Debug.Print "Control_name=" & Control_NAME

lochkBillto = Search_Billto(Control_NAME, mailtoSheet,
kfn)
Debug.Print "Bill-to=" & lochkBillto
Debug.Print "mailtosheet=" & mailtoSheet
If lochkBillto 0 Then
'~~ Get Information
Set loSheet =
Application.Workbooks(Control_NAME).Sheets(mailtoS heet)
Process_flg =
VBA.Left(VBA.UCase(loSheet.Cells(lochkBillto,
2)), 1)
Debug.Print "Process_flg = " & Process_flg
If Process_flg = "Y" Then
StateDateText = ChangeDateEnglish(loStateDateText)
Company = loSheet.Cells(lochkBillto, 3)
Mailto = loSheet.Cells(lochkBillto, 4)
cc = loSheet.Cells(lochkBillto, 5)
If VBA.Trim(Mailto) = "" And VBA.Trim(cc) = ""
Then
'~~MsgBox "blank found"
Mailto = EmailAddress
End If
'~~MsgBox "Mailto " & Mailto
cntFileSend = cntFileSend + 1
Application.StatusBar = "Processing ... " &
Statement_Dir & "\" & FN & " , " & _
"Number of file = " & cntFileSend
Call Send_mail(Statement_Dir, FN)
'~~ Move file
kill_file (History_Dir & "\" & FN)
Name Statement_Dir & "\" & FN As History_Dir & "\"
&
FN
End If
End If
FN = Dir
Loop
Application.ScreenUpdating = True
MsgBox "Number of Files sent = " & cntFileSend
End Sub