Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Excel 97 when running Excel 2000 macro
Tom, I didn't see any "missing libraries, here is the code. It i basically a routine which calls a large sub routine. The code fails i the second routine within the Do Until Loop. It fails on the line o code: X = Round(Range(Target_Address) - Target, 5) It highlights the word "Range" and says "Compile error" Sub or Functio not defined. See Below............ Sub Run_Breakevens() Dim t As Integer Dim r As Variant Range("BreakEven_Switch") = "Yes" t = 0 Calculate For Each r In Range("Breakeven_Range").Rows() t = t + 1 Do_Breakeven (t) Next r Range("BreakEven_Switch") = "No" Sheets("FSA Sensitivity Control").Select End Sub Sub Do_Breakeven(Count As Variant) Dim Sens_Address As Variant, Amount As Variant, Solve_Address A Variant Dim Target_Address As Variant, Target As Variant, Resolution A Variant Dim OldAmount As Variant, Finish As Boolean, Delta As Variant Dim X As Variant, Solution As Variant Sheets("FSA Sensitivity Control").Select Sens_Address = Range("Breakeven_Range").Cells(Count, 1).Text Amount = Range("Breakeven_Range").Cells(Count, 2).Value Solve_Address = Range("Breakeven_Range").Cells(Count, 3).Text Target_Address = Range("Breakeven_Range").Cells(Count, 4).Address Target = Range("Breakeven_Range").Cells(Count, 5).Value Resolution = Range("Breakeven_Range").Cells(Count, 6).Value OldAmount = Sheets("Input forecast").Range(Sens_Address) Sheets("Input forecast").Range(Sens_Address) = Amount OldSolve_Address = Sheets("Input forecast").Range(Solve_Address) Finish = False Delta = -1 * Resolution Do Until Finish = True Calculate X = Round(Range(Target_Address) - Target, 5) If X = 0 Then Finish = True If Abs(Delta) < 0.00000000001 And Delta * Resolution 0 The Finish = True If Delta * (Range(Target_Address).Value - Target) 0 Then Delta -Delta / 10 Sheets("Input forecast").Range(Solve_Address) = _ Sheets("Input forecast").Range(Solve_Address) + Delta Loop Solution = Sheets("Input forecast").Range(Solve_Address).Value Range("Breakeven_Results").Cells(Count, 1) = Solution Sheets("Input forecast").Range(Sens_Address) = OldAmount Sheets("Input forecast").Range(Solve_Address) = OldSolve_Address Calculate End Su -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php...nfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=26235 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error in Excel 97 when running Excel 2000 macro
Round was added in VBA 6 which was introduced in xl2000. So Excel 97
doesn't recognize it. You can use the worksheet function version X = WorksheetFunction.Round(Range(Target_Address) - Target, 5) This will work in both xl97 and xl2000 although the results might be slightly different since the worksheet function version uses a slightly different algorithm than the VBA round. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... Tom, I didn't see any "missing libraries, here is the code. It is basically a routine which calls a large sub routine. The code fails in the second routine within the Do Until Loop. It fails on the line of code: X = Round(Range(Target_Address) - Target, 5) It highlights the word "Range" and says "Compile error" Sub or Function not defined. See Below............ Sub Run_Breakevens() Dim t As Integer Dim r As Variant Range("BreakEven_Switch") = "Yes" t = 0 Calculate For Each r In Range("Breakeven_Range").Rows() t = t + 1 Do_Breakeven (t) Next r Range("BreakEven_Switch") = "No" Sheets("FSA Sensitivity Control").Select End Sub Sub Do_Breakeven(Count As Variant) Dim Sens_Address As Variant, Amount As Variant, Solve_Address As Variant Dim Target_Address As Variant, Target As Variant, Resolution As Variant Dim OldAmount As Variant, Finish As Boolean, Delta As Variant Dim X As Variant, Solution As Variant Sheets("FSA Sensitivity Control").Select Sens_Address = Range("Breakeven_Range").Cells(Count, 1).Text Amount = Range("Breakeven_Range").Cells(Count, 2).Value Solve_Address = Range("Breakeven_Range").Cells(Count, 3).Text Target_Address = Range("Breakeven_Range").Cells(Count, 4).Address Target = Range("Breakeven_Range").Cells(Count, 5).Value Resolution = Range("Breakeven_Range").Cells(Count, 6).Value OldAmount = Sheets("Input forecast").Range(Sens_Address) Sheets("Input forecast").Range(Sens_Address) = Amount OldSolve_Address = Sheets("Input forecast").Range(Solve_Address) Finish = False Delta = -1 * Resolution Do Until Finish = True Calculate X = Round(Range(Target_Address) - Target, 5) If X = 0 Then Finish = True If Abs(Delta) < 0.00000000001 And Delta * Resolution 0 Then Finish = True If Delta * (Range(Target_Address).Value - Target) 0 Then Delta = -Delta / 10 Sheets("Input forecast").Range(Solve_Address) = _ Sheets("Input forecast").Range(Solve_Address) + Delta Loop Solution = Sheets("Input forecast").Range(Solve_Address).Value Range("Breakeven_Results").Cells(Count, 1) = Solution Sheets("Input forecast").Range(Sens_Address) = OldAmount Sheets("Input forecast").Range(Solve_Address) = OldSolve_Address Calculate End Sub -- ExcelMonkey ------------------------------------------------------------------------ ExcelMonkey's Profile: http://www.excelforum.com/member.php...fo&userid=5221 View this thread: http://www.excelforum.com/showthread...hreadid=262358 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro error after switching from Excel 2000 to Excel 2003 | Excel Discussion (Misc queries) | |||
Error in Excel 97 when running Excel 2000 macro | Excel Programming | |||
Error when running macro in Excel | Excel Programming | |||
Excel 2000 macro error | Excel Programming | |||
KERNEL32.DLL error after running EXCEL Macro | Excel Programming |