Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Guidance, please (xl2007)
Problem: when I run the sub, I get the message "sub or function not defined",
the problem appears to be related to the statement assessing, which is the minimum value. Cant I use the Min statement like this, or where might the trouble be? Thx very much! Code: Sub Optimize_pension_A() Dim startyear, number_yrs As Integer Dim potential, max_finansing, maks_contribution As Single h = 1 ' contant used in function i = 0 startyear = cells(78, 2).Value 'startyear number_yrs = cells(3, 10).Value - cells(6, 10).Value 'years to retirement, person A max_contribution = 45300 max_financing= cells(69, 2).Value '(budgetdeficit) For i = 0 To number_yrs If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution, (cells(60, 2 + i))) Else Exit For cells(80, 2 + i).Value = -potentiale finansing = cells(99, 2 + i).Value If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2 + i), cells(99, 2 + i)) Else Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Guidance, please (xl2007)
Hi Kragelund
To use a worksheetfunction in a macro use this syntax: Application.WorksheetFunction.Min(.... Regards, Per On 7 Dec., 23:43, Kragelund wrote: Problem: when I run the sub, I get the message "sub or function not defined", the problem appears to be related to the statement assessing, which is the minimum value. Cant I use the Min statement like this, or where might the trouble be? Thx very much! Code: Sub Optimize_pension_A() Dim startyear, number_yrs As Integer Dim potential, max_finansing, maks_contribution As Single h = 1 * * * * * * * * * * * * * * * * * * * * * * * ' contant used in function i = 0 startyear = cells(78, 2).Value * * * * * * * * * * * *'startyear number_yrs = cells(3, 10).Value - cells(6, 10).Value *'years to retirement, person A max_contribution = 45300 * * * * * * * * * * * * * * max_financing= cells(69, 2).Value * * * * * * *'(budgetdeficit) For i = 0 To number_yrs If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution, (cells(60, 2 + i))) Else Exit For cells(80, 2 + i).Value = -potentiale finansing = cells(99, 2 + i).Value If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2 + i), cells(99, 2 + i)) Else Next i End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Guidance, please (xl2007)
You need the qualifier WorksheetFunction
If cells(60, 2 + i).Value 0 Then potential = _ WorksheetFunction.Min(max_contribution, + (cells(60, 2 + i))) Else Exit For The VBA Min property is used in relation to measurement and is not a function, so you have to tell VBA when you want to use it as a function with the qualifying statement. "Kragelund" wrote: Problem: when I run the sub, I get the message "sub or function not defined", the problem appears to be related to the statement assessing, which is the minimum value. Cant I use the Min statement like this, or where might the trouble be? Thx very much! Code: Sub Optimize_pension_A() Dim startyear, number_yrs As Integer Dim potential, max_finansing, maks_contribution As Single h = 1 ' contant used in function i = 0 startyear = cells(78, 2).Value 'startyear number_yrs = cells(3, 10).Value - cells(6, 10).Value 'years to retirement, person A max_contribution = 45300 max_financing= cells(69, 2).Value '(budgetdeficit) For i = 0 To number_yrs If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution, (cells(60, 2 + i))) Else Exit For cells(80, 2 + i).Value = -potentiale finansing = cells(99, 2 + i).Value If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2 + i), cells(99, 2 + i)) Else Next i End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Guidance, please (xl2007)
Also, you should add the expression "Option Explicit" (without the quote
marks) as the first line of the code module. -- Jim Cone Portland, Oregon USA "Kragelund" wrote in message Problem: when I run the sub, I get the message "sub or function not defined", the problem appears to be related to the statement assessing, which is the minimum value. Cant I use the Min statement like this, or where might the trouble be? Thx very much! Code: Sub Optimize_pension_A() Dim startyear, number_yrs As Integer Dim potential, max_finansing, maks_contribution As Single h = 1 ' contant used in function i = 0 startyear = cells(78, 2).Value 'startyear number_yrs = cells(3, 10).Value - cells(6, 10).Value 'years to retirement, person A max_contribution = 45300 max_financing= cells(69, 2).Value '(budgetdeficit) For i = 0 To number_yrs If cells(60, 2 + i).Value 0 Then potential= Min(max_contribution, (cells(60, 2 + i))) Else Exit For cells(80, 2 + i).Value = -potentiale finansing = cells(99, 2 + i).Value If finansing <= max_finansing Then Result = Find_finansiering_A(cells(80, 2 + i), cells(99, 2 + i)) Else Next i End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Guidance, please (xl2007)
Further...
Option Explicit will highlight those areas that need attention, such as... max_contribution maks_contribution max_finansing max_financing finansing potential potentiale Result -- Jim Cone Portland, Oregon USA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Guidance, please (xl2007)
wonderful, all, now it works, thanks. Good idea to use option explicit for
future use. Kragelund "Jim Cone" wrote: Further... Option Explicit will highlight those areas that need attention, such as... max_contribution maks_contribution max_finansing max_financing finansing potential potentiale Result -- Jim Cone Portland, Oregon USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some guidance | Excel Worksheet Functions | |||
Need code or just guidance!! | Excel Programming | |||
Implementation Guidance | Excel Programming | |||
looking for logic guidance | Excel Programming | |||
Architectural guidance, please | Excel Programming |