Excel Functions to VBA

Capture.JPG
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!
Mich MichBI LeadAsked:
Who is Participating?
 
aikimarkCommented:
This is a solution using the Select Case statement:
Function Q_29069731a(ByVal parmDiff) As String
    Select Case parmDiff
        Case Is >= 0
            Q_29069731a = vbNullString
        Case Is >= -7
            Q_29069731a = "Late 0-7 Days"
        Case Is >= -30
            Q_29069731a = "Late 8-30 Days"
        Case Is >= -60
            Q_29069731a = "Late 31-60 Days"
        Case Is >= -90
            Q_29069731a = "Late 61 to 90 Days"
        Case Else
            Q_29069731a = "Late Over 90 Days"
    End Select
End Function

Open in new window

1
 
NorieVBA ExpertCommented:
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?
1
 
Mich MichBI LeadAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Mich

Could you upload a sample workbook with some dummy data?
0
 
aikimarkCommented:
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

0
 
aikimarkCommented:
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

0
 
aikimarkCommented:

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

0
 
Mich MichBI LeadAuthor Commented:
aikimark, so for the value in that calculated column I just call Q_29069731a function?
0
 
aikimarkCommented:
Yes.  You can invoke the function just like any other (Excel Intrinsic) function.
0
 
Mich MichBI LeadAuthor Commented:
parmDiff - is a variable for my column 9 (column called "Late")? Correct?
0
 
aikimarkCommented:
yes.
0
 
aikimarkCommented:
@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

1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.