asked on

Excel Functions to VBA

I have an Excel tab "RawData" with the table - see attached screen snip.

Column 35 has this function:

=IF(RC[-26]<0,IF(RC[-26]>=-7,"Late 0-7 Days",IF(RC[-26]>=-30,"Late 8-30 Days",IF(RC[-26]>=-60,"Late 31-60 Days",IF(RC[-26]>=-90,"Late 61 to 90 Days","Late Over 90 Days")))))

Columns 36 to 40:

=IF(RC35="Late 0-7 Days",1,0)

=IF(RC35="Late 8-30 Days",1,0)

=IF(RC35="Late 31-60 Days",1,0)

=IF(RC35="Late 61 to 90 Days",1,0)

=IF(RC35="Late Over 90 Days",1,0)

I need to build a Sub that will do what Functions do in the above.

Can this be done in VBA?

Thanks!

Norie

MIch

What exactly is it you need VBA to do?

Do you want code to put the formulas in columns 36-40?

Or perhaps code that will go down column 35 and instead of placing the formulas in the columns put the results instead?

Mich Mich

ASKER

Norie, I need both.
I guess, first to have Column 35 calculations done by VBA and then respectively calculations done in 36-40. Thank you.

Norie

Mich

Could you upload a sample workbook with some dummy data?

aikimark

This seems the simplest solution, using the Switch() function

Function Q_29069731(ByVal parmDiff) As String
    If parmDiff >= 0 Then
        Q_29069731 = vbNullString
    Else
        Q_29069731 = Switch(parmDiff >= -7, "Late 0-7 Days", parmDiff >= -30, "Late 8-30 Days", parmDiff >= -60, "Late 31-60 Days", parmDiff >= -90, "Late 61 to 90 Days", parmDiff < -90, "Late Over 90 Days")
    End If
End Function

Open in new window

aikimark

This might be a bit easier to read

Function Q_29069731(ByVal parmDiff) As String
    If parmDiff >= 0 Then
        Q_29069731 = vbNullString
    Else
        Q_29069731 = Switch(parmDiff >= -7, "Late 0-7 Days", _
                            parmDiff >= -30, "Late 8-30 Days", _
                            parmDiff >= -60, "Late 31-60 Days", _
                            parmDiff >= -90, "Late 61 to 90 Days", _
                            parmDiff < -90, "Late Over 90 Days")
    End If
End Function

Open in new window

aikimark

Testing results:

?Q_29069731(-4)
Late 0-7 Days
?Q_29069731(-14)
Late 8-30 Days
?Q_29069731(-42)
Late 31-60 Days
?Q_29069731(-4242)
Late Over 90 Days
?Q_29069731(42)

'end of tests

Open in new window

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mich Mich

ASKER

aikimark, so for the value in that calculated column I just call Q_29069731a function?

aikimark

Yes. You can invoke the function just like any other (Excel Intrinsic) function.

Mich Mich

ASKER

parmDiff - is a variable for my column 9 (column called "Late")? Correct?

aikimark

yes.

aikimark

@Mich

If you know the expected distribution of differences, you can improve the performance of the function.
Example:
In this example, the Case clause conditions are written to be executed in any order. The hypothetical case for this example is that most of the differences will be 31-60 days late, followed by 8-30 days late, etc.

Function Q_29069731a(ByVal parmDiff) As String
    Select Case parmDiff
        Case -60 To -31
            Q_29069731a = "Late 31-60 Days"
        Case -30 To -8
            Q_29069731a = "Late 8-30 Days"
        Case -90 To -61
            Q_29069731a = "Late 61 to 90 Days"
        Case Is < -90
            Q_29069731a = "Late Over 90 Days"
        Case Is >= 0
            Q_29069731a = vbNullString
        Case -7 To -1
            Q_29069731a = "Late 0-7 Days"
    End Select
End Function

Open in new window