VB activates unwanted function
 

Problem with VB going outside the Sub to do other things.
The following is part of an risk analysis, when VB
hits Sensitivity.Range("Sens_area").Clear it
goes to a module that have lots of functions that has
nothing to do with the risk calculations. I have tried
Application.EnableEvents = False and then turning it on
after without success.

Using Application.Calculation = xlCalculationManual works
until I need to start the calculations again
Application.Calculation = xlCalculationAutomatic then it
does the same thing it goes to the module with the
functions.

How can you stop VB to go outside the sub during its
execution?

Thanks,

Urban


Private Sub RiskButton()

Application.ScreenUpdating = False

' Starts risk calculations when risk button is pressed.


'This part calculates different scenario that have
different values of the 3 parameters
'(IRR, Year, NPV) to perform the Risk analysis

Sensitivity.Unprotect Password:=PWD
Sensitivity.Range("Sens_area").Clear
Sensitivity.Range("Sens_RiskNum").Clear

Sensitivity.Range("VarTab").EntireRow.Hidden =
False

VB activates unwanted function
 

Urban, serveral things to try:

first, is there an on error trap that is active that is taking control at
the line in question? The easy way to find out is to put two lines right
above the line in question:

dim anyV as integer
V = 1/0

If an error trap is present, the above will trigger it. Put a break point
on the V =1/0 and see what happens.

I see that you have renamed your sheet module names. Try instead to use
Sheets("sheetname"). I have had mixed results with renamed sheet modules.
Also, confirm that the sheet does in fact exists.

Unload all un-needed add-ins.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Urban" wrote in message
...
Problem with VB going outside the Sub to do other things.
The following is part of an risk analysis, when VB
hits Sensitivity.Range("Sens_area").Clear it
goes to a module that have lots of functions that has
nothing to do with the risk calculations. I have tried
Application.EnableEvents = False and then turning it on
after without success.

Using Application.Calculation = xlCalculationManual works
until I need to start the calculations again
Application.Calculation = xlCalculationAutomatic then it
does the same thing it goes to the module with the
functions.

How can you stop VB to go outside the sub during its
execution?

Thanks,

Urban


Private Sub RiskButton()

Application.ScreenUpdating = False

' Starts risk calculations when risk button is pressed.


'This part calculates different scenario that have
different values of the 3 parameters
'(IRR, Year, NPV) to perform the Risk analysis

Sensitivity.Unprotect Password:=PWD
Sensitivity.Range("Sens_area").Clear
Sensitivity.Range("Sens_RiskNum").Clear

Sensitivity.Range("VarTab").EntireRow.Hidden =
False

VB activates unwanted function
 

Hi Bob,

thanks, at V=1/0 I get runtime error Division by zero.
What do I do next?

Urban



-----Original Message-----
Urban, serveral things to try:

first, is there an on error trap that is active that is
taking control at
the line in question? The easy way to find out is to put
two lines right
above the line in question:

dim anyV as integer
V = 1/0

If an error trap is present, the above will trigger it.
Put a break point
on the V =1/0 and see what happens.

I see that you have renamed your sheet module names. Try
instead to use
Sheets("sheetname"). I have had mixed results with
renamed sheet modules.
Also, confirm that the sheet does in fact exists.

Unload all un-needed add-ins.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros
for Excel

"Urban" wrote in
message
...
Problem with VB going outside the Sub to do other
things.
The following is part of an risk analysis, when VB
hits Sensitivity.Range("Sens_area").Clear it
goes to a module that have lots of functions that has
nothing to do with the risk calculations. I have tried
Application.EnableEvents = False and then turning it on
after without success.

Using Application.Calculation = xlCalculationManual
works
until I need to start the calculations again
Application.Calculation = xlCalculationAutomatic then it
does the same thing it goes to the module with the
functions.

How can you stop VB to go outside the sub during its
execution?

Thanks,

Urban


Private Sub RiskButton()

Application.ScreenUpdating = False

' Starts risk calculations when risk button is pressed.


'This part calculates different scenario that have
different values of the 3 parameters
'(IRR, Year, NPV) to perform the Risk analysis

Sensitivity.Unprotect Password:=PWD
Sensitivity.Range("Sens_area").Clear
Sensitivity.Range("Sens_RiskNum").Clear

Sensitivity.Range("VarTab").EntireRow.Hidden =
False



.

VB activates unwanted function
 

I found a fix. It appears that the problem was connected
to the size of the spreadsheet. I changed some formulas to
values and now everything works fine again.


Urban



-----Original Message-----
Urban, serveral things to try:

first, is there an on error trap that is active that is
taking control at
the line in question? The easy way to find out is to put
two lines right
above the line in question:

dim anyV as integer
V = 1/0

If an error trap is present, the above will trigger it.
Put a break point
on the V =1/0 and see what happens.

I see that you have renamed your sheet module names. Try
instead to use
Sheets("sheetname"). I have had mixed results with
renamed sheet modules.
Also, confirm that the sheet does in fact exists.

Unload all un-needed add-ins.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros
for Excel

"Urban" wrote in
message
...
Problem with VB going outside the Sub to do other
things.
The following is part of an risk analysis, when VB
hits Sensitivity.Range("Sens_area").Clear it
goes to a module that have lots of functions that has
nothing to do with the risk calculations. I have tried
Application.EnableEvents = False and then turning it on
after without success.

Using Application.Calculation = xlCalculationManual
works
until I need to start the calculations again
Application.Calculation = xlCalculationAutomatic then it
does the same thing it goes to the module with the
functions.

How can you stop VB to go outside the sub during its
execution?

Thanks,

Urban


Private Sub RiskButton()

Application.ScreenUpdating = False

' Starts risk calculations when risk button is pressed.


'This part calculates different scenario that have
different values of the 3 parameters
'(IRR, Year, NPV) to perform the Risk analysis

Sensitivity.Unprotect Password:=PWD
Sensitivity.Range("Sens_area").Clear
Sensitivity.Range("Sens_RiskNum").Clear

Sensitivity.Range("VarTab").EntireRow.Hidden =
False



.