vba怎样使用当前日期 如何用VBA来控制excel中的自动当前日期
Excel \u600e\u4e48\u7528VBA\u4ee3\u7801\u8868\u793a\u65e5\u671f\u201c\u4eca\u5929\u201d1\u3001\u6253\u5f00\u684c\u9762\u4e0a\u7684Excel\u8868\u683c\uff0c\u5728\u65b0\u5efa\u4e00\u4e2a\u5de5\u4f5c\u6837\u8868\u3002
2\u3001\u63a5\u7740\u4f7f\u7528alt+f11\u7ec4\u5408\u5feb\u6377\u952e\u8fdb\u5165vbe\u7f16\u8f91\u5668\uff0c\u63d2\u5165\u4e00\u4e2a\u65b0\u7684\u6a21\u5757\u3002
3\u3001\u8f93\u5165\u4ee5\u4e0b\u4ee3\u7801\uff1aOption ExplicitSub ddt()Dim rq As DateDim lx As StringDim n As IntegerDim Msglx = "m"rq = InputBox("\u8bf7\u8f93\u5165\u4e00\u4e2a\u65e5\u671f")n = InputBox("\u8f93\u5165\u589e\u52a0\u6708\u7684\u6570\u76ee\uff1a")Msg = "\u65b0\u65e5\u671f:" &DateAdd(lx, n, rq)MsgBox MsgEnd Sub\u3002
4\u3001\u8fd9\u91cc\u5148\u5bf9\u53d8\u91cf\u505a\u58f0\u660e\uff0c\u5176\u4e2drq\u53d8\u91cf\u4e3a\u65e5\u671f\u7c7b\u578b\uff0clx\u4e3a\u5b57\u7b26\u4e32\uff0cn\u4e3a\u6574\u6570\u578b\u3002msg\u5e76\u6ca1\u6709\u8bbe\u7f6e\u5177\u4f53\u7c7b\u578b\uff0cvba\u4f1a\u5c06\u5176\u9ed8\u8ba4\u4e3a\u53ef\u53d8\u7c7b\u578bvariant\u3002
5\u3001\u8f93\u5165\u65e5\u671f\u5e76\u6ca1\u6709\u505a\u9519\u8bef\u7ea0\u6b63\uff0c\u6700\u597d\u8f93\u51652020/2/17\u8fd9\u6837\u7684\u683c\u5f0f\u65e5\u671f\u3002\u589e\u52a0\u7684\u6708\u6570\u662f\u8d4b\u503c\u7ed9\u4e86n\u8fd9\u4e2a\u53d8\u91cf\u3002
6\u3001\u6700\u540e\u7528msgbox\u6765\u8f93\u51fa\u6700\u540e\u7684\u7ed3\u679c\uff0c\u5c31\u5b8c\u6210\u4e86\u3002
\u4e0d\u6539\u53d8\u539f\u59cb\u8868\u683c\u6570\u636e\uff0c\u6253\u5370\u65f6\u81ea\u52a8\u6dfb\u52a0\u6253\u5370\u65e5\u671f\u3002
vba使用当前日期的方法是利用excel内置函数实现的。
1、excel中函数date的使用方法:
2、在vba中,需要先定义一个变量,然后把date赋值:
dim cuDate as string
cuDate = date
这样变量cuDate就获取到了date的值。
3、运行结果:
Sub test()
Dim cuDate As String
cuDate = Date
MsgBox cuDate
End Sub
不改变原始表格数据,打印时自动添加打印日期。
=date()
这样就可以引入当前日期。
.Replacement.Text = "日"
改为:.Replacement.Text = Day(date)
1.调用当天日期
Sub 当天日期()
Dim a As Date ‘给出当天日期
a = Format(Date, "yyyy-mm-dd")
End Sub
2.调用昨天的日期
目的:删除前天店铺库存、在途数量和订单数
sub 删除店铺库存()
If CDate(Application.Max(Range("j:j"))) < Format(Date - 1, "yyyy-mm-dd") Then range("要删除的范围”).delect‘如果前天店铺库存还在,删除
end sub
3.日期
Sub 日期()
Dim 昨天 As Date, 今天 As Date, 最大值 As Date
昨天 = Format(Date - 1, "yyyy-mm-dd")
今天 = Format(Date, "yyyy-mm-dd")
时间 = CDate(Now())
最大值 = CDate(Application.Max(Range("j:j")))
End Sub
绛旓細vba浣跨敤褰撳墠鏃ユ湡鐨勬柟娉曟槸鍒╃敤excel鍐呯疆鍑芥暟瀹炵幇鐨銆1銆乪xcel涓嚱鏁癲ate鐨勪娇鐢ㄦ柟娉曪細2銆佸湪vba涓紝闇瑕佸厛瀹氫箟涓涓彉閲忥紝鐒跺悗鎶奷ate璧嬪硷細dim cuDate as string cuDate = date 杩欐牱鍙橀噺cuDate灏辫幏鍙栧埌浜哾ate鐨勫笺3銆佽繍琛岀粨鏋滐細Sub test()Dim cuDate As String cuDate = Date MsgBox cuDate End Sub ...
绛旓細绗笁锛鐢娑堟伅妗嗚繑鍥褰撳墠鏃ユ湡鐨勫勾銆佹湀銆佹棩鍜屽綋鍓嶆椂闂寸殑鏃躲佸垎銆佺 Sub SmpYearHour()鈥樹互瀵硅瘽妗嗘樉绀哄綋鍓嶆棩鏈熺殑骞淬佹湀銆佹棩 MsgBox "褰撳墠鏃ユ湡锛" & Date & Chr(10) & _"骞达細" & Year(Date) & Chr(10) & _"鏈堬細" & Month(Date) & Chr(10) & _"鏃ワ細" & Day(Date)鈥樹互瀵硅瘽妗嗘樉绀...
绛旓細date涓哄綋鏃ョ殑鏃ユ湡锛岄氬父now涓烘棩鏈熸椂闂存牸寮忥紝鑰孌ATE 鍙幓鏃ユ湡鏍煎紡銆=Format("3/31/09", "mm-dd-yyyy")鍏充簬姝や緥涓鐢鍒扮殑Format鍑芥暟鐨勭浉鍏崇煡璇嗭細Format鍑芥暟 鏍规嵁鏈夋晥鐨勫懡鍚嶈〃杈惧紡鏉ュ鎸囧畾鐨勮〃杈惧紡杩涜鏍煎紡鍖栥侳ormat(expression[, format[, firstdayofweek[, firstweekofyear]]])Format 鍑芥暟鍏锋湁涓嬮潰鍑犱釜鍙...
绛旓細1銆佸湪寮鍙戝伐鍏锋爮閫夋嫨鎻掑叆-鍏朵粬鎺т欢锛屾墦寮鍏朵粬鎺т欢閫夋嫨妗嗐2銆佸湪閫夋嫨妗嗕腑閫夋嫨calendar control8.0锛岀‘瀹銆3銆佸湪閫傚綋鐨勪綅缃粯鍒舵棩鍘嗭紝骞跺皢楂樺害鍜屽搴﹂傚綋璋冩暣銆傝繖鏃剁殑鏃ュ巻鎺т欢杩樻瘮杈冨師濮嬶紝涓嶄細鐐瑰嚮鏃ユ湡鍚庤嚜鍔ㄩ殣钘忔棩鍘嗗苟鍦ㄥ崟鍏冩牸鍐呭~鍏ユ棩鏈燂紝杩欐椂灏遍渶瑕佹坊鍔犺嚜宸辩殑浠g爜銆4銆佸湪璁捐妯″紡涓嬪弻鍑绘棩鍘嗐傜偣鍑昏璁℃ā寮...
绛旓細1銆佹墦寮妗岄潰涓婄殑Excel琛ㄦ牸锛屽湪鏂板缓涓涓伐浣滄牱琛ㄣ2銆佹帴鐫浣跨敤alt+f11缁勫悎蹇嵎閿繘鍏be缂栬緫鍣紝鎻掑叆涓涓柊鐨勬ā鍧椼3銆佽緭鍏ヤ互涓嬩唬鐮侊細Option ExplicitSub ddt()Dim rq As DateDim lx As StringDim n As IntegerDim Msglx = "m"rq = InputBox("璇疯緭鍏ヤ竴涓鏃ユ湡")n = InputBox("杈撳叆澧炲姞鏈堢殑...
绛旓細1銆侀鍏堟墦寮涓涓狤XCEL琛ㄦ牸锛岃緭鍏ヤ竴鍒鏃ユ湡銆2銆侀変腑鏃ユ湡鍒楋紝鐐瑰嚮鑿滃崟鏍忎笂鈥滄彃鍏-鏁版嵁閫忚琛ㄢ濄3銆佺‘璁ゅ脊鍑虹殑瀵硅瘽妗嗐傚彸渚у皬绐楀彛涓婂嬀閫夋棩鏈燂紝宸﹁竟鏁版嵁閫忚琛ㄤ笂鏄剧ず鍑烘棩鏈熴4銆佺劧鍚庨紶鏍囩Щ鍔ㄥ埌閫忚琛ㄤ笂浠绘剰涓涓棩鏈熶笂锛岀偣鍑婚紶鏍囧彸閿紝閫夋嫨鈥滅粍鍚堚濄5銆佸脊鍑虹殑瀵硅瘽妗嗕笂閫夋嫨鏈堛6銆佺‘璁ゅ璇濇鍚庯紝閫忚琛ㄦ寜...
绛旓細鐢鍗曞厓鏍间簨浠 褰撴煇鍗曞厓鏍煎綍鍏ユ暟鎹椂锛屾棩鏈熷崟鍏冩牸涓褰撳墠鏃ユ湡锛堝浜嗭紝鏄椂闂村氨鏃堕棿锛屾槸鏃ユ湡灏辨棩鏈燂級濡傦紝鍦ˋ1鍗曞厓鏍煎綍鍏ユ暟鎹紝鍦˙1鍗曞厓鏍艰嚜鍔ㄥ啓鍏ュ綋鍓嶆棩鏈熴侾rivate Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$A$1" Then Range("B1") = Date End If End Sub ...
绛旓細鍙互杩欎箞鍐欙細Sub Macro1()Dim dt As Date dt = Format(Date, "yyyy-mm-dd")MsgBox dt End Sub
绛旓細Sub 褰撳墠鏃ユ湡()Dim idate As Date idate = Date ActiveSheet.Range("A1").Value = idate End Sub
绛旓細Private Sub Workbook_Open()If [a1] = "" Then [a1] = Date Else Exit Sub End If End Sub 鍦ㄥ伐浣滆〃鏍囩锛屽彸閿紝鏌ョ湅浠g爜銆傚弻鍑籘hisworkbook锛屽緱鍒颁竴涓唬鐮佺獥鍙 灏嗚繖娈典唬鐮佺矘璐村埌Thisworkbook鐨勪唬鐮佺獥鍙e唴鍗冲彲 a1鏄竴涓崟鍏冩牸鍦板潃 瑕佹敼鍙鏃ユ湡鐨勫瓨鏀惧湴鍧锛岃灏哸1鏇存敼涓哄叾瀹冨崟鍏冩牸鍦板潃鍗冲彲 ...