Stop a macro running more than once (hasRun = True)

[Hendrixx]

Hi all,
I am a trader (and have zero programming experience of any sort - although I have learnt a lot from this site and others over the past month or so - now I know just enough to be dangerous!!).

Most of my macros are not particularly sophisticated - they simply call other macros, often on a schedule (and this because the base spreadsheet I am using is a sample one provided by the broker, and it contains macros configured to connect, send orders, update prices etc, and I don't want to mess with these).

I have managed to automate most of what I want, but am having trouble with the following code....in that it runs every time it is called (it gets called every 60 seconds to "watch the market").

I lifted the "hasRun" method from somewhere online, as well as the CDO_Mail code. I am only using this code to send an email at the moment - eventually, if I can get it working properly and reliably, the "mail" type of the code will be replaced and it will send the relevant order directly.

Anyway, here is my problem code....(I have removed email addresses).

Sub SendMailTWShortLevel1()


Dim hasRun As Boolean
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
strSubject = "TW Short Level 1"
strFrom = "xxxxxxxz@gmail.com"
strTo = "xxxxxxx@yahoo.com"
strCc = ""
strBcc = ""
strBody = "TW Short, move stop to (L1) " & Range("_TWShortLevel1")
On Error GoTo ErrorCatch


If hasRun = True Then[INDENT]Exit Sub[/INDENT]
End If

If hasRun = False And Time > TimeValue("13:30:00") And Range("_TWLevel1Direction") = "BUY" And Range("_TWSecondaryLow").value <= Range("_TWL1") Then

[INDENT]Set CDO_Mail = CreateObject("CDO.Message")[/INDENT]
[INDENT]Set CDO_Config = CreateObject("CDO.Configuration")[/INDENT]
[INDENT]CDO_Config.Load -1[/INDENT]


[INDENT]Set SMTP_Config = CDO_Config.fields[/INDENT]


[INDENT]With SMTP_Config[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxxxxx@gmail.com"[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxx"[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25[/INDENT]
[INDENT=2] .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True[/INDENT]
[INDENT=2] .Update[/INDENT]
[INDENT]End With[/INDENT]


[INDENT]With CDO_Mail[/INDENT]
[INDENT=2] Set .Configuration = CDO_Config[/INDENT]
[INDENT]  End With[/INDENT]


[INDENT]CDO_Mail.Subject = strSubject[/INDENT]
[INDENT]CDO_Mail.From = strFrom[/INDENT]
[INDENT]CDO_Mail.To = strTo[/INDENT]
[INDENT]CDO_Mail.TextBody = strBody[/INDENT]
[INDENT]CDO_Mail.CC = strCc[/INDENT]
[INDENT]CDO_Mail.BCC = strBcc[/INDENT]
[INDENT]CDO_Mail.send[/INDENT]
End If

hasRun = True

Exit Sub

ErrorCatch:
MsgBox Err.Description

End Sub

The mail sends fine. My issue is possibly one of my understanding i.e., that once the code has run once, the variable hasRun sets to "True", and thus when it encounters the If hasRun = True the next time, it should just exit the Sub, but this is obviously not correct, as the email sends every 60 seconds.

I'm thinking the issue may be because this code is called from an outside (within the same workbook) macro which may somehow reset "hasRun" each time it calls it.

Could someone please tell me what I am doing wrong?

Regards
Hendrix

 

[Dim Me as xlNoob]

Hi Hendrix,

You need to declare hasRun as a Public variable.

Remove this line:

Dim hasRun As Boolean

And then at the very beginning of the module **outside of any subroutines** insert this line:

Public hasRun As Boolean

Hope this helps.

 

[Hendrixx]

Thanks Dim Me as xlNoob,

I neglected to mention that this is one of a number of similar macros (4 for each market I trade)....all are in the same module, and all are cycled through every 60 seconds (I did try using "Worksheet_Calculate" to accomplish something similar, but this just locked excel up - there are price feeds coming in, etc etc, and I think it was all too much....or maybe it was just badly written!).

I did have it declared originally as a Public variable, but (I could be wrong, and maybe it was an issue elsewhere) this seemed to cause none of the relevant macros to run after one had done so....ie, if the one above had run, none of the other similar ones would then run if their conditions were triggered.

Does this sound plausible?

 

[Hendrixx]

Actually, I MAY have just answered my own question.....if I set each routine with its own "hasRun" (hasRun1, hasRun2, etc) and declare them all as Public.....

 

[Dim Me as xlNoob]

Yes if it is a public variable it means that any macro in the workbook has access to it, so one macro changing it to True would make it True for all.

Ok to make the variable available only to the module it is in, place this line in each module which uses the variable (before the start of the subs):

Dim hasRun As Boolean

This sets the scope of the variable to the module only so there should be a different instance of 'hasRun' for each module using it if that makes sense.

So with variable scope hopefully this helps you understand:

Public Var1 as Variant ' This is available to ALL macros in the workbook
Dim Var2 as Variant ' This is available to macros in THIS MODULE only
Sub substartshere()
Dim Var3 as Variant ' This is available to THIS SUBROUTINE only

     ' Code

End Sub

Does that make sense?

 

[Hendrixx]

It does. Thank you! This would mean I'd need to put each macro in its own module, correct? I will do that. Thanks for all your help. I appreciate it.

 

[Dim Me as xlNoob]

Yes that's correct. Glad to help!