excelVBA注释和赋值语句
1)注释语句是用来说明程序中某些语句的功能和作用;VBA中有两种方法标识为注释语句。单引号 ’ ;如:’定义全局变量;可以位于别的语句之尾,也可单独一行
Rem ;如:Rem定义全局变量;只能单独一行
2)赋值语句是进行对变量或对象属性赋值的语句,采用赋值号 =,如X=123:Form1.caption=”我的窗口”
对对象的赋值采用:set myobject=object 或 myobject:=object
绛旓細Sub abc()Dim r As LongFor r = 1 To 1000 If Range("A" & r) = 0 Then Range("F" & r).FormulaR1C1 = "RC1&RC2&RC3"Next rEnd Sub璇蜂慨鏀瑰惊鐜ぇ灏忎负鎵闇鐨勬暟鍊笺傚彟澶栵紝[A1]=0鏃讹紝[F1]=A2&B2&C2锛岃繕鏄=A1&B1&C1 锛熶笂闈㈢殑浠g爜鏄 =A1&B1&C1 ...
绛旓細浣犺繖涓儏鍐靛簲璇ユ槸 涓嶅瓨鍦 sheet4 ,鎴戞祴璇曚簡 濡傛灉瀛樺湪鐨勬儏鍐典笅锛 浠g爜鏄彲浠ヨ繍琛岀殑 浣犺繖涓〃閲岄潰鐨剆heet4 璺熶綘浠g爜涓殑sheet4,鍏跺疄涓嶆槸涓鍥炰簨锛 浣犳敼鎴 sheets("琛ㄥ悕") 杩欑妯″紡锛屽簲璇ュ氨娌¢棶棰樹簡 浣犵湅杩欎釜鍥句腑锛 sheet4 瀹為檯鏄〃閲岄潰鐨剆heet1 杩欑琛ㄧず鏂规硶鏄寜鐓 琛ㄧ殑鎺掑簭鏉ユ寚瀹氳〃鐨 ...
绛旓細鍦VBA涓紝鍙敤涓嬪垪璧嬪艰鍙锛歊ange("A1") = "abcd"鍗筹細绗竴琛岀殑A鍒楃殑鍊间负锛氣渁bcd鈥
绛旓細Suba()'褰撳墠鐨勮〃鏍糄imsht1AsWorksheet'鎵撳紑鐨excel琛―imsht2AsWorksheet'鍙橀噺sht1璧嬪Setsht1=ActiveSheet'鍙橀噺sht2璧嬪硷紝鎵撳紑宸ヤ綔琛⊿etsht2=Workbooks.Open("Filename").ActiveSheet'婵娲诲綋鍓嶅伐浣滆〃sht1.Activate'寮曠敤鎵撳紑鐨別xcel琛ㄦ牸涓殑鏁版嵁Cells(j,1).Value=sht2.Cells(i,1).ValueEndSub ...
绛旓細璧嬪杩愮畻绗= 姝ゅ锛屾湁浜涚▼搴忎腑鍙兘杩樹細鍖呮嫭VBA鍑芥暟銆佹潯浠舵垨寰幆璇彞锛岃繖浜涢兘鏄粍鎴怴BA绋嬪簭鐨勫厓绱犮傚湪鍚庣画鐨勭郴鍒椾腑锛屾垜浠皢瀵硅繖浜涘唴瀹归愪竴瑙c俒澶嶄範]杩愯绋嬪簭浠g爜 鎸夌収Excel VBA瑙h锛3锛夛細VBA浠g爜涔嬪涓墍杩扮殑鎵ц浠g爜锛屽彲寰楀埌涓嬫柟鎵绀虹殑缁撴灉銆傝鏄庯細澶у鍙互鍙傜収Excel VBA瑙h锛3锛夛細VBA浠g爜涔嬪涓殑鈥...
绛旓細Set 璇彞 璇峰弬闃 绀轰緥 鐗规 灏嗗璞″紩鐢ㄨ祴缁欏彉閲忔垨灞炴с傝娉 Set objectvar = {[New]objectexpression | Nothing} Set 璇彞鐨勮娉曞寘鍚笅闈㈤儴鍒嗭細閮ㄥ垎 鎻忚堪 objectvar 蹇呴渶鐨勩傚彉閲忔垨灞炴х殑鍚嶇О锛岄伒寰爣鍑嗗彉閲忓懡鍚嶇害瀹氥侼ew 鍙夌殑銆傞氬父鍦ㄥ0鏄庢椂浣跨敤 New锛屼互渚垮彲浠ラ殣寮忓垱寤哄璞°傚鏋 New 涓 S...
绛旓細Private Sub Worksheet_Activate() '宸ヤ綔琛ㄦ縺娲讳簨浠 Set d = CreateObject("Scripting.Dictionary") '璧嬪D涓哄瓧鍏稿璞 arr = Sheet1.UsedRange For i = 2 To UBound(arr)If arr(i, 1) <> "" Then d(arr(i, 1)) = "" 'D澧炲姞鍏抽敭瀛 Next With Range("B2").Validation .Delete .Add 3...
绛旓細statements 鍙夊弬鏁般傝鎵ц鍦 object 涓婄殑涓鏉℃垨澶氭潯璇彞銆傝鏄 With 璇彞鍙互瀵规煇涓璞℃墽琛屼竴绯诲垪鐨勮鍙ワ紝鑰屼笉鐢ㄩ噸澶嶆寚鍑哄璞$殑鍚嶇О銆備緥濡傦紝瑕佹敼鍙樹竴涓璞$殑澶氫釜灞炴э紝鍙互鍦 With 鎺у埗缁撴瀯涓姞涓婂睘鎬х殑璧嬪艰鍙锛岃繖鏃跺欏彧鏄紩鐢ㄥ璞′竴娆¤屼笉鏄湪姣忎釜灞炴ц祴鍊兼椂閮借寮曠敤瀹冦備笅闈㈢殑渚嬪瓙鏄剧ず浜嗗浣曚娇鐢...
绛旓細涓嬫柇鐐硅皟璇曪細鍦 If Workbooks(j).Name = "鑱屽伐妗f.xls" Then鎵鍦ㄨ锛屾寜蹇嵎F9涓嬫柇鐐癸紝鍚姩鑴氭湰涔嬪悗锛岃繍琛屽埌鏂偣澶勶紝閫変腑鈥 Workbooks(j).Name鈥濆苟鐐瑰嚮鍙抽敭锛岄夋嫨娣诲姞鐨勭洃瑙嗭細鎸塅8渚濇瑙傚療鑾峰緱鍊兼槸鍚︿负鈥滆亴宸ユ。妗.xls鈥濆嵆鍙
绛旓細1銆佸湪Excel鐨VBA涓紝澹版槑鍙橀噺璇彞涓猴細Dim 鍙橀噺鍚 As 鍙橀噺绫诲瀷銆2銆丼et 璇彞鏄粰瀵硅薄鍨嬪彉閲璧嬪艰鍙銆3銆佷緥濡傦細Dim SH As worksheet 杩欏彞鏄0鏄庝簡涓涓彉閲忓悕涓篠H鐨勫伐浣滆〃鍨嬪彉閲忋4銆佽孲et SH = Sheets("璁剧疆")杩欏彞鏄妸宸ヤ綔琛ㄥ悕涓"璁剧疆"鐨勫伐浣滆〃鎸囧畾缁欏彉閲廠H銆