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
-------------------------------------------------------------------------------------

Maybe a for loop would work?

[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



jseven wrote:
> 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

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> jseven wrote:
> > 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]

THIS IS FANTASTIC!
Thanks heaps.


NickHK wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> > jseven wrote:
> > > 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]

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 wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> > jseven wrote:
> > > 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
> >

[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
"Santa-D" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> > >
> > > jseven wrote:
> > > > 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
> > >
>

[NickHK]

This should odd or even number of inputs:
Private Function igst(taxRate As Single, ParamArray Inputs() As Variant) As
Variant
Dim i As Long
Dim RunTot As Single

On Error GoTo Handler

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

Handler:
Select Case Err.Number
Case 9 'Subscript Out of Range
RunTot = RunTot + Inputs(i) * taxRate
igst = RunTot
Case Else
'Any other errors to deal with
igst = CVErr(xlErrNum) 'Or other error
End Select

End Function

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

NickHK

"Santa-D" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> > >
> > > jseven wrote:
> > > > 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
> > >
>