Run Time error 13

July 27, 2017 at 19:36:36
Specs: Windows 7
Hi,

I just wondering why there is run time error 13 in this coding? however is still working.,


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$L$29" And Target <> Chr(yes) Then
Range("O29,Q29,S29") = ""
End If

End Sub

Thanks

message edited by fdecmc7


See More: Run Time error 13

Reply ↓  Report •

#1
July 28, 2017 at 01:28:40
Not sure what you were trying to do with

Chr(yes)


I think your code should be something like

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" And Target <> "yes" Then
        Range("B10,C10,D10") = vbNullString
    End If

End Sub


Reply ↓  Report •

#2
July 28, 2017 at 01:49:15
Thank you for that.. Sorry Im still new in VB.. Lot of things need to understand.. actually I had tried your code but still showing the same results..

Reply ↓  Report •

#3
July 28, 2017 at 02:27:51
When the error occurs it will ask you to debug the code, when you press debug, it will highlight a line of code in yellow, can you post that line so we can see where the code is failing, it works perfectly fine for me so I am assuming it may be something on your worksheet

Reply ↓  Report •

Related Solutions

#4
July 28, 2017 at 02:31:05
If Target.Address = "$L$29" And Target <> "yes" Then

This line been highlighted


Reply ↓  Report •

#5
July 28, 2017 at 02:40:18
its working on the target cell. However when I tried to copy something from other cell and paste it somewhere on the sheet, then the dialog of that error pop up.

Reply ↓  Report •

#6
July 28, 2017 at 04:17:40
When the error occurs, hit debug, copy and paste the highlighted line here.

Reply ↓  Report •

#7
July 28, 2017 at 05:36:23
Runtime error 13 = Type mismatch. You should probably break up your conditions into their own If statements.
If Target.Address = "$L$29" Then 
  If Target <> "yes" Then
    Range("B10,C10,D10") = ""
  End If
End If

How To Ask Questions The Smart Way


Reply ↓  Report •

#8
July 28, 2017 at 06:47:57
re: "when I tried to copy something from other cell and paste it somewhere on the sheet, then the dialog of that error pop up."

If I were to guess that you are copying more than one cell at a time, would I be right?

The reason you need to evaluate the If Target <> "yes" as a separate instruction is because of how VBA handles the value of a Target.

When the Target is a single cell, the value of the Target is the content of that single cell.

When the Target is a range of cells e.g. L29:L31, the value of the Target can't be determined, so VBA throws a Type Mismatch error.

By testing for Target.Address first (and alone) the value of the Target will never be evaluated when the Target.Address is a range of cells, so you don't get the error.

Another way to handle the the situation is shown below. The point is that in both Razor2.3's code and the following code is that Target <> "yes" is not evaluated when the Target is a range of cells, so the error doesn't happen.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Cells.Count = 1 Then
    If Target.Address = "$L$29" And Target <> "yes" Then
       Range("B10,C10,D10") = ""
    End If
   End If
End Sub

That takes care of your immediate issue. I have something else to add regarding Worksheet_Change macros, but I don't have time to do it now. Stay tuned!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#9
July 28, 2017 at 09:09:55
I would like to point out something related to Worksheet_Change macros.

I don’t know if you are aware of this or not, but your Worksheet_Change macro is going to run after every change to the worksheet, not just a change to L29. e.g If you change A1, the code is going to fire and check to see if the Target was L29. Obviously it is not going clear B10:D10 since the Target wasn't L29, but the code still ran. You may wonder why this is important, considering that even though the code runs with every change, it’s not going to do anything except when the If’s are TRUE. The reason it is important is because of this:

When the Target is L29 and <> “yes”, the code is going to clear B10:D10. Since the clearing of the cells is a change to the sheet, the code is going to fire again, even though it never finished running the first time. If you use F8 to single step through the code, you can watch it "restart" as soon as B10:D10 is changed. It will then check the Target address again but since the Target is now B10:D10, the L29 If will be FALSE and the code will exit.

Now, in this instance it doesn't really matter since the change is minimal, but in some instances a Worksheet_Change macro that is firing with every change that the macro itself is making to the sheet can send the code into an endless loop. It basically ends up “nesting” each run within the previous one and then bounces back out to complete its predecessor. At a minimum, it makes the code less efficient. Worst case is that code gets so wrapped up inside itself that it can’t find its way out and you end up with a fatal error, a messed up worksheet and possibly a crashed application.

This situation can be solved by disabling Events before the code makes any changes and then enabling it again after all of the changes are done:

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$L$29" Then
     If Target <> "yes" Then
      Application.EnableEvents = False  '<-------
        Range("B10,C10,D10") = ""
      Application.EnableEvents = True    '<-------
     End If
    End If
End Sub 

However, there is a danger associated with this technique. Disabling Events is an Application level setting. If the code crashes or exits after the Application.EnableEvents = False instruction is executed but before the Application.EnableEvents = True instruction is executed, Events will disabled for the entire Excel application. All worksheets, all workbooks. No Event macros across the entire Application will run until Events are manually enabled again. Obviously this is an issue, especially if you aren’t aware/forget that Events are disabled.

There are (at least) 3 ways to enable Events outside of the code that caused them to be disabled:

