Help with vba macro crash

**Durarara** · 09-01-2012, 02:13 PM

Hello, everyone!

I've just written a hefty piece of vba code and while it works like a charm it keeps crashing for no known by me reason. Since this is important to me and I am a complete newbie to vba I would like to ask for your help solving this problem.

The macro itself is basically a huge If/then/else cycle that checks what's the value in a cell and fills other cells, based on the value.

Since the code itself is more than 10000 characters long, I will write the last subroutine in the following post. There's a commented section I've left in on purpose, just in case.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("D14")) Is Nothing Then
	Call References
End If

If Not Intersect(Target, Range("D16")) Is Nothing Then
	Call Multiclass
	Call References
End If

If Not Intersect(Target, Range("J11")) Is Nothing Then
	If Range("J11").Value > Range("J14").Value Then
		Range("D16").Value = Range("D16").Value + 1
		Call Multiclass
		Call References
	End If
End If

Application.EnableEvents = True

End Sub

Private Sub References()

Dim LevelRef As Range
Dim ClassRef As Range
Dim NextXP As Range
Dim SkillPoints As Range
Dim Hit As Range
Dim Parry As Range
Dim Dodge As Range
Dim Initiative As Range
Dim Damage As Range
Dim APR As Range
Dim Fort As Range
Dim Will As Range
Dim Ref As Range
Dim Title As Range

If Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B4").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C4")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 53 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B5").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C5")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 44 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B6").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C6")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 75 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B7").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C7")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 97 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B8").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C8")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 119 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B9").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C9")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 141 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B10").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C10")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 163 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B11").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C11")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 229 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B12").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C12")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 295 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B13").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C13")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 185 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B14").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C14")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 207 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B15").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C15")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 229 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B16").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C16")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 251 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B17").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C17")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 273 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B18").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C18")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 295 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B19").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C19")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 317 + LevelRef.Value)

'ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B20").Value Then
'LevelRef = Worksheets("Hidden Data Sheet").Range("C20")
'ClassRef = Worksheets("Hidden Data Sheet").Range("A" & 3 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B21").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C21")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 339 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B22").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C22")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 361 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B23").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C23")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 383 + LevelRef.Value)

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B24").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C24")
	Set ClassRef = Worksheets("Class Library XP Table").Range("A" & 22 + LevelRef.Value)

End If

Set NextXP = ClassRef.OffSet(1, 1)
Set Title = ClassRef.OffSet(0, 2)
Set SkillPoints = ClassRef.OffSet(0, 3)
Set APR = ClassRef.OffSet(0, 4)
Set Fort = ClassRef.OffSet(0, 5)
Set Ref = ClassRef.OffSet(0, 6)
Set Will = ClassRef.OffSet(0, 7)
Set Hit = ClassRef.OffSet(0, 8)
Set Parry = ClassRef.OffSet(0, 9)
Set Dodge = ClassRef.OffSet(0, 10)
Set Initiative = ClassRef.OffSet(0, 11)
Set Damage = ClassRef.OffSet(0, 12)

Range("J14").Value = NextXP.Value
Range("B19").Value = Hit.Value
Range("C19").Value = Parry.Value
Range("D19").Value = Dodge.Value
Range("E19").Value = Initiative.Value
Range("F19").Value = Damage.Value
Range("G19").Value = APR.Value
Range("H19").Value = Fort.Value
Range("I19").Value = Will.Value
Range("J19").Value = Ref.Value
Range("D15").Value = Title.Value
Range("D16").Value = LevelRef.Value
Worksheets("Skills").Range("K17").Value = Worksheets("Skills").Range("K17").Value + SkillPoints.Value

End Sub

**Durarara** · 09-01-2012, 02:14 PM

Last subroutine.

I can tell the entire thing works, because it fills the correct values and a second after excel just closes itself, just like that.

Private Sub Multiclass()

Dim LevelRef As Range

