Solved: Obtaining the Value from a Formula in VBA

[Opv]

I've been working with the formula property to assign formulas to particular cells. I got to wondering whether it is possible to to do some stuff on the fly within VBA without actually populating a cell with the formula.

By that, I mean, is it possible to: 1) construct a formula (the specific formula below); 2) calculate the resulting value; 3) assign that value to a variable, without populating a cell; and then to 4) use that Value in yet a second formula that will be used to populate a cell.


=IF(WEEKDAY(DATE(YEAR(TODAY()),1,1),1) =1,DATE(YEAR(TODAY()),1,1),DATE(YEAR(TODAY()),1,1)+ (8-WEEKDAY(DATE(YEAR(TODAY()),1,1))))


For example, this formula calculates the first Sunday of the current year. What I'm wanting to do is calculate this date without populating a cell on my worksheet, then assign the resulting date value to a variable which can then be referenced in another formula.

Possible?

[Paul_Hossler]

Not sure if this is what you're looking for, but there are VBA equivalents for the WS functions that you can call directly



Option Explicit
Sub drv()
Dim i As Long
Dim d As Date

i = 0
While Weekday(DateSerial(Year(Now), 1, 1 + i)) <> vbSunday
i = i + 1
Wend

d = DateSerial(Year(Now), 1, i + 1)
MsgBox Format(d, "Long Date")
'add 60 days and put on the sheet
ActiveSheet.Cells(1, 1).Value = d + 60
End Sub


Paul

[Opv]

Not sure if this is what you're looking for, but there are VBA equivalents for the WS functions that you can call directly



Option Explicit
Sub drv()
Dim i As Long
Dim d As Date

i = 0
While Weekday(DateSerial(Year(Now), 1, 1 + i)) <> vbSunday
i = i + 1
Wend

d = DateSerial(Year(Now), 1, i + 1)
MsgBox Format(d, "Long Date")
'add 60 days and put on the sheet
ActiveSheet.Cells(1, 1).Value = d + 60
End Sub

Paul

Yes, I think that is what I'm trying to accomplish. Thank you very much.

Opv

[mdmackillop]

You can use a function to calculate a value. This can be assigned to a Public Variable (MyDate) or to a Private Variable (x). Both can be used in other routines e.g.

Option Explicit

'Public variable
Dim MyDate As Date

Sub GetDate()
Dim x As Date 'Private variable
MyDate = DateOfFirst(1)
Call Test
x = MyDate + 3
Call Test2(x)
End Sub

'Calculate date
Function DateOfFirst(Dy As Long)
Dim d, dt
dt = DateSerial(Year(Date), 1, 1)
d = Weekday(dt)
DateOfFirst = dt + (Dy + d + 2) Mod 7
End Function

'Use Public variable
Sub Test()
MsgBox MyDate
End Sub

'Date passed as Private variable
Sub Test2(x As Date)
MsgBox x
End Sub

[Opv]

You can use a function to calculate a value. This can be assigned to a Public Variable (MyDate) or to a Private Variable (x). Both can be used in other routines e.g.


Thanks. I'll experiment with these options.

Opv