The Set statement is only used for object variables (like Range
, Cell
or Worksheet
in Excel), while the simple equal sign '=' is used for elementary datatypes like Integer
. You can find a good explanation for when to use set here.
The other problem is, that your variable g1val
isn't actually declared as Integer
, but has the type Variant
. This is because the Dim statement doesn't work the way you would expect it, here (see example below). The variable has to be followed by its type right away, otherwise its type will default to Variant
. You can only shorten your Dim statement this way:
Dim intColumn As Integer, intRow As Integer 'This creates two integers
For this reason, you will see the "Empty" instead of the expected "0" in the Watches window.
Try this example to understand the difference:
Sub Dimming()
Dim thisBecomesVariant, thisIsAnInteger As Integer
Dim integerOne As Integer, integerTwo As Integer
MsgBox TypeName(thisBecomesVariant) 'Will display "Empty"
MsgBox TypeName(thisIsAnInteger ) 'Will display "Integer"
MsgBox TypeName(integerOne ) 'Will display "Integer"
MsgBox TypeName(integerTwo ) 'Will display "Integer"
'By assigning an Integer value to a Variant it becomes Integer, too
thisBecomesVariant = 0
MsgBox TypeName(thisBecomesVariant) 'Will display "Integer"
End Sub
Two further notices on your code:
First remark: Instead of writing
'If g1val is bigger than the value in the current cell
If g1val > Cells(33, i).Value Then
g1val = g1val 'Don't change g1val
Else
g1val = Cells(33, i).Value 'Otherwise set g1val to the cell's value
End If
you could simply write
'If g1val is smaller or equal than the value in the current cell
If g1val <= Cells(33, i).Value Then
g1val = Cells(33, i).Value 'Set g1val to the cell's value
End If
Since you don't want to change g1val
in the other case.
Second remark: I encourage you to use Option Explicit when programming, to prevent typos in your program. You will then have to declare all variables and the compiler will give you a warning if a variable is unknown.