Application.EnableEvents = False

If Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B4").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C4")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B5").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C5")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B6").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C6")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B7").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C7")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B8").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C8")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B9").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C9")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B10").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C10")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B11").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C11")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B12").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C12")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B13").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C13")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B14").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C14")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B15").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C15")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B16").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C16")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B17").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C17")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B18").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C18")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B19").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C19")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B20").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C20")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B21").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C21")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B22").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C22")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B23").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C23")

ElseIf Range("D14").Value = Worksheets("Hidden Data Sheet").Range("B24").Value Then
	Set LevelRef = Worksheets("Hidden Data Sheet").Range("C24")

End If

LevelRef = Range("D16").Value

Application.EnableEvents = True
End Sub

**shg** · 09-01-2012, 02:49 PM

Does the code break?

Is all the code in the sheet module? Everything except the Change event should be in a standard module.

I think Multiclass could be replaced with something like this:

Private Sub Multiclass()
    Dim rFind       As Range

    Set rFind = Worksheets("Hidden Data Sheet").Range("B4:B24").Find(What:=Range("D14").Value, _
                                                                     LookIn:=xlValues, _
                                                                     LookAt:=xlkwhole)
    If Not rFind Is Nothing Then
        Application.EnableEvents = False
        rFind.Offset(, 1).Value = Range("D16").Value
        Application.EnableEvents = True
    End If
End Sub

... and the other code could be similarly simplified.

**Durarara** · 09-02-2012, 05:46 AM

I am not sure what it means for the code to "break", but if you mean whether or not it displays some sort of error/warning, then - no, it runs flawlessly, executes to the end and then excel just closes.

Actually I've put all the code on one sheet (Sheet1) is that a problem?

And thank you, that looks much simpler, I didn't know the .find method even existed before this. :D
I shall rewrite it and see what happens.

EDIT: OK, so I've rewritten the subroutines "References" and "Multiclass", at first they both worked fine but when I basically ran the third "if" from the "Worksheet_Change" sub it began to crash every time I used any of the subs, afterwards.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("D14")) Is Nothing Then
   Call References
End If

If Not Intersect(Target, Range("D16")) Is Nothing Then
   Call Multiclass
   Call References
End If

If Not Intersect(Target, Range("J11")) Is Nothing Then
   If Range("J11").Value > Range("J14").Value Then
       Range("D16").Value = Range("D16").Value + 1
       Call Multiclass
       Call References
   End If
End If

Application.EnableEvents = True

End Sub

Private Sub References()

Dim LevelRef As Range
Dim ClassRef As Range
Dim NextXP As Range
Dim SkillPoints As Range
Dim Hit As Range
Dim Parry As Range
Dim Dodge As Range
Dim Initiative As Range
Dim Damage As Range
Dim APR As Range
Dim Fort As Range
Dim Will As Range
Dim Ref As Range
Dim Title As Range

Set LevelRef = Worksheets("Hidden Data Sheet").Range("B4:B24").Find(What:=Range("D14").Value, LookIn:=xlValues, LookAt:=xlWhole)
Set ClassRef = Worksheets("Class Library XP Table").Range("A1:B381").Find(What:=Range("D14").Value, LookIn:=xlValues, LookAt:=xlWhole)
    
If Not LevelRef Is Nothing Then
   Application.EnableEvents = False
   Set LevelRef = LevelRef.Offset(0, 1)
   Range("D16").Value = LevelRef.Value
   Application.EnableEvents = True
End If

If Not ClassRef Is Nothing Then
   Application.EnableEvents = False
   Set ClassRef = ClassRef.Offset(2 + LevelRef.Value, 0)
   Application.EnableEvents = True
End If

