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>

 

[jblevins]

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.

put text in any cell then goto VBA and define a variable as an integer and then try to load the cell value into the variable. You cannot put a decimal point in a cell if it is formatted for General.

A1 = something

Dim v as integer
v = Range("A1")

 

[MARK858]

put text in any cell then

That is because it formats as text when you enter text and so it is text before you enter the number.
Defining it as Integer doesn't work because it is already text.
And if it was General then you could put in a decimal point as it is the default for a cell.

 

[jblevins]

Here is the code that I came up with, range will have to be modified to fit individual's needs. The only thing that does not work is dragging to copy cells. Any undo has to be done before making another change, it will not compile them but is satisfactory for my needs.

Worksheet code:

Option Explicit

Private Sub Worksheet_Activate()
    nc = 0
    OldData = Range(Cells(1, 1), Cells(5, 5))
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If nc = True Then Exit Sub
    OldData = Newdata   ' Save unchanged data.
    UndoAddress = Selection.Address
    Application.OnUndo "Undo Change", "UndoChange"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Newdata = Range(Cells(1, 1), Cells(5, 5))   ' Save current data.
End Sub

Module code:

Option Explicit

Public OldData() As Variant
Public Newdata() As Variant
Public UndoAddress As String
Public nc As Boolean

Sub UndoChange()
Dim c As Integer
Dim r As Integer
    
    nc = True  ' Prevent recursive calls to Worksheet_Change.
    Range(UndoAddress).Select
    For r = 1 To 5
        For c = 1 To 5
            Cells(r, c) = OldData(r, c)
        Next c
    Next r
    nc = False
End Sub