none
"Run-time error:'13' Type Mismatch" for Excel 2010

    Question

  • I have been working with this code, attempting to debug it, after I have been getting a very frustrating and unexplained at least by several forums I go to. I looked up in MSDN and it says that it has been patched since XP SP3, and I am running XP SP3, so I don't understand why I am getting this error....

    Here is the code: 

    Dim PreviousValue
    Private Sub Worksheet_Change(ByVal Target As Range)
    Const LogFileName As String = "C:\Matt's Stuff\Dropbox\Excel Projects\PDI\Open Order Report\test.LOG"
    Dim FileNum As Integer
        FileNum = FreeFile ' next file number
        If Target.Value <> PreviousValue Then
        Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
        LogMessage = Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
        Print #FileNum, LogMessage ' write information at the end of the text file
        Close #FileNum ' close the file
        End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        PreviousValue = Target.Value

    The place I am getting the error is here: 

    If Target.Value <> PreviousValue Then

    I get a "Run-time error:'13' Type Mismatch" for Excel 2010, I don't understand why, if someone here can either explain to me why my code is getting this error and how to fix it, it would be greatly appreciated. 

    If people want to see what I am attempting to work on it's here: 

    Download Link

    Also, just a heads up, I am new to VBA so even though I've written this with some heavy research I don't understand VBA as well as most people here so please bare with me.  Thanks.
    • Moved by Reed KimbleMVP Tuesday, May 01, 2012 4:26 PM VBA in VB Language (From:Visual Basic Language)
    Tuesday, May 01, 2012 3:05 PM