Set NextXP = ClassRef.Offset(1, 1)
Set Title = ClassRef.Offset(0, 2)
Set SkillPoints = ClassRef.Offset(0, 3)
Set APR = ClassRef.Offset(0, 4)
Set Fort = ClassRef.Offset(0, 5)
Set Ref = ClassRef.Offset(0, 6)
Set Will = ClassRef.Offset(0, 7)
Set Hit = ClassRef.Offset(0, 8)
Set Parry = ClassRef.Offset(0, 9)
Set Dodge = ClassRef.Offset(0, 10)
Set Initiative = ClassRef.Offset(0, 11)
Set Damage = ClassRef.Offset(0, 12)

Range("J14").Value = NextXP.Value
Range("B19").Value = Hit.Value
Range("C19").Value = Parry.Value
Range("D19").Value = Dodge.Value
Range("E19").Value = Initiative.Value
Range("F19").Value = Damage.Value
Range("G19").Value = APR.Value
Range("H19").Value = Fort.Value
Range("I19").Value = Will.Value
Range("J19").Value = Ref.Value
Range("D15").Value = Title.Value
Range("D16").Value = LevelRef.Value
Worksheets("Skills").Range("K17").Value = Worksheets("Skills").Range("K17").Value + SkillPoints.Value

End Sub

Private Sub Multiclass()
    
Dim LevelRef As Range
   
Set LevelRef = Worksheets("Hidden Data Sheet").Range("B4:B24").Find(What:=Range("D14").Value, LookIn:=xlValues, LookAt:=xlWhole)
    
If Not LevelRef Is Nothing Then
   Application.EnableEvents = False
   Set LevelRef = LevelRef.Offset(0, 1)
   LevelRef.Value = Range("D16").Value
   Application.EnableEvents = True
End If
End Sub

**shg** · 09-02-2012, 10:26 AM

Actually I've put all the code on one sheet (Sheet1) is that a problem?

Put the change event code in the sheet module and everyhing else in a standard module.

**Durarara** · 09-02-2012, 01:06 PM

Nope, does not help at all. In all three cases where the subs are executed excel crashes.

Just by the way - is it wrong to have them all in one sheet?

**shg** · 09-02-2012, 03:45 PM

When you say "crash", you mean Excel closes?

**Durarara** · 09-02-2012, 05:36 PM

Yes, I thought I have mentioned that?

I can tell the entire thing works, because it fills the correct values and a second after excel just closes itself, just like that.

I am not sure what it means for the code to "break", but if you mean whether or not it displays some sort of error/warning, then - no, it runs flawlessly, executes to the end and then excel just closes.

I am not sure if it matters, but I would like to stress that it DOES execute to the end and AFTER it closes. Although I am not sure if that's because the problem is within the end of the procedure of whether it is processor lag.

**shg** · 09-02-2012, 05:47 PM

By code breaking, I mean it stops executing and the VBE takes you to the code with the offending line highlighted (or gives you the option of doing so if you select Debug).

You say your code runs to the end; how do you know that?

You could set several breakpoints in your code, including at the last line (End Sub) of the Change event; if you don't get there, it didn't finish executing. In VBE options, you could set error handling to break on all errors.

I do note that you have a long If/ElseIf statement to set ranges, but nothing to test that they are actually set (there is no Else clause).

If you're not already familiar, now would be a good time to read http://www.cpearson.com/excel/debug.htm

**Durarara** · 09-02-2012, 06:56 PM

Oh, My God, it is so simple... it always is, isn't it?

I've just read the link you gave me, many thanks for that, and started debugging line by line and guess what? My "Reference" subroutine? I hadn't disabled "Application.EnableEvents" when I told it to change a bunch of values in the sheet with the "change event" macro.

Even though it didn't change any of the cells that the "ifs" filter, I guess it caused a memory overflow or something similar and Excel closes itself.

Hah, silly. Thank you for helping me find my error!

**shg** · 09-02-2012, 07:01 PM

You're welcome.

There is nothing, nothing that will improve your coding skill like debugging your own code.

Help with vba macro crash

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Re: Help with vba macro crash

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions