public function to sum input and return total sum + 10%

[Santa-D]

Can someone quickly tell me if I'm on the right path?

I'm manually entering a pile of invoices to finalise end of month
payments but because the supplier changed the format of the invoices I
have to manually enter the cell values such as:

RENT Variable Outgoings
=((100+200)*1.1) + ((250+900)*1.1)

this needs to be done as the GST is calculated on each individual item.

I was trying to make a public function but I can't figure out the
input.

What I want to do is in a cell enter the following
=igst(100,200,250,900) and the function will return the correct result.

But I get errors.

This is what I've done.

-------------------------------------------------------------------------------------
Public Function igst(range) As Double

Dim sumarray As Double
igst = 0

sumarray = DSum(igst) * 10
gst = sumarray

End Function

 

[jseven]

You were getting there. Problem is you have to define each variable.
This will allow you to enter the formula as you wish. Then you have to
return the result to the function by saying "igst = result"

Function igst(var1 As Double, var2 As Double, var3 As Double, var4 As
Double)

igst = ((var1 + var2) * 1.1) + ((var3 + var4) * 1.1)

End Function

Regards,
Jamie

 

[Santa-D]

What if the array is larger than 4 variables?

Let's say there is 6 or 8 or 12 variables?

It would be stupid to go

Dim var1, var2, var3, var4.....var99 ?

I guess what I'm trying to do is define an array of values that is
input via the var1,2,3,4 and then return a single string.

i.e.

igst($200,$300,$400,$500) = $1540
at the same time I could do
igst($200) = $220

 

[NickHK]

Assuming that 2 variables are added, then * 1.1, you can send in a
ParamArray and loop through it. You may have to change the calculation, or
allow odd number of inputs depending on your requirements. Something like:

Private Function igst(taxRate As Single, ParamArray Inputs() As Variant) As
Variant
Dim i As Long
Dim RunTot As Single
'Check if even number of elements in paramarray
If (UBound(Inputs) - LBound(Inputs)) Mod 2 <> 1 Then
igst = CVErr(xlErrNum) 'Or other error
Exit Function
End If

For i = LBound(Inputs) To UBound(Inputs) Step 2
RunTot = RunTot + (Inputs(i) + Inputs(i + 1)) * taxRate
Next
igst = RunTot
End Function

Private Sub CommandButton1_Click()
MsgBox igst(1.1, 10, 20, 30, 40, 50, 60, 70, 80)
End Sub

NickHK

 

[Santa-D]

THIS IS FANTASTIC!
Thanks heaps.

Assuming that 2 variables are added, then * 1.1, you can send in a
ParamArray and loop through it. You may have to change the calculation, or
allow odd number of inputs depending on your requirements. Something like:

Private Function igst(taxRate As Single, ParamArray Inputs() As Variant) As
Variant
Dim i As Long
Dim RunTot As Single
'Check if even number of elements in paramarray
If (UBound(Inputs) - LBound(Inputs)) Mod 2 <> 1 Then
igst = CVErr(xlErrNum) 'Or other error
Exit Function
End If

For i = LBound(Inputs) To UBound(Inputs) Step 2
RunTot = RunTot + (Inputs(i) + Inputs(i + 1)) * taxRate
Next
igst = RunTot
End Function

Private Sub CommandButton1_Click()
MsgBox igst(1.1, 10, 20, 30, 40, 50, 60, 70, 80)
End Sub

NickHK

 

[Santa-D]

Is there a way to make it work if an odd number is entered?
What I'm doing is going

=(100,200,20,0)

 

[NickHK]

You could just use a 0 to pad to an even number, but it all depends on how
you calculate your total. I was only going with the even rule as in your
first post you said:
=((100+200)*1.1) + ((250+900)*1.1)
I supposed you have a reason to add pairs, then * 1.1, rather than add all,
then *1.1

NickHK

It depends how you