Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
redo function loss after auto-save (just installed) activates. | Excel Discussion (Misc queries) | |||
HOW DO I REMOVE AND UNWANTED FUNCTION IN A CELL OR GROUP OF CELLS | Excel Worksheet Functions | |||
selection from dropdown list activates hyperlink | Excel Discussion (Misc queries) | |||
Special Menu activates Web Toolbar | Excel Discussion (Misc queries) | |||
Solver activates other worksheets - why? | Excel Programming |