All replies

  • Your PreviousValue variable is not initialized. Assign a value to it prior to the first use. I don't know what causes that, because by default a declared variable without a type should be considered As Variant, but still if you try to get its value in the Immediate window the same error occurs.

    Either assign an initial value to this variable or use On Error Resume Next construction and check err.Number at the following line.

    Tuesday, May 01, 2012 4:19 PM
  • This forum is for Windows Forms using VB.Net; I'll move this to the VBA forum.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, May 01, 2012 4:25 PM
  • Your PreviousValue variable is not initialized. Assign a value to it prior to the first use. I don't know what causes that, because by default a declared variable without a type should be considered As Variant, but still if you try to get its value in the Immediate window the same error occurs.

    Either assign an initial value to this variable or use On Error Resume Next construction and check err.Number at the following line.

    How? I hate to ask, but this bit of code took all my skills, and I don't actually know how to do what you are talking about, sorry...
    Tuesday, May 01, 2012 4:39 PM
  • This forum is for Windows Forms using VB.Net; I'll move this to the VBA forum.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thanks... I was told to put it here from another person.
    Tuesday, May 01, 2012 4:40 PM
  • How? I hate to ask, but this bit of code took all my skills, and I don't actually know how to do what you are talking about, sorry...

    It will probably help you, but it goes against my sense of neatness:

    If Target.Value <> PreviousValue Then
    On Error Resume Next
    If Target.Value <> PreviousValue Then
    On Error Goto 0


    Tuesday, May 01, 2012 5:21 PM
  • How? I hate to ask, but this bit of code took all my skills, and I don't actually know how to do what you are talking about, sorry...

    It will probably help you, but it goes against my sense of neatness:

    If Target.Value <> PreviousValue Then
    On Error Resume Next
    If Target.Value <> PreviousValue Then
    On Error Goto 0


    Thanks. Can I ask why? I'm learning so if I can learn correctly the first time, all the better. BTW this works perfectly :) Thanks a lot. I can't tell you how many people I had look at this code with me working on it, and no one could come up with this answer.
    • Edited by Matt Ridge Tuesday, May 01, 2012 6:55 PM
    Tuesday, May 01, 2012 6:32 PM
  • Ok, I am getting an error type 13 error now. If the cells aren't merged the script works, if the cells are merged the script doesn't work.  Is there a way to make this script work with merged cells as well as unmerged cells? 

    Option Explicit
    Dim PreviousValue
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim sLogFileName As String, nFileNum As Long, sLogMessage As String
        
        sLogFileName = ThisWorkbook.Path & "\OpenOrderLog.txt"
        
        On Error Resume Next
        If Target.Value <> PreviousValue Then
        On Error GoTo 0
            sLogMessage = Application.UserName & " changed cell " & Target.Address _
            & " from " & PreviousValue & " to " & Target.Value
            
            nFileNum = FreeFile                         ' next file number
            Open sLogFileName For Append As #nFileNum   ' create the file if it doesn't exist
            Print #nFileNum, sLogMessage                ' append information
            Close #nFileNum                             ' close the file
        End If
    End Sub
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        PreviousValue = Target.Value
    End Sub

    I tried using 

    sLogMessage = Application.UserName & " changed cell " & Target.MergeArea.Address _
            & " from " & PreviousValue & " to " & Target.Value
    but it seems to still not work. I mouse over each section and data is there, so I'm not sure why this is not working, if someone could help it would be greatly appreciated.

    • Edited by Matt Ridge Tuesday, May 01, 2012 7:39 PM adding info
    Tuesday, May 01, 2012 7:35 PM
  • Try this one

        On Error Resume Next ' Turn on error handling
        If Target.Value <> PreviousValue Then
            ' Check if we have an error
            If Err.Number = 13 Then
               PreviousValue = 0
            End If
            ' Turn off error handling
            On Error GoTo 0
    
    

    Tuesday, May 01, 2012 7:59 PM
  • Try this one

        On Error Resume Next ' Turn on error handling
        If Target.Value <> PreviousValue Then
            ' Check if we have an error
            If Err.Number = 13 Then
               PreviousValue = 0
            End If
            ' Turn off error handling
            On Error GoTo 0
    

    Just curious, what does this do that the original didn't? Also, just curious why doesn't this script work on a Mac? The path, etc is still the same...as far as I know.

    • Edited by Matt Ridge Tuesday, May 01, 2012 9:07 PM
    Tuesday, May 01, 2012 8:56 PM
  • As I said, the attempt to read the PreviousValue (just read, without doing anything else even) raised the Type mismatch error. I actually downloaded your file and checked it on my system. This behavior is strange and not documented. It appears, that if you declare a variant like this:

    Dim VariableName

    - it doesn't hold any value, and an attempt to read it causes error. As I said at the beginning - your PreviousValue was not initialized (didn't hold ANY value at all at the first call). I suggested to initialize it with anything prior to the first use.

    This is just what I did in my modification - I turned on the error handling, caught Error 13, and when error hit - I assigned zero value to your variable.

    The previous code still never assigned that initial zero to PreviousValue so the error repeated at the second attempt of using this variable.

    What concerns Mac - I don't know. I never used mac, never tested anything on mac and never intend to :)

    Wednesday, May 02, 2012 7:00 AM
  • > If the cells aren't merged the script works

    The explanation is something like rngMergedCells.value returns a 2D array of all the merged cells' values, albeit only the first element will contain a value. You can't directly 'compare' an array with another array or a variable containing a single value. You'd get a similar problem if 'Target' referred to more than a single cell, which potentially it might do if user used Ctrl-Enter or pasted over multiple cells.

    If you will only ever be interested in the active-cell use this to ensure you are only reading the active cell
    Target(1).Value. OTH you might want to check the target.count first

    It's still worth including "proper" error handling to cater for the possibility the changed cell includes an error eg #N/A. Or perhaps to give you even more control start with an extra If check (see VarType in Help)

    If VarType(PreviousValue) = VarType(Target.Value) Then

    Use of On Error Resume Next masks errors, so the code will ignore the error, fool the If check and possibly go on to do the wrong thing

    Peter Thornton

    Wednesday, May 02, 2012 12:56 PM
    Moderator
  • Ok, two questions: 

    1. How would the script look?

    2. Can you show me how the code would look so that it treats merged cells and single cells the same? (If it's even possible)


    • Edited by Matt Ridge Monday, May 07, 2012 1:25 PM
    Monday, May 07, 2012 1:25 PM
  • I suggested various things depending on your overall objective, which you haven't clarified so not sure what code to suggest for you. However to solve your immediate problem use as I suggested last time (did you try it?), eg in your original code as posted change
        If Target.Value <> PreviousValue Then
    to
       If Target(1).Value <> PreviousValue Then

    You didn't show how you originally assigned a value to PreviousValue but you'll probably also want to do something like this somewhere

    PreviousValue = Target(1).Value

    or depending on what you are doing maybe
    PreviousValue = Selection(1).Value

    This alone may not be enough to cater for multiple cells (in addition to merged cells).

    Peter Thornton

    Monday, May 07, 2012 6:31 PM
    Moderator