Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 340
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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



.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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



.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
redo function loss after auto-save (just installed) activates. JT Spitz Excel Discussion (Misc queries) 0 October 26th 09 05:38 PM
HOW DO I REMOVE AND UNWANTED FUNCTION IN A CELL OR GROUP OF CELLS SirCharles Excel Worksheet Functions 3 November 10th 07 04:30 AM
selection from dropdown list activates hyperlink jlind50 Excel Discussion (Misc queries) 1 June 5th 07 03:38 AM
Special Menu activates Web Toolbar BEEJAY Excel Discussion (Misc queries) 0 July 4th 06 01:40 PM
Solver activates other worksheets - why? Ken Dahlberg Excel Programming 1 September 12th 03 06:05 PM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"