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(mailtoSheet)
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
Tools>Options>General>Error Trapping to get the desired result.

NickHK

"moonhk" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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(mailtoSheet)
> 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
> Tools>Options>General>Error Trapping to get the desired result.
>
> NickHK
>
> "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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(mailtoSheet)
> > 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



(E-Mail Removed) 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
> > Tools>Options>General>Error Trapping to get the desired result.
> >
> > NickHK
> >
> > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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(mailtoSheet)
> > > 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" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> (E-Mail Removed) 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
> > > Tools>Options>General>Error Trapping to get the desired result.
> > >
> > > NickHK
> > >
> > > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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(mailtoSheet)
> > > > 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" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> (E-Mail Removed) 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
>> > Tools>Options>General>Error Trapping to get the desired result.
>> >
>> > NickHK
>> >
>> > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > 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(mailtoSheet)
>> > > 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" <moon_ils-(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
> >
> >
> >
> > (E-Mail Removed) 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
> >> > Tools>Options>General>Error Trapping to get the desired result.
> >> >
> >> > NickHK
> >> >
> >> > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> > > 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(mailtoSheet)
> >> > > 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
>
>
>
> (E-Mail Removed) 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
> > > Tools>Options>General>Error Trapping to get the desired result.
> > >
> > > NickHK
> > >
> > > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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(mailtoSheet)
> > > > 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" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> >
> >
> >
> > (E-Mail Removed) 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
> > > > Tools>Options>General>Error Trapping to get the desired result.
> > > >
> > > > NickHK
> > > >
> > > > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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(mailtoSheet)
> > > > > 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" <moon_ils-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <moon_ils-(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>> >
>> >
>> > (E-Mail Removed) 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
>> >> > Tools>Options>General>Error Trapping to get the desired result.
>> >> >
>> >> > NickHK
>> >> >
>> >> > "moonhk" <moon_ils-(E-Mail Removed)> wrote in message
>> >> > news:(E-Mail Removed)...
>> >> > > 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(mailtoSheet)
>> >> > > 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
>> >> > >
>> >
>