vba_1《考勤表》

最新推荐文章于 2024-04-09 15:37:28 发布

原创最新推荐文章于 2024-04-09 15:37:28 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#vba

本文介绍了使用VBA进行考勤表设计的方法，包括自动填充当月日期、设置指定单元格颜色和格式化时间。通过VBA的日期处理、单元格格式和颜色设置，实现考勤表的高效管理。

VBA(考勤表简单设计)

一当月份日期取得

二指定单元格设置颜色

三指定单元格格式

一
Sub EnterDates5() '获取当前月份的所有日子
Dim TheDate As Date
TheDate = DateSerial(Year(Date), Month(Date), 1) '获取当日所在月份的第一天的日期
While Month(TheDate) = Month(Date) '循环，直到跳出当月
ActiveCell = TheDate '设置当前单元格值为当前日期
TheDate = TheDate + 1 '当前日期的下一天
ActiveCell.Offset(1, 0).Activate '激活下一个单元格，用于输出下一天的日期
Wend
End Sub
(转载于http://www.excelpx.com/thread-156742-1-1.html)
二
Range(“c” & m + 2 & “:h” & m + 2).Interior.ColorIndex = 15
单元格范围.Interior.ColorIndex = 15
#15为颜色索引
参考(https://blog.youkuaiyun.com/keepiss/article/details/79475018)

三
Range(“d3:” & “f” & t).NumberFormatLocal = “hh:mm:ss”
参考(https://blog.youkuaiyun.com/jyh_jack/article/details/78037246)

Sub weekcheck()
    Dim TheDate As Date
    Dim count As Integer
    
    Range("c3").Select
    TheDate = DateSerial(Year(Date), Month(Date), 1)
    While Month(TheDate) = Month(Date)
        count = count + 1
        dateselect = Weekday(TheDate)'获取日期的星期数
        'MsgBox (dateselect)
        Call weekcolor(dateselect, count)
        ActiveCell = TheDate
        TheDate = TheDate + 1
        ActiveCell.Offset(1, 0).Activate
    Wend
    cellscount = Cells(3, 3).End(xlDown).Row
    'MsgBox (cellscount)
    countscellset (cellscount)
    
End Sub
'遇到工作日将单元格标灰
Function weekcolor(dateselect, count)
  m = count
  If dateselect = 1 Or dateselect = 7 Then
    Range("c" & m + 2 & ":h" & m + 2).Interior.ColorIndex = 15
  End If
End Function
'设置单元格格式
Function countscellset(cellscount)
  t = cellscount
  Range("d3:" & "f" & t).NumberFormatLocal = "hh:mm:ss"
  Range("d3:" & "f" & t).Value = "00:00:00"
  Range("f3:" & "f" & t).Value = "01:00:00"

End Function