F
Frank & Pam Hayes
I am a relative newbie in VBA. I have created a relatively complicated
Excel application that sets takes a series of variables in one file, passes
them one at a time to a second file, recalculates the second file, and then
passes a calculated field from the second file back to the first, where it
is written out. This is basically doing what-if analysis of Net Present
Values under a number of different uncertainties.
The VBA code utilizes Public Variables, Dim Objects, Integers, Strings, and
Arrays as well as If statements, Do loops, and Cases. There is a great deal
of iterative looping.
My problem is that somewhere along the line I made a change that is causing
Excel to crash with the message "Microsoft Excel has encountered a problem
and needs to close". This is in Office 2000 on Windows XP. I have also
worked on this on a Office 2000 laptop running Windows 2000, and in that
case Excel crashes with a memory error.
This occurs in a couple of different circumstances including:
1. When I try to edit the Declarations section between "Option Explicit"
and my first sub-routine. This is reproduced below in case I have defined a
variable incorrectly.
2. When I try to compile the VBA code.
3. When I run the VBA code and it fails, I choose Debug and am given a
message that a variable is not defined. I close the window and I am taken
to the applicable section of VBA code. If I stop the debugger to change
something, it crashes.
4. Interestingly, the code runs fine through the majority of the
programming until it stops in one of the very last steps.
I feel that something I am doing with a variable is causing me a conflict,
since it crashes on a compile, but I can not figure out what I can do to fix
it (since I can not edit my variable section).
Here are my variables:
Option Explicit
Public ModelWB As Object
Public a As Object
Public b As Object
Public c As Object
Public d As Object
Dim RB_Number_of_Uncertainties As Object
Dim x As Integer
Dim Y As Integer
Dim Z As Integer
Dim PrintRow As Integer
Dim PrintCol As Integer
Dim TotalRows As Integer
Dim CurColumn As Integer
Dim CurCell As Object
Dim RangeCount As Integer
Dim rng As Integer
Dim Total_Branches As Integer
Dim Total_Variance As Double
Dim Tree_Variables As Integer
Dim Group_Size As Integer
Dim Cumulative_Variance As Single
Dim Prob_Val1 As Single
Dim Prob_Val2 As Single
Dim Prob_Val3 As Single
Dim Prob_Val4 As Single
Dim Prob_Val5 As Single
Dim Prob_Val6 As Single
Dim Prob_Val7 As Single
Dim Prob_Val8 As Single
Dim Prob_Array(1 To 8)
Dim uName As String
Dim DecisionCriteriaName As String
Dim RB_Value As Single
Any help or pointing in the right direction would be appreciated.
Thanks,
Frank Hayes
(e-mail address removed)
Excel application that sets takes a series of variables in one file, passes
them one at a time to a second file, recalculates the second file, and then
passes a calculated field from the second file back to the first, where it
is written out. This is basically doing what-if analysis of Net Present
Values under a number of different uncertainties.
The VBA code utilizes Public Variables, Dim Objects, Integers, Strings, and
Arrays as well as If statements, Do loops, and Cases. There is a great deal
of iterative looping.
My problem is that somewhere along the line I made a change that is causing
Excel to crash with the message "Microsoft Excel has encountered a problem
and needs to close". This is in Office 2000 on Windows XP. I have also
worked on this on a Office 2000 laptop running Windows 2000, and in that
case Excel crashes with a memory error.
This occurs in a couple of different circumstances including:
1. When I try to edit the Declarations section between "Option Explicit"
and my first sub-routine. This is reproduced below in case I have defined a
variable incorrectly.
2. When I try to compile the VBA code.
3. When I run the VBA code and it fails, I choose Debug and am given a
message that a variable is not defined. I close the window and I am taken
to the applicable section of VBA code. If I stop the debugger to change
something, it crashes.
4. Interestingly, the code runs fine through the majority of the
programming until it stops in one of the very last steps.
I feel that something I am doing with a variable is causing me a conflict,
since it crashes on a compile, but I can not figure out what I can do to fix
it (since I can not edit my variable section).
Here are my variables:
Option Explicit
Public ModelWB As Object
Public a As Object
Public b As Object
Public c As Object
Public d As Object
Dim RB_Number_of_Uncertainties As Object
Dim x As Integer
Dim Y As Integer
Dim Z As Integer
Dim PrintRow As Integer
Dim PrintCol As Integer
Dim TotalRows As Integer
Dim CurColumn As Integer
Dim CurCell As Object
Dim RangeCount As Integer
Dim rng As Integer
Dim Total_Branches As Integer
Dim Total_Variance As Double
Dim Tree_Variables As Integer
Dim Group_Size As Integer
Dim Cumulative_Variance As Single
Dim Prob_Val1 As Single
Dim Prob_Val2 As Single
Dim Prob_Val3 As Single
Dim Prob_Val4 As Single
Dim Prob_Val5 As Single
Dim Prob_Val6 As Single
Dim Prob_Val7 As Single
Dim Prob_Val8 As Single
Dim Prob_Array(1 To 8)
Dim uName As String
Dim DecisionCriteriaName As String
Dim RB_Value As Single
Any help or pointing in the right direction would be appreciated.
Thanks,
Frank Hayes
(e-mail address removed)