Excel VBA常用代碼總結(jié)1

《Excel VBA常用代碼總結(jié)1》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫(kù)。

1、ExcelVBA常用代碼總結(jié)1·改變背景色Range("A1").Interior.ColorIndex=xlNone?ColorIndex一覽·改變文字顏色Range("A1").Font.ColorIndex=1·獲取單元格Cells(1,2)Range("H7")·獲取范圍Range(Cells(2,3),Cells(4,5))Range("a1:c3")'用快捷記號(hào)引用單元格Worksheets("Sheet1").[A1:B5]·選中某sheetSetNewSheet=Sheets("sheet1")NewSheet.

2、Select·選中或激活某單元格'“Range”對(duì)象的的Select方法可以選擇一個(gè)或多個(gè)單元格，而Activate方法可以指定某一個(gè)單元格為活動(dòng)單元格。'下面的代碼首先選擇A1:E10區(qū)域，同時(shí)激活D4單元格：Range("a1:e10").SelectRange("d4:e5").Activate'而對(duì)于下面的代碼：Range("a1:e10").SelectRange("f11:g15").Activate'由于區(qū)域A1:E10和F11:G15沒(méi)有公共區(qū)域，將最終選擇F11:G15，并激活F11單元格?！か@得文檔的路徑和文

3、件名ActiveWorkbook.Path　　　　'路徑ActiveWorkbook.Name　　　'名稱ActiveWorkbook.FullName　'路徑＋名稱'或?qū)ctiveWorkbook換成thisworkbook·隱藏文檔Application.Visible=False·禁止屏幕更新Application.ScreenUpdating=False·禁止顯示提示和警告消息Application.DisplayAlerts=False·文件夾做成strPath="C:temp"MkDirstrPath·狀態(tài)欄文

4、字表示Application.StatusBar="計(jì)算中"·雙擊單元格內(nèi)容變換PrivateSubWorksheet_BeforeDoubleClick(ByValTargetAsRange,CancelAsBoolean)If(Target.Cells.Row>=5AndTarget.Cells.Row<=8)ThenIfTarget.Cells.Value="●"ThenTarget.Cells.Value=""ElseTarget.Cells.Value="●"EndIfCancel=TrueEndIfEndSub·文件

5、夾選擇框方法1SetobjShell=CreateObject("Shell.Application")SetobjFolder=objShell.BrowseForFolder(0,"文件",0,0)IfNotobjFolderIsNothingThenpath=objFolder.self.Path&""endifSetobjFolder=NothingSetobjShell=Nothing·文件夾選擇框方法2（推薦）PublicFunctionChooseFolder()AsStringDimdlgOpenAsFileD

6、ialogSetdlgOpen=Application.FileDialog(msoFileDialogFolderPicker)WithdlgOpen.InitialFileName=ThisWorkbook.path&""If.Show=-1ThenChooseFolder=.SelectedItems(1)EndIfEndWithSetdlgOpen=NothingEndFunction'使用方法例：DimpathAsStringpath=ChooseFolder()Ifpath<>""ThenMsgBox"openfo

7、lder"EndIf·文件選擇框方法PublicFunctionChooseOneFile(OptionalTitleStrAsString="Pleasechooseafile",OptionalTypesDecAsString="*.*",OptionalExtenAsString="*.*")AsStringDimdlgOpenAsFileDialogSetdlgOpen=Application.FileDialog(msoFileDialogFilePicker)WithdlgOpen.Title=TitleStr.Fi

8、lters.Clear.Filters.AddTypesDec,Exten.AllowMultiSelect=False.InitialFileName=ThisWorkbook.PathIf.Show=-1Then'.AllowMultiSelect=True