# If and constant logical problem

#### earp_

##### Active Member
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?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Oaktree

##### MrExcel MVP
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_

##### Active Member
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

##### Well-known Member
Isn't there a clue in your thread title? - constant.

#### Oaktree

##### MrExcel MVP
I think you'll want both for your case:

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

#### earp_

##### Active Member
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

##### MrExcel MVP
Ok, then use:

Public myK4 As Integer

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

#### earp_

##### Active Member
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

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

Consider this:

Code:
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_

##### Active Member
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.

Replies
3
Views
378
Replies
4
Views
186
Replies
14
Views
406
Replies
12
Views
1K
Replies
13
Views
474

Threads
1,191,420
Messages
5,986,464
Members
440,031
Latest member
davidvillegasr

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back