Function Revision

[cstimart]

I am terrible with writing functions using the R1C1 style. I assume this can be written better that I have it.

Function Revenue_CQ(x As String)
Dim rw As Integer
If x = "C" Then
    rw = 12
ElseIf x = "P" Then
    rw = 61
End If
    With Sheets("Info")
        If Quarter() = "1Q" Then
            Revenue_CQ = .Range("C" & rw)
        ElseIf Quarter() = "2Q" Then
            Revenue_CQ = .Range("I" & rw)
        ElseIf Quarter() = "3Q" Then
            Revenue_CQ = .Range("O" & rw)
        ElseIf Quarter() = "4Q" Then
            Revenue_CQ = .Range("U" & rw)
        End If
    End With
End Function

Help?

 

[Joe4]

With functions, you usually do not reference sheets and cells directly within the function. You usually feed it in a bunch of values or cell references, and then program it to do the calculations off of that.

Can you explain what the function is supposed to be doing, and how it should work?

 

[cstimart]

it's basically going to the Info sheet and grabbing a value, depending on the value input into the function and the value in Quarter().

therefore, a cell on my spreadsheet contained =Revenue_CQ("P"), and we are looking a 1st Quarter data, the function would go to the Info sheet and extract the value in range C61.

I just figure that the "C" - "I" - "O" - "U" columns could be utilized via the R1C1 option...driven from the quarter() value, using (Left(Quarter(),1) *6) - 3 and then eliminating the If/Then/Else?

 

[Joe4]

Where does Quarter() come from?
Is this another UDF that you have?

 

[cstimart]

Where does Quarter() come from?
Is this another UDF that you have?

Yes, Quarter() is a Function I have that derives the quarter from the file name.

 

[Joe4]

I am thinking it might be possible that you may not need any UDFs at all, that you might be able to do it all through a standard Excel function.

Can you post the code for your Quarter function? I find it odd that it is doing the calculation without any inputs.

 

[cstimart]

Function Quarter()
    Dim spacePos As Integer
    Quarter = Application.ActiveWorkbook.FullName
    spacePos = InStr(Quarter, "CONTRACT")
        Quarter = Mid$(Quarter, spacePos - 3, 2)
End Function

 

[Joe4]

Here is how I would write the function:

Function Revenue_CQ(x As String, Quarter As String)
 
    Dim rw As Integer
    Dim qtr As Byte
    
    Select Case x
        Case "C"
            rw = 12
        Case "P"
            rw = 61
'   What to do if x not equal to "P" or "C"?
        Case Else
            Revenue_CQ = 0
            Exit Function
    End Select
    
'   Get the numeric quarter number (or exit function if no quarter)
    If Len(Quarter) > 0 Then
        qtr = Left(Quarter, 1) + 0
    Else
        Revenue_CQ = 0
        Exit Function
    End If
        
'   Return revenue
    Revenue_CQ = Sheets("Info").Cells(rw, qtr * 6 - 3)
    
End Function

Note that I made use of the fact that columnc C, I, O, and U are 6 columns from each other. So I was table to collapse the lookup into a single line, making use of the number from the quarter.

Note also that I do not call Quarter() from inside the function. Instead, I added a variable to this Function to feed it in. So, if you are calling this function from a Spreadsheet, your formula might look like this:

=Revenue_CQ("P",Quarter())

 

[Joe4]

BTW, you could keep the Quarter function call in this function, if you like. I just put it in the arguments of your function instead to give it more flexibility (then you could use this function and feed it values from places other than the file name, if need be).