Declaration type getting changed

[jblevins]

All cells are formatted as "General". Range(Cells(1, 1), Cells(5, 5)) all contain a 2. Getting type mismatch error - see "Watch" at bottom.

Declared in a module:

Option Explicit 

Public OldData As Variant
Public Newdata As Variant
Public TmpData As Variant

Worksheet:

Option Explicit Private Sub Worksheet_Activate()
    OldData = Range(Cells(1, 1), Cells(5, 5))
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    TmpData = Range(Cells(1, 1), Cells(5, 5))
    If OldData = TmpData Then Newdata = Range(Cells(1, 1), Cells(5, 5))
End Sub

Watch return:

Watch :   : OldData = TmpData : <type mismatch=""> : Variant/Integer : Sheet1.Worksheet_SelectionChange

How is TmpData getting changed from a variant to an integer?
</type>

 

[Joe4]

Are you trying to set your variables equal to a value, or to a range?

If to a range, declare the variables as "Range" and use the "Set" command, i.e.

Public OldData As Range
...

Set TmpData = Range(Cells(1, 1), Cells(5, 5))

If you are trying to set it equal to a value, you cannot do it the way you have written, as a value cannot be equal to value in more than one cell at a time (unless you are using an array)!

What exactly is it that you are trying to do?

 

[jblevins]

Just trying to check and see if data in a range of cells has changed. The goal is to overcome the killing of the undo stack by Worksheet_Changed. I know that there is problems with trying to do this and have an example with a recommended class module that does not work. I just have a few cells that I want to check; currently, I can only undo a single cell and want to do several.

Tried your example and still got the same result.

 

[Joe4]

I think you would need to use arrays, and look through each cell in the range and assign it to the array variable.
Then to compare them, you would need to loop through each array variable to compare it to the corresponding one in the other.

The goal is to overcome the killing of the undo stack by Worksheet_Changed. I know that there is problems with trying to do this and have an example with a recommended class module that does not work.

Yes, that is a tricky one.

 

[jblevins]

I am considering the array approach, but would like to know why a variant is changing to an integer - I am sure that it is not a MS bug... To the bigger point, I wish they would fix the undo problem, just changing a cell value should not kill the undo stack.

Thanks Joe4

 

[Jonmo1]

It's by design. It's what variant does.
Basically, it's you telling VBA that you don't know what type of data will be assigned to the variable (it could be text, could be an integer, could be boolean)
You're letting VBA decide the datatype.
VBA will choose the appropriate type based on the value you assign to the variable.

 

[jblevins]

I have changed the cell format and variable format to both integer and I still get the same error. I have tried leaving the cell format as general and changing the variable to integer. I have tried entering text into the cells and changing the variable to different settings and always the same error.

 

[MARK858]

Why would the format of the cell affect the variable type

 

[jblevins]

If you have text in a cell, you can't load that into an integer variable - type mismatch.

 

[MARK858]

Changing the format only affects the appearance of a cell , it's underlying value therefore it's type remains the same.
If you format a cell as text before you put anything in the cell then it's contents will be text.