If and constant logical problem

[earp_]

Hi i have problem with a long code.
So I divided that code in 4 parts.

So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()

I have declared

Option Explicit

Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer

all my sub have the following structure:

Sub Macro1()

'If (myK1 = 0) Then
' myEnter = myC
' myK1 = 1
'End If

With Sheets("Sheet1").Range("J2")
'7.40
If .Value >= TimeSerial(7, 0, 0) And .Value < TimeSerial(7, 40, 0) Then
With ThisWorkbook
...
End With
If (myEnter = 0) Then
...
myEnter = 1
End If
End If

'8.00
If .Value >= TimeSerial(7, 40, 0) And .Value < TimeSerial(8, 0, 0) Then
With ThisWorkbook
...
End With
If (myEnter = 1) Then
...
myEnter = 2
End If
End If

'8.00
If .Value >= TimeSerial(8, 0, 0) And .Value < TimeSerial(8, 40, 0) Then
With ThisWorkbook
...
End With
If (myEnter = 2) Then
...
myEnter = 3
End If
End If
...
and so on

myEnter is declared as Integer and it's initial value is 0

Dim myEnter As Integer

The question is that if I start the macro at 7.00 everything run ok because myEnter Updates itself
but if I run it at 7.41 I will miss the update and it will not go inside

If (myEnter = 2) Then
...
myEnter = 3

So i thought...well let's use this

If (myK1 = 0) Then
myEnter = myC
myK1 = 1
End If

and if I declare myC as

Const myC As Integer = 1

I should revolve this.
The problem is when for example in macro4 I have

If (myK4 = 0) Then
myEnter = myC
myK4 = 1
'MsgBox myEnter
End If

myEnter doesn't have that value, but it's empty

any suggests?

 

[Oaktree]

Not sure I understand what you mean, but I'm guessing that you want to carry the value of myEnter (or myC) from one module to the next.

If that's right, try declaring myEnter with Public myEnter As Integer outside of your subs:

i.e.

Module 1:

Public myEnter as integer

Sub stuff()
'code goes here
End sub

Module 2

Sub otherstuff()
'code runs after the stuff sub in module 1 and references myEnter, which keeps its value assigned in module 1 stuff because it was declared as public
end sub

Is that what you mean?

 

[earp_]

yep something like that.
the question is that in php or C or whatever I can declare a variable and assign a value to that variable, and that would be perfect for me.
But if i'm not wrong in vba i can't.
I can only declare it and assign a value into a sub and not outside..
This is allowed

Sub myMacro
Dim myK4 As Integer = 22
...
end Sub

but this is not

Dim myK4 As Integer = 22
Sub myMacro
...
end Sub

 

[Norie]

Isn't there a clue in your thread title? - constant.

 

[Oaktree]

I think you'll want both for your case:

Public Const myK4 As Integer = 22
Sub myMacro()
...
End Sub

 

[earp_]

mmm
If I declare this

Public Const myK4 As Integer = 22

myK4 = 22
then I go inside the Sub Macro1 and here myK4 will be updated into another number let's say 69
When I recall again Sub Macro1 I want the updated number 69 (not myK=22), but because myK is a constant, myK is still = 22.
That's why I don't want constants.

 

[Oaktree]

Ok, then use:

Public myK4 As Integer

Sub myMacro()
myK4 = 22
...
End Sub

 

[earp_]

ok but

Public myK4 As Integer

Sub myMacro()
myK4 = 22
...
End Sub

inside myMacro there is a procedure which updates myK4 to 69 for example.
When I recall myMacro if I leave myk4 = 22 on the top

Su mymacro()
myK4 = 22

myK is still 22 and not 69

 

[Oaktree]

I'm sorry, I don't understand what you are trying to do.

Consider this:

Public myk4 As Integer

Sub test()
mymacro1
mymacro2
mymacro3
End Sub

Sub mymacro1()
myk4 = 22
End Sub

Sub mymacro2()
myk4 = 52
End Sub

Sub mymacro3()
MsgBox myk4
End Sub

running test will display the message box 52, as that was the last value assigned to myk4. If you run mymacro1 again, mymacro1 would naturally change the value of myk4 to be 22 again.

 

[earp_]

No worries, but this is something else.
I'll write it again and simplified than my first post.

Public myk4 As Integer

Sub test()
mymacro1
mymacro2
mymacro3
End Sub

Sub mymacro1()
if(myK4 = 22) then
'do something
myk4 = 23

if(myK4 = 23) then
'do something
myk4 = 24

if(myK4 = 24) then
'do something
myk4 = 25

End Sub

Sub mymacro2()
if(myK4 = 25) then
'do something
myk4 = 26

if(myK4 = 26) then
'do something
myk4 = 27

if(myK4 = 27) then
'do something
myk4 = 28

End Sub

Sub mymacro3()
if(myK4 = 28) then
'do something
myk4 = 29

if(myK4 = 29) then
'do something
myk4 = 30

if(myK4 = 30) then
'do something
myk4 = 31

End Sub</pre>

Sometimes I want to start from the third with myK = 24.
Now, if I declare myK = 22 as const everytime it will start from the first if. And I don't want this.