Thread: Trouble Using Formulas in VBA Thanks: 0 Likes: 0

1. Trouble Using Formulas in VBA

Hi,

I have a specific problem with some code, and then a general question to ask as well. I started learning/writing VBA code about 4 months ago; therefore, I apologize for my ignorance on a lot of this stuff; anyways, my general question would be,

1. Is there any general / simple way to use formulas in VBA? I've seen people use the Evaluate, as well as other methods, and am just confused as to when? and how? to use these methods. ; if you do not quite understand my question, my specific code question below gives a perfect example.

2. I am working on the code below, and can't get the DateDiff function to work, anybody know what I'm doing wrong?

Code:
```Sub Assumed_Retention()

Application.ScreenUpdating = False

Dim LRA_WB As Workbook, Off_TC As String
Dim LRA_WS, LRA_WS2 As Worksheet
Dim Off_SC, ProformaDate, Off_SinceSC, Off_Since, Off_Retention, Off_RetentionSC, Off_Calc, C As Range

Set LRA_WB = ThisWorkbook
Set LRA_WS = LRA_WB.Worksheets("Rent_Roll")
Set LRA_WS2 = LRA_WB.Worksheets("List")

Set Off_SinceSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 15)
Set Off_Since = Range(Off_SinceSC, Off_SinceSC.End(xlUp))
Off_TC = "Total Office"

Set Off_RetentionSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 12)
Set Off_Retention = Range(Off_RetentionSC, Off_RetentionSC.End(xlUp))

For Each C In Off_Calc

If C > 9.99 Then

Off_Retention = 0.75

ElseIf C > 4.99 Then

Off_Retention = 0.5

ElseIf C > 2.99 Then

Off_Retention = 0.25

End If

Next C

Application.ScreenUpdating = True

End Sub```

2. Re: Trouble Using Formulas in VBA

Originally Posted by ohFice
2. I am working on the code below, and can't get the DateDiff function to work, anybody know what I'm doing wrong?

Code:
`Off_Calc = Evaluate("DateDiff(" & ProformaDate.Address, Off_Since.Address, " & m & ")`
The Excel function is spelled DateDif (only one "f" at the end)... I didn't really parse your code in full, but if you make that change in your code, I think it should work. Just so you know, VBA has its own function named DateDiff (yes, it is what you tried to use inside of the Evaluate function) that you can call directly to get the number of months...
Code:
`Off_Calc = DateDiff("m", ProformaDate.Value, Off_Since.Value)`
A note about your Dim statements... in VBA, each variable must be individually declared for its Type or else it will be defaulted to a Variant. So, in this code line...
Code:
`Dim Off_SC, ProformaDate, Off_SinceSC, Off_Since, Off_Retention, Off_RetentionSC, Off_Calc, C As Range`
only C is being declared as a Range variable... all of the other variable in the statement are being declared as Variants (which I think may be correct for your Off_Calc variable). The correct way to declare all of them except Off_Calc as Range variables is like this...
Code:
`Dim Off_SC As Range, ProformaDate As Range, Off_SinceSC As Range, Off_Since As Range, Off_Retention As Range, Off_RetentionSC As Range, Off_Calc As Variant, C As Range`
On a quick look, I do not see where the variable I highlighted in red is being used within your code, so I am guessing you intended it to be a Range.

3. Re: Trouble Using Formulas in VBA

Hi Rick,

Unfortunately that did not fix the issue; I adjusted my Dim statement, and set my Off_Calc variable to your suggested code, and getting a Type mismatch error.

Any idea why?

4. Re: Trouble Using Formulas in VBA

Did you try the change below?

The Excel function is spelled DateDif (only one "f" at the end)... I didn't really parse your code in full, but if you make that change in your code, I think it should work.

5. Re: Trouble Using Formulas in VBA

Yeah didn't work either, I think my evaluate was all messed up, its so hard to use for me :/

6. Re: Trouble Using Formulas in VBA

Post the code as you now have it and tell us what line it errors out on for you.

7. Re: Trouble Using Formulas in VBA

Code:
```Sub Assumed_Retention()

Application.ScreenUpdating = False

Dim LRA_WB As Workbook, Off_TC As String
Dim LRA_WS As Worksheet, LRA_WS2 As Worksheet
Dim Off_SC As Range, ProformaDate As Range, Off_SinceSC As Range, Off_Since As Range, Off_Retention As Range, Off_RetentionSC As Range, Off_Calc As Variant, C As Range

Set LRA_WB = ThisWorkbook
Set LRA_WS = LRA_WB.Worksheets("Rent_Roll")
Set LRA_WS2 = LRA_WB.Worksheets("List")

Set Off_SinceSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 15)
Set Off_Since = Range(Off_SinceSC, Off_SinceSC.End(xlUp))
Off_TC = "Total Office"

Set Off_RetentionSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 12)
Set Off_Retention = Range(Off_RetentionSC, Off_RetentionSC.End(xlUp))

For Each C In Off_Calc

If C > 9.99 Then

Off_Retention = 0.75

ElseIf C > 4.99 Then

Off_Retention = 0.5

ElseIf C > 2.99 Then

Off_Retention = 0.25

End If

Next C

Application.ScreenUpdating = True

End Sub```

there's a mismatch error on the DateDif formula line, and not sure if anything past that is bugged

8. Re: Trouble Using Formulas in VBA

It looks like you are trying to do a datediff between Range("F11") against a range of cells

Code:
`Off_Calc = DateDiff("m", ProformaDate.Value, Off_Since.Value)`
If you want to do that you will need to loop through each cell in Off_Since

9. Re: Trouble Using Formulas in VBA

As Michael has stated (posted quicker than me)

Code:
`Set Off_Since = Range(Off_SinceSC, Off_SinceSC.End(xlUp))`

is creating a multicell range

but you are using it in Datediff which requires a single date in the below line

Code:
`Off_Calc = DateDiff("m", ProformaDate.Value, Off_Since.Value)`
Also I can't see how
Code:
`Off_TC = "Total Office"`
shouldn't be higher up as you are using it in

Code:
`Set Off_SinceSC = LRA_WS.Range("E:E").Find(Off_TC, , xlValues, xlWhole).Offset(-1, 15)`

but then you will then also get an issue with the line below as Off_Calc is a number when it needs to be an Object

Code:
`For Each C In Off_Calc`