# 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Analyst Assistant Commented:
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
Analyst Assistant Commented:
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
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.