Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space error
Hi folks! VB dummy here... :)
As the subject line says, I get this error almost every time I make changes involving column D4-D13 (the first IF statement) Can anyone offer some advice? Thanks! Craig ~~~snip~~~ Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("D4:D13")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, "D"), Cells(13, "D")).Value = Target Application.EnableEvents = True End If If Not Intersect(Target, Range("e4:e13")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, "e"), Cells(13, "e")).Value = Target Application.EnableEvents = True End If If Target.HasFormula = False Then Target.Value = UCase(Target.Value) End If Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C4")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space error
Maybe around these lines:
--- If Target.HasFormula = False Then Target.Value = UCase(Target.Value) End If --- also put the enableevents off/on: --- If Target.HasFormula = False Then Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End If --- "TheMilkGuy" wrote in message ... Hi folks! VB dummy here... :) As the subject line says, I get this error almost every time I make changes involving column D4-D13 (the first IF statement) Can anyone offer some advice? Thanks! Craig ~~~snip~~~ Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("D4:D13")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, "D"), Cells(13, "D")).Value = Target Application.EnableEvents = True End If If Not Intersect(Target, Range("e4:e13")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, "e"), Cells(13, "e")).Value = Target Application.EnableEvents = True End If If Target.HasFormula = False Then Target.Value = UCase(Target.Value) End If Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C4")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Out of Stack Space error
Not sure if it is intended but you have a recursive call here (left events
enabled) If Target.HasFormula = False Then Target.Value = UCase(Target.Value) End If Out of stack space means that you have overflowed the memory usually becuase of a recursive call or never ending loop. Becuase it is stack we are looking for global or static variables (heap would indicate local variables)... -- HTH... Jim Thomlinson "TheMilkGuy" wrote: Hi folks! VB dummy here... :) As the subject line says, I get this error almost every time I make changes involving column D4-D13 (the first IF statement) Can anyone offer some advice? Thanks! Craig ~~~snip~~~ Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("D4:D13")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, "D"), Cells(13, "D")).Value = Target Application.EnableEvents = True End If If Not Intersect(Target, Range("e4:e13")) Is Nothing Then Application.EnableEvents = False Range(Cells(Target.Row, "e"), Cells(13, "e")).Value = Target Application.EnableEvents = True End If If Target.HasFormula = False Then Target.Value = UCase(Target.Value) End If Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("C4")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Out of Stack space - run time error 28 | Excel Discussion (Misc queries) | |||
"Out of Stack Space" Macro Error | Excel Discussion (Misc queries) | |||
Avoiding Value error message due to space in cell w/ formula | Excel Discussion (Misc queries) | |||
Exceeded font space error message | Excel Discussion (Misc queries) | |||
Paper Space / Model Space ? | Excel Discussion (Misc queries) |