1 - Brute Force:
- Shut down Excel and restart it. Events are automatically enabled with each start of the application.

2 - A macro (possibly attached to a button for ease of access):

 
Sub MyEventEnabler()
      Application.EnableEvents = True
End Sub 

3 – The Immediate Window in the VBA Editor:
- Any instruction entered directly in the Immediate Window will run, well, immediately.
- If you put Application.EnableEvents = True in the immediate window and press Enter, Events will be enabled. At any time that you need to (or want to), you can simply place the cursor anywhere on that line and press Enter. This is great for those times when you are testing new code and Events keep getting disabled by failing code or code that you abort early.

Finally, probably the best method to avoiding the situation altogether is to include an Error handling routine in your code. The following version of the macro you are using does just that. You will see that the Application.EnableEvents = True instruction will run regardless of whether the code exits cleanly or if the code throws up an error.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo macro_exit:
    If Target.Address = "$L$29" Then
     If Target <> "yes" Then
      Application.EnableEvents = False
        Range("B10,C10,D10") = ""
     End If
    End If
macro_exit:
      Application.EnableEvents = True
End Sub 

I hope this helps. If you have any questions, just ask.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#10
July 30, 2017 at 18:07:53
Thanks very much for the insights.. Its help me very much.. still lot to learn.. This forum is very helpful :))))

Reply ↓  Report •

#11
July 30, 2017 at 18:45:02
Someone should point out that if you're ever On Erroring, you'll never know if your macro encountered a problem unless your code explicitly checks the Err object. Otherwise, you're left scratching your head, wondering why your code seemingly just stops sometimes.

How To Ask Questions The Smart Way


Reply ↓  Report •

#12
July 31, 2017 at 06:52:00
Razor2.3:

From an absolute, 100% bullet-proof/user-proof perspective, I agree with you. In fact, we could even take it a step further and include error logging, etc. ;-) However, in a real-world, practical sense I don't know if we have to go that far in every instance of On Erroring.

In the case of the (current) requirements in this thread, I'm not sure that there is any error that could occur that would stop the code in an unexpected manner. Yes, I can cause a run-time error which would essentially be "hidden" by not checking the Err object (e.g. If Target.Address = "$L$2q" Then) but that problem would have been discovered during testing.

I think it's a matter of balance between what is needed in each particular case and "perfect code".

Once again, I do agree that checking the Err object is a good practice to be aware of and there are certainly times when it must be used.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#13
July 31, 2017 at 09:40:29
Well yes, but I've seen people use On Error before initial debugging because, "It was the correct thing to do." Since we're now talking generalities and not the thread's specific case, then we should point out the associated cost, and that the cost is most likely to appear after you think you're done with the project.

I once had a person who tried to argue with me that her macro was fine because it didn't throw an error dialog, while she ignored that she asked me to help her figure out why the macro was doing the wrong thing. (I think it was a case of On Error Resume Next without any sanity checks.)

I'm not saying the OP will make the same mistakes, or that he shouldn't read up on VBA error handling on his own, but we'd be remiss to not warn newbies that any thrown up error won't be tossed towards you.

If you're looking for boilerplate code, I suppose you could do something like this, but error handling isn't exactly conducive to boilerplate.

macroExit:
  Application.EnableEvents = True
  If Err Then
    MsgBox "Error: " & Err & vbNewLine & vbNewLine & _
     Err.Description, vbExclamation, "VBA Error"
    End    'Stop & Resume is also an option here
  End If

How To Ask Questions The Smart Way

message edited by Razor2.3


Reply ↓  Report •

#14
July 31, 2017 at 10:23:51
re: "Well yes, but I've seen people use On Error before initial debugging because, "It was the correct thing to do.""

I hope you pointed out that it wasn't the correct thing to do. ;-)

re: "...or that he shouldn't read up on VBA error handling on his own"

I agree. The OP (and others) might enjoy this...

https://excelmacromastery.com/vba-e...

re: "If you're looking for boilerplate code...but error handling isn't exactly conducive to boilerplate."

Once again, I agree, but...

Your example would work well for the author but I don't think I'd want my users seeing a "VBA Error" message pop up on their screen. Hopefully, there will always be an error handling routine that not only handles the error properly but also "hides" it from the user. (We can always hope that that's the case, anyway.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#15
July 31, 2017 at 17:11:07
Now we're getting political here, especially since we both acknowledge the decision should be handled case-by-case, and then immediately talk in generalities.

I would just say we have a different starting point. I'm part of the "fail fast, fail hard" crowd. If my code isn't meeting my assumptions, I want it to stop as soon as possible, before it does something potentially destructive. Then I can work on getting everything aligned again. To that end, my boilerplate is about as close to the default error handler as I can get, while also getting Excel back into a usable state. (I usually pair that code up with Application.ScreenUpdating.)

You seem to be a part of the "keep things going" movement. You expect your code to accept a few lumps and give it a best effort. That's fine, so long as you take the proper precautions when they're required. Again, not saying you wouldn't, nor that we both wouldn't end up with similar error handlers for a specific situation, just that you want to make your code bulletproof, and I want to see where the bullet holes are.

How To Ask Questions The Smart Way


Reply ↓  Report •

Ask Question