Will Macro before a Function like Now() ??

[Boiler-Todd]

If I run a macro for a workbook with "Sub Workbook_Open()", will the macro
run first before the functions like "Now()" in the workbook updates. I am
triggering my macro off the date function of Now().

For example, I did work today Now(10/5/09). When I open the workbook
tomorrow, I want the macro to run with the date (10/5/09) first then have the
function update to Now(10/6/09).

Here is my code. It works but I haven't tried it over a long period.
ub Workbook_Open()
Dim cell As Range, intSheet As Integer
Dim wS1 As Worksheet
Dim wSA As Worksheet
Dim wS2 As Worksheet
Dim wSB As Worksheet
Dim wS3 As Worksheet
Dim wSC As Worksheet
Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date


Set wS1 = ThisWorkbook.Sheets(1)
Set wSA = ThisWorkbook.Sheets("History 1")
Set wS2 = ThisWorkbook.Sheets(2)
Set wSB = ThisWorkbook.Sheets("History 2")
Set wS3 = ThisWorkbook.Sheets(3)
Set wSC = ThisWorkbook.Sheets("History 3")

' For intSheet = 1 To 3
If Sheets(3).Range("H1").Value < Date Then

wS1.Range("A12:K16").Copy wSA.Range("A12:K16")
wS2.Range("A12:K16").Copy wSB.Range("A12:K16")
wS3.Range("A12:K16").Copy wSC.Range("A12:K16")
Date1 = wS1.Range("H1")
Date2 = wS2.Range("H1")
Date3 = wS3.Range("H1")
wSA.Range("H1") = Date1
wSB.Range("H1") = Date2
wSC.Range("H1") = Date3

For intSheet = 1 To 3
For Each cell In Sheets(intSheet).Range("A12:K16")
If Not cell.Locked Then cell.ClearContents
Next cell

Next
End If
End Sub

 

[Gary''s Student]

Don't fret over whether the macro will run before or after =NOW() gets
calculated.

Just store the date/time in some cell when the workbook is closed:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(1).Range("Z100").Value = Now
End Sub


If your macro needs the old Now, have it look in Z100. If it needs the new
Now, let it calculate it.

 

[Tushar Mehta]

A possible alternative to Gary's Student's suggestion would be for
your code to update the cell with the current date & time instead of
using the formula =NOW().

If I run a macro for a workbook with "Sub Workbook_Open()", will the macro
run first before the functions like "Now()" in the workbook updates. I am
triggering my macro off the date function of Now().

For example, I did work today Now(10/5/09). When I open the workbook
tomorrow, I want the macro to run with the date (10/5/09) first then have the
function update to Now(10/6/09).

Here is my code. It works but I haven't tried it over a long period.
ub Workbook_Open()
Dim cell As Range, intSheet As Integer
Dim wS1 As Worksheet
Dim wSA As Worksheet
Dim wS2 As Worksheet
Dim wSB As Worksheet
Dim wS3 As Worksheet
Dim wSC As Worksheet
Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date


Set wS1 = ThisWorkbook.Sheets(1)
Set wSA = ThisWorkbook.Sheets("History 1")
Set wS2 = ThisWorkbook.Sheets(2)
Set wSB = ThisWorkbook.Sheets("History 2")
Set wS3 = ThisWorkbook.Sheets(3)
Set wSC = ThisWorkbook.Sheets("History 3")

' For intSheet = 1 To 3
If Sheets(3).Range("H1").Value < Date Then

wS1.Range("A12:K16").Copy wSA.Range("A12:K16")
wS2.Range("A12:K16").Copy wSB.Range("A12:K16")
wS3.Range("A12:K16").Copy wSC.Range("A12:K16")
Date1 = wS1.Range("H1")
Date2 = wS2.Range("H1")
Date3 = wS3.Range("H1")
wSA.Range("H1") = Date1
wSB.Range("H1") = Date2
wSC.Range("H1") = Date3

For intSheet = 1 To 3
For Each cell In Sheets(intSheet).Range("A12:K16")
If Not cell.Locked Then cell.ClearContents
Next cell

Next
End If
End Sub

Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins