# 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!
###### Who is Participating?

Commented:
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
``````
1

VBA 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

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

VBA ExpertCommented:
Mich

Could you upload a sample workbook with some dummy data?
0

Commented:
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
``````
0

Commented:
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
``````
0

Commented:

## 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
``````
0

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

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

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

Commented:
yes.
0

Commented:
@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
``````
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.