作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试将来自不同文件的季度数据编译到单个 Excel 工作簿中的过程自动化(这需要每个季度完成)。现在我已经到了一个地步,我想知道是否应该将所有代码保留在主子程序中,或者是否应该使用函数。我真的很想听听那些使用 VBA 超过几个月的人的意见,并了解如何编写高效且可读的代码。
我的代码需要做的第一件事是获取三个与日期/日期相关的变量:
Case Else
没必要吧?我的意思是
month(Date)
只能是 12 个月之一。
Sub DetermineDate()
Dim qVar As String
Dim yVar As Integer
Dim fullDate As String
yVar = Year(Date) 'set value here or each time in case statement?
Select Case month(Date)
Case 1, 2, 3
qVar = "Q4"
yVar = Year(Date) - 1
fullDate = "12.31." & yVar
Case 4, 5, 6
qVar = "Q1"
fullDate = "03.31." & yVar
Case 7, 8, 9
qVar = "Q2"
fullDate = "06.30." & yVar
Case 10, 11, 12
qVar = "Q3"
fullDate = "09.30." & yVar
Case Else
MsgBox "Error"
Exit Sub
End Select
End Sub
最佳答案
你可以做这样的事情。
将以下代码添加到新的 类(class)模块 在 VBA 编辑器中并确保为该新类命名 cls 对象 ...
Public Quarter As Integer
Public Year As Integer
Public EndDate As Date
Property Get EndMonth() As Integer
EndMonth = Month(EndDate)
End Property
Property Get QuarterText() As String
QuarterText = "Quarter " & Quarter
End Property
Property Get DateString() As String
DateString = Right("0" & EndMonth, 2) & "." & Day(EndDate) & "." & Year
End Property
Function DetermineDate(ByVal dtDate As Date) As clsObject
' Initialise the current functions return value.
' This is only required if the return value is an object that is yet to be assigned a value
Set DetermineDate = New clsObject
' If the year is a simple derivation that is not specific to the month, do it here.
' You can also set it here first if it is the default for the vast majority of cases
' and then in the case statement, set it to something different if required.
DetermineDate.Year = Year(dtDate)
Select Case Month(dtDate)
Case 1, 2, 3
DetermineDate.Quarter = 4
DetermineDate.EndDate = DateSerial(DetermineDate.Year, 12, 31)
DetermineDate.Year = DetermineDate.Year - 1
Case 4, 5, 6
DetermineDate.Quarter = 1
DetermineDate.EndDate = DateSerial(DetermineDate.Year, 3, 31)
Case 7, 8, 9
DetermineDate.Quarter = 2
DetermineDate.EndDate = DateSerial(DetermineDate.Year, 6, 30)
Case 10, 11, 12
DetermineDate.Quarter = 3
DetermineDate.EndDate = DateSerial(DetermineDate.Year, 9, 30)
Case Else
' For this scenario, you don't need an else but it's here anyway.
Exit Function
End Select
End Function
Public Sub CallDetermineDate()
Dim objResult As clsObject
Set objResult = DetermineDate(Now)
Debug.Print "End Date = " & objResult.EndDate
Debug.Print "Year = " & objResult.Year
Debug.Print "End Month = " & objResult.EndMonth
Debug.Print "Quarter = " & objResult.Quarter
Debug.Print "Quarter Text = " & objResult.QuarterText
Debug.Print "Date String = " & objResult.DateString
End Sub
CallDetermineDate
例程,您将看到输出。
关于excel - VBA:何时使用函数与将代码放在主子中,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55878753/
我是一名优秀的程序员,十分优秀!