用于带日期循环的 Excel VBA 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15016743/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA script for loop with dates
提问by Selvam
I am calculating the number of work hours (8am to 8pm) between the 2 given dates, excluding Weekends and Public holidays, but my code syntax is incorrect.
我正在计算两个给定日期之间的工作小时数（上午 8 点到晚上 8 点），不包括周末和公共假期，但我的代码语法不正确。
Sample data:
样本数据：
Start day: 17/06/2011 08:00:00 AM
开始日期：17/06/2011 08:00:00 AM
End day: 19/06/2011 08:00:00 PM
结束日：19/06/2011 08:00:00 PM
Sub SLA_Days_Resolved_F()
Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("F2", Range("F2").End(xlDown)).Rows.Count
Dim total As Integer 'to count the total hours
Dim st As String 'start date cell
Dim en As String 'end date cell
Dim destCell As String
Dim d As Date ' for the loop
total = 0
' Establish "For" loop to loop "numrows" number of times.
For x = 2 To NumRows + 1
st = "G" & CStr(x) 'reference to the cells
en = "D" & CStr(x)
'loop from start date to end date
For d = Date(Range(st)) To Date(Range(en))
'check if the current date is found is a Public holiday in the range or if a weekend
If ((Vlookup(d,lookups!$o:$p,2,false))=1) or (weekend(d))Then
'minus 8 to remove hours before 8am.
total = (total + Hour(d) + minutes(d) / 60) - 8
End If
Next
Next
End Sub
回答by David Zemens
You are not assigning any values to variables stor en.
您没有为变量st或en.
Dateis not a function available in VBA. You will probably need to use DateSerial function. Here is a simple example of looping over dates which you should be able to modify.
Date不是 VBA 中可用的函数。您可能需要使用 DateSerial 函数。这是一个循环日期的简单示例，您应该能够修改它。
Sub LoopDates()
Dim d As Date
'Loop the days beteween today and March 1, 2013.
For d = DateSerial(Year(Now), Month(Now), Day(Now)) To DateSerial(2013, 3, 1)
Debug.Print d 'Prints the "d" value in the immediate window.
Next
End Sub
Also, you can't just put worksheet formulae in VBA. This line is definitely wrong syntax for Vlookup, and Weekendis not a formula that I'm aware of (testing it seems to confirm it is not a valid call on worksheet or in VBA.
此外，您不能只将工作表公式放在 VBA 中。这行对于 Vlookup 来说绝对是错误的语法，并且Weekend不是我所知道的公式（测试它似乎确认它不是工作表或 VBA 中的有效调用。
If ((Vlookup(d,lookups!$o$3:$p$26,2,false))=1) or (weekend(d))Then
If ((Vlookup(d,lookups!$o$3:$p$26,2,false))=1) or (weekend(d))Then
Rewrite as:
改写为：
If Application.WorksheetFunction.Vlookup(d,Sheets("lookups").Range("$o:$p"),2,false)=1 _
or Not Application.WorksheetFunction.Weekday(d) Then
ANOTHER EXAMPLEof a date loop where I have dimensioned the variables in what I believe to be a more efficient manner:
另一个日期循环示例，我以我认为更有效的方式对变量进行了标注：
Sub Test()
Dim st As Range
Dim x As Integer
Dim stDate As Date
Dim enDate As Date
Dim d As Date
Dim numRows as Long
NumRows = Range("F2", Range("F2").End(xlDown)).Rows.Count
For x = 0 To NumRows-2
'SET YOUR VARIABLES HERE
' This may seem redundant or unnecessary for this case, but it makes structuring nested
' loops easier to work with, and then there are fewer places to make changes,
' if you need to make changes.
Set st = Range("G2").Offset(x, 0)
Set en = Range("D2").Offset(x, 0)
stDate = DateSerial(Year(st), Month(st), Day(st))
enDate = DateSerial(Year(en), Month(en), Day(en))
'Then, loop through the dates as necessary
For d = stDate To enDate
Debug.Print d
'Do your code here.
Next
Next
End Sub
