VBA functions & Arguments

[Brad1951]

Ok, so I have the 2 equations: A = 18000 + 500x
b = 22000 + 500y + 1000

x are values on range b2:b11 & y are values on c2:c11
the results will be on d2:d11

For this function I need to calculate the first equation with range b2:b12 and x has to be an argument

I need the same function for the second equation but my main concern is to set up the final function which decides which equation to calculate. If Y > 0 then I need the final function to use equation B. So if b2 has a x-value of 5 and c2 has a y-value of 2 I would use equation B and the output would be in D2.

Any help is appreciated. I am looking through the site trying to figure it out step by step but any help is appreciated!

First post/Thread!! Hello everyone!!

[Norie]

What if Y<=0?

[Brad1951]

y cannot be 0

[Norie]

So Y will always be >0 and equation B will always be used?

[Brad1951]

There should be no negative numbers

[Norie]

Sorry for being thick here but if there are no negative numbers doesn't that mean that you will always use formula B and the criteria Y>0 is redundant?

Also, if the results from from both formulas are to go in D2:D11 I can't see how it'll work.

[Brad1951]

Sorry, I didn't explain the scenario thoroughly. Equation b should only be used if y is any number other than 0.

[Brad1951]

How would I go about referring to the cells and having the proper equation calculate it?

[Brad1951]

How would I go about referring to the cells and having the proper equation calculate it?

[Norie]

The formulas are simple.

For formula A it would be:

=18000 + 500*B2

For formula B it would be:

=2300 + 500*C2

You would copy both formulas down for all the values in column B/C.

[Brad1951]

Ok, so I am about to finalize my work and need help with the final step.

So far I have 2 Function with a sub to test

Private Sub test_getBaristaSalary()
Dim InputYear As Integer

    InputYear = InputBox(Prompt:="How many years of Experience?")
    MsgBox Prompt:="Total Salary is $" & getBaristaSalary(Whatyearisit:=InputYear)

End Sub

Function getBaristaSalary(Whatyearisit As Integer)

  
    getBaristaSalary = Whatyearisit * 500 + 18000
    ' Annual Salary = $18000 + $500x(years as barista)
End Function

Function 2 is

Private Sub test_getManagerSalary()
Dim InputYearBarista As Integer
Dim InputYearManager As Integer

    InputYearBarista = InputBox(Prompt:="Years of experience as Barista?")
    InputYearManager = InputBox(Prompt:="Years of experience as Manager?")
    MsgBox Prompt:="Total Salary is $" & getManagerSalary(BaristaYear:=InputYearBarista, ManagerYear:=InputYearManager)

End Sub

Function getManagerSalary(BaristaYear As Integer, ManagerYear As Integer)
  
    getManagerSalary = (BaristaYear * 500) + (ManagerYear * 1000) + 22000
    ' Annual Salary = $22000 + $500x(years as barista) + $1000x(years as a manager)
End Function

My third function needs to determine which of the first 2 functions will be used to calculate the equation based on the answer to InputYearManager. If the value of InputYearManager is anything besides 0 then I need to use the second function however if 0 is the answer then I need the first function used.

So far I have this:
[B]

Private Sub test_getPredictedSalary()
Dim InputYearBarista As Integer
Dim InputYearManager As Integer

    InputYearManager = InputBox(Prompt:="Years of experience as Manager?")
    InputYearBarista = InputBox(Prompt:="Years of experience as Barista?")
    MsgBox Prompt:="Total Salary is $" & getPredictedSalary(BaristaYear:=InputYearBarista, ManagerYear:=InputYearManager)
End Sub

Function getPredictedSalary(BaristaYear As Integer, ManagerYear As Integer) As Double

If InputYearManager >= 0 Then
    getPredictedSalary = getManagerSalary
ElseIf InputYearManager = 0 Then
    getPredictedSalary = getBaristaSalary

End If
End Function


' Annual Salary = $22000 + $500x(years as barista) + $1000x(years as a manager)
' Annual Salary = $18000 + $500x(years as barista)

When I run this I get a Complie Error: Argument not optional. Any suggestions?

[Norie]

This looks slightly different to the original question.

Anyway, I do get an error with that code but not the one you describe.

The error I get is in the function getPredictedSalary and it's Variable not defined with InputYearManager highlighted.

If I change InputYearManager to ManagerYear I then get the error you describe.

The error is because you aren't passign any arguments to the functions getManagerSalary or getBaristaSalary.

If I change the function to this then it works.

Function getPredictedSalary(BaristaYear As Integer, ManagerYear As Integer) As Double

    If ManagerYear >= 0 Then
        getPredictedSalary = getManagerSalary(BaristaYear, ManagerYear)
    ElseIf ManagerYear = 0 Then
        getPredictedSalary = getBaristaSalary(BaristaYear)

    End If
End Function

There was another problem with overflow errors so I changed the declarations throughout the code from Integer to Long.

This is what I ended up with.

Option Explicit

Private Sub test_getBaristaSalary()
Dim InputYear As Integer

    InputYear = InputBox(Prompt:="How many years of Experience?")
    MsgBox Prompt:="Total Salary is $" & getBaristaSalary(Whatyearisit:=InputYear)

End Sub

Function getBaristaSalary(Whatyearisit As Long) As Long

    getBaristaSalary = Whatyearisit * 500 + 18000
    ' Annual Salary = $18000 + $500x(years as barista)
End Function

Private Sub test_getManagerSalary()
Dim InputYearBarista As Long
Dim InputYearManager As Long

    InputYearBarista = InputBox(Prompt:="Years of experience as Barista?")
    InputYearManager = InputBox(Prompt:="Years of experience as Manager?")
    MsgBox Prompt:="Total Salary is $" & getManagerSalary(BaristaYear:=InputYearBarista, ManagerYear:=InputYearManager)

End Sub

Function getManagerSalary(BaristaYear As Long, ManagerYear As Long) As Long

    getManagerSalary = (BaristaYear * 500) + (ManagerYear * 1000) + 22000
    ' Annual Salary = $22000 + $500x(years as barista) + $1000x(years as a manager)
End Function

Private Sub test_getPredictedSalary()
Dim InputYearBarista As Long
Dim InputYearManager As Long

    InputYearManager = InputBox(Prompt:="Years of experience as Manager?")
    InputYearBarista = InputBox(Prompt:="Years of experience as Barista?")
    MsgBox Prompt:="Total Salary is $" & getPredictedSalary(BaristaYear:=InputYearBarista, ManagerYear:=InputYearManager)

End Sub

Function getPredictedSalary(BaristaYear As Long, ManagerYear As Long) As Long

    If ManagerYear >= 0 Then
        getPredictedSalary = getManagerSalary(BaristaYear, ManagerYear)
    ElseIf ManagerYear = 0 Then
        getPredictedSalary = getBaristaSalary(BaristaYear)
    End If

End Function

[Brad1951]

You are my savior Norie! Thanks for the quick reply!

One more thing. I need to allow decimals as inputs for the years. What do I need to change?

[protonLeah]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please! edit post #11. It must look like #12.

[Brad1951]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please! edit post #11. It must look like #12.

Done! Sorry about that

[Brad1951]

How can I change the code to allow decimals (such as 4.5) to be allowed as inputs. Now it just rounds the solution when a decimal is the input.

[Brad1951]

Nevermind, got it! Just changed few things to double