Creating a new function
 

I'm trying to create a function to help determine rates.

There are four cells to be examined, and based on the selection of the
four cells, it will spit out a value in another cell.
The first cell, choice is Single or Family
second, third and fourth cell, all have a choice of A, B or C.

The problem is what I plunk this function into a cell in the
spreadsheet, I get a "#VALUE" error. I'm VERY new to VB, and I'm
hoping someone out there could help me out. Any help would be greatly
appreciated.

Here's what I have:

Function Benefits(Selection, Health, Dental, Drug As String)
Dim TotalHealth, TotalDental, TotalDrug As Integer
Dim SHealthA, SHealthB, SHealthC As Integer
Dim SDentalA, SDentalB, SDentalC As Integer
Dim SDrugA, SDrugB, SDrugC As Integer
Dim FSHealthA, FHealthB, FHealthC As Integer
Dim FSDentalA, FDentalB, FDentalC As Integer
Dim FSDrugA, FDrugB, FDrugC As Integer
Dim Benefit As Integer

'Rate Table (Single)
SHealthA = "insert rates here"
SHealthB = "insert rates here"
SHealthC = "insert rates here"
'etc., as dim'd above.

'Rate Table (Family)
FHealthA = "insert rates here"
FHealthB = "insert rates here"
FHealthC = "insert rates here"
'etc., as dim'd above.

'Single
If Selection = "Single" Then
'Health Selection
If Health = "A" Then
TotalHealth = SHealthA
ElseIf Health = "B" Then
TotalHealth = SHealthB
Else
TotalHealth = SHealthC
End If
'Dental Selection
If Dental = "A" Then
TotalDental = SDentalA
ElseIf Dental = "B" Then
TotalDental = SDentalB
Else
TotalDental = SDentalC
End If
'Drug Selection
If Drug = "A" Then
TotalDrug = SDrugA
ElseIf Drug = "B" Then
TotalDrug = SDrugB
Else
TotalDrug = SDrugC
End If

'Family
Else
'Health Selection
If Health = "A" Then
TotalHealth = FHealthA
ElseIf Health = "B" Then
TotalHealth = FHealthB
Else
TotalHealth = FHealthC
End If
'Dental Selection
If Dental = "A" Then
TotalDental = FDentalA
ElseIf Dental = "B" Then
TotalDental = FDentalB
Else
TotalDental = FDentalC
End If
'Drug Selection
If Drug = "A" Then
TotalDrug = FDrugA
ElseIf Drug = "B" Then
TotalDrug = FDrugB
Else
TotalDrug = FDrugC
End If

End If
'Calculation
Benefit = TotalHealth + TotalDrug + TotalDental
ActiveCell.Value = Benefit
End Function

Creating a new function
 

Kirk,

Your function is called "Benefits", but you are returning the value to
"Benefit".
Also, remove the line "ActiveCell.Value = Benefit".

Assuming "Benefit" is the choosen function name, in the cell you want your
answer, enter "=Benefit(~cell address for Selection~,~cell address for
Health~,~cell address for Dental~,~cell address for Drug~)"

Dale Preuss



"Kirk L." wrote:

I'm trying to create a function to help determine rates.

There are four cells to be examined, and based on the selection of the
four cells, it will spit out a value in another cell.
The first cell, choice is Single or Family
second, third and fourth cell, all have a choice of A, B or C.

The problem is what I plunk this function into a cell in the
spreadsheet, I get a "#VALUE" error. I'm VERY new to VB, and I'm
hoping someone out there could help me out. Any help would be greatly
appreciated.

Here's what I have:

Function Benefits(Selection, Health, Dental, Drug As String)
Dim TotalHealth, TotalDental, TotalDrug As Integer
Dim SHealthA, SHealthB, SHealthC As Integer
Dim SDentalA, SDentalB, SDentalC As Integer
Dim SDrugA, SDrugB, SDrugC As Integer
Dim FSHealthA, FHealthB, FHealthC As Integer
Dim FSDentalA, FDentalB, FDentalC As Integer
Dim FSDrugA, FDrugB, FDrugC As Integer
Dim Benefit As Integer

'Rate Table (Single)
SHealthA = "insert rates here"
SHealthB = "insert rates here"
SHealthC = "insert rates here"
'etc., as dim'd above.

'Rate Table (Family)
FHealthA = "insert rates here"
FHealthB = "insert rates here"
FHealthC = "insert rates here"
'etc., as dim'd above.

'Single
If Selection = "Single" Then
'Health Selection
If Health = "A" Then
TotalHealth = SHealthA
ElseIf Health = "B" Then
TotalHealth = SHealthB
Else
TotalHealth = SHealthC
End If
'Dental Selection
If Dental = "A" Then
TotalDental = SDentalA
ElseIf Dental = "B" Then
TotalDental = SDentalB
Else
TotalDental = SDentalC
End If
'Drug Selection
If Drug = "A" Then
TotalDrug = SDrugA
ElseIf Drug = "B" Then
TotalDrug = SDrugB
Else
TotalDrug = SDrugC
End If

'Family
Else
'Health Selection
If Health = "A" Then
TotalHealth = FHealthA
ElseIf Health = "B" Then
TotalHealth = FHealthB
Else
TotalHealth = FHealthC
End If
'Dental Selection
If Dental = "A" Then
TotalDental = FDentalA
ElseIf Dental = "B" Then
TotalDental = FDentalB
Else
TotalDental = FDentalC
End If
'Drug Selection
If Drug = "A" Then
TotalDrug = FDrugA
ElseIf Drug = "B" Then
TotalDrug = FDrugB
Else
TotalDrug = FDrugC
End If

End If
'Calculation
Benefit = TotalHealth + TotalDrug + TotalDental
ActiveCell.Value = Benefit
End Function

Creating a new function
 

First..
a FUNCTION is supposed to return a value

your procedure doesn't

a FUNCTION is not allowed to change the "environment"
(e.g. activecell)

To Solve

Function Benefits(Selection, Health, Dental, Drug As String) As Integer
'your variables...
'REMOVE Dim Benefit as Integer
'your code
'last lines:
'Assign the calculation as the functions result
Benefits = TotalHealth + TotalDrug + TotalDental
'REMOVE ActiveCell.Value = Benefit
End Function

Other erros in your code...
Dim x,y,z as integer

will dim x and y as variant, only z as integer
instrad of using all the ":loose" variables
use arrays

Dim aiHealth((1 to 3) as integer
Dim aiDental(1 to 3) as integer
Dim aiDrugs(1 to 3) as integer
Dim aiTotals(1 to 3) as integer

OR even...
dim aValues(1 to 4,1 to 3) as integer
(while your at it.. an Integer variable will hold values to 32000 only
(probably wizer to Type it as Long or Double)

Further..
check out Select Case in VBA help...

Further... buy a VBA book...
you've gotten enthousiastic, now get efficient!

(cuz to be honest your function aint the greatest thing
since Muhammed Ali! <g

HTH & Suc6!





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Kirk L.) wrote:

I'm trying to create a function to help determine rates.

There are four cells to be examined, and based on the selection of the
four cells, it will spit out a value in another cell.
The first cell, choice is Single or Family
second, third and fourth cell, all have a choice of A, B or C.

The problem is what I plunk this function into a cell in the
spreadsheet, I get a "#VALUE" error. I'm VERY new to VB, and I'm
hoping someone out there could help me out. Any help would be greatly
appreciated.

Here's what I have:

Function Benefits(Selection, Health, Dental, Drug As String)
Dim TotalHealth, TotalDental, TotalDrug As Integer
Dim SHealthA, SHealthB, SHealthC As Integer
Dim SDentalA, SDentalB, SDentalC As Integer
Dim SDrugA, SDrugB, SDrugC As Integer
Dim FSHealthA, FHealthB, FHealthC As Integer
Dim FSDentalA, FDentalB, FDentalC As Integer
Dim FSDrugA, FDrugB, FDrugC As Integer
Dim Benefit As Integer

'Rate Table (Single)
SHealthA = "insert rates here"
SHealthB = "insert rates here"
SHealthC = "insert rates here"
'etc., as dim'd above.

'Rate Table (Family)
FHealthA = "insert rates here"
FHealthB = "insert rates here"
FHealthC = "insert rates here"
'etc., as dim'd above.

'Single
If Selection = "Single" Then
'Health Selection
If Health = "A" Then
TotalHealth = SHealthA
ElseIf Health = "B" Then
TotalHealth = SHealthB
Else
TotalHealth = SHealthC
End If
'Dental Selection
If Dental = "A" Then
TotalDental = SDentalA
ElseIf Dental = "B" Then
TotalDental = SDentalB
Else
TotalDental = SDentalC
End If
'Drug Selection
If Drug = "A" Then
TotalDrug = SDrugA
ElseIf Drug = "B" Then
TotalDrug = SDrugB
Else
TotalDrug = SDrugC
End If

'Family
Else
'Health Selection
If Health = "A" Then
TotalHealth = FHealthA
ElseIf Health = "B" Then
TotalHealth = FHealthB
Else
TotalHealth = FHealthC
End If
'Dental Selection
If Dental = "A" Then
TotalDental = FDentalA
ElseIf Dental = "B" Then
TotalDental = FDentalB
Else
TotalDental = FDentalC
End If
'Drug Selection
If Drug = "A" Then
TotalDrug = FDrugA
ElseIf Drug = "B" Then
TotalDrug = FDrugB
Else
TotalDrug = FDrugC
End If

End If
'Calculation
Benefit = TotalHealth + TotalDrug + TotalDental
ActiveCell.Value = Benefit
End Function