CODEWITHSUNDEEP
vbaexcel
neeenway
neeenway

Reputation: 5

Passing cell ranges to VBA function working via sub but not on spreadsheet

I was writing a function that runs some payment calculations for me. What I do is I feed in ranges of cells. 

Function clinkmeup(Tuesdays As Excel.Range, Sundays As Excel.Range, Subbing As Excel.Range, Optional Other As Exce.Range = 0) As Variant

e.g. Payment(A10:A12, B12:B14, C13:C17) and it calculates my pay from those cells. This function works properly when I call it from a sub as seen here: 

Sub lmao()
    money = clinkmeup(Worksheets("Talent").Range("C9:C10"), Worksheets("Talent").Range("F9:F11", Worksheets("Talent").Range("H9:H12"))
    MsgBox (money)
End Sub

But when I try to call it from the spreadsheet (by writing =clinkmeup(C9:C10, F9:F11, H9:H12))I get a VALUE error.

I have searched all over the net for a solution but I haven't found any that is relevant to my own case.

This has been frustrating me for a while as I am new to VBA and any help would be much appreciated.

Below is my whole code:

    Function clinkmeup(Tuesdays As Excel.Range, Sundays As Excel.Range, Subbing As Excel.Range, Optional Other As Excel.Range = 0) As Variant
    Dim teaching As Double
    Dim admin As Double
    Dim seminar As Double
    Dim elem As Variant
    Dim sumo1 As Double


    teaching = Worksheets("Talent").Range("Teacrate")
    admin = Worksheets("Talent").Range("Admrate")
    seminar = Worksheets("Talent").Range("Semrate")
    sum1 = 0



    For Each elem In Tuesdays
        sum1 = sum1 + elem
    Next elem

    For Each elem In Sundays
        sum1 = sum1 + elem
    Next elem

    For Each elem In Subbing
        sum1 = sum1 + elem
    Next elem


    sum1 = (sum1/ 60) * teaching
    sum2 = 0



    If Not Other Is Nothing Then
        For Each elem In Other
             If elem.Offset(0, 1) = "S" Then
                 sum2 = (elem / 60) * seminar + sum2
             Else
                 sum2 = (elem / 60) * admin + sum2
             End If

         Next elem
    End If


    clinkmeup = sum1 + su2

   End Function

Upvotes: 0

Views: 68

Answers (1)

Matt
Matt

Reputation: 26

I replicated the error and solved it by replacing this part of the function header:

Optional other As Range = 0

with this: 

Optional other As Range = Nothing

Upvotes: 1

Related Questions