| 订阅 | 在线投稿
分享
 
 
当前位置: 王朝网络 >> vb >> ge zhong Excel VBA de ming ling 1

ge zhong Excel VBA de ming ling 1

2008-09-02 06:53:53 编辑來源:互联网 评论
 
 
 
本文为【各种Excel VBA的命令1】的拼音翻译版
  ben shi li wei she zhi gong zuo biao mi ma
  ActiveSheet.Protect Password:=641112 ' bao hu gong zuo biao bing she zhi mi ma
  ActiveSheet.Unprotect Password:=641112 ' che xiao gong zuo biao bao hu bing qu xiao mi ma
  ' ben shi li bao cun dang qian huo dong gong zuo bao de fu ben 。
  ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS"
  ' ben shi li tong guo jiang Saved shu xing she wei True lai guan bi bao han ben duan dai ma de gong zuo bao , bing fang qi dui gai
  gong zuo bao de ren he geng gai 。
  ThisWorkbook.Saved = True
  ThisWorkbook.Close
  ' ben shi li dui zi dong chong xin ji suan gong neng jin xing she zhi , shi Microsoft Excel bu dui di yi zhang gong zuo biao zi
  dong jin xing chong xin ji suan 。
  Worksheets(1).EnableCalculation = False
  ' xia shu guo cheng da kai C pan shang ming wei MyFolder de wen jian jia zhong de MyBook.xls gong zuo bao 。
  Workbooks.Open ("C:\MyFolder\MyBook.xls")
  ' ben shi li xian shi huo dong gong zuo bao zhong gong zuo biao sheet1 shang dan yuan ge A1 zhong de zhi 。
  MsgBox Worksheets("Sheet1").Range("A1").Value
  ben shi li xian shi huo dong gong zuo bao zhong mei ge gong zuo biao de ming cheng
  For Each ws In Worksheets
  MsgBox ws.Name
  Next ws
  ben shi li xiang huo dong gong zuo bao tian jia xin gong zuo biao , bing she zhi gai gong zuo biao de ming cheng ?
  Set NewSheet = Worksheets.Add
  NewSheet.Name = "current Budget"
  ben shi li jiang xin jian de gong zuo biao yi dao gong zuo bao de mo yi
  'Private Sub Workbook_NewSheet(ByVal Sh As Object)
  Sh.Move After:=Sheets(Sheets.Count)
  End Sub
  ben shi li jiang xin jian gong zuo biao yi dao gong zuo bao de mo yi
  'Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _
  ByVal Sh As Object)
  Sh.Move After:=Wb.Sheets(Wb.Sheets.Count)
  End Sub
  ben shi li xin jian yi zhang gong zuo biao , ran hou zai di yi lie zhong lie chu huo dong gong zuo bao zhong de suo you gong zuo biao de ming cheng 。
  Set NewSheet = Sheets.Add(Type:=xlWorksheet)
  For i = 1 To Sheets.Count
  NewSheet.Cells(i, 1).Value = Sheets(i).Name
  Next i
  ben shi li jiang di shi xing yi dao chuang kou de zui shang mian ?
  Worksheets("Sheet1").Activate
  ActiveWindow.ScrollRow = 10
  dang ji suan gong zuo bao zhong de ren he gong zuo biao shi , ben shi li dui di yi zhang gong zuo biao de A1:A100 qu yu jin xing pai xu
  。
  'Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
  With Worksheets(1)
  .Range("a1:a100").Sort Key1:=.Range("a1")
  End With
  End Sub
  ben shi li xian shi gong zuo biao Sheet1 de da yin yu lan 。
  Worksheets("Sheet1").PrintPreview
  ben shi li bao cun dang qian huo dong gong zuo bao ?
  ActiveWorkbook.Save
  ben shi li bao cun suo you da kai de gong zuo bao , ran hou guan bi Microsoft Excel。
  For Each w In Application.Workbooks
  w.Save
  Next w
  Application.Quit
  xia li zai huo dong gong zuo bao de di yi zhang gong zuo biao qian mian tian jia liang zhang xin de gong zuo biao ?
  Worksheets.Add Count:=2, Before:=Sheets(1)
  ben shi li she zhi 15 miao hou yun xing my_Procedure guo cheng , cong xian zai kai shi ji shi 。
  Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"
  ben shi li she zhi my_Procedure zai xia wu 5 dian kai shi yun xing 。
  Application.OnTime TimeValue("17:00:00"), "my_Procedure"
  ben shi li che xiao qian yi ge shi li dui OnTime de she zhi 。
  Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
  Procedure:="my_Procedure", Schedule:=False
  mei dang gong zuo biao chong xin ji suan shi , ben shi li jiu tiao zheng A lie dao F lie de kuan du 。
  'Private Sub Worksheet_Calculate()
  Columns("A:F").AutoFit
  End Sub
  ben shi li shi huo dong gong zuo bao zhong de ji suan jin shi yong xian shi de shu zi jing du 。
  ActiveWorkbook.PrecisionAsDisplayed = True
  ben shi li jiang gong zuo biao Sheet1 shang de A1:G37 qu yu jian xia , bing fang ru jian tie ban 。
  Worksheets("Sheet1").Range("A1:G37").Cut
  Calculate fang fa
  ji suan suo you da kai de gong zuo bao 、 gong zuo bao zhong de yi zhang te ding de gong zuo biao huo zhe gong zuo biao zhong zhi ding qu yu de dan yuan
  ge , ru xia biao suo shi :
  ' yao ji suan ' yi zhao ben shi li
  suo you da kai de gong zuo bao ' Application.Calculate ( huo zhi shi Calculate
  )
  zhi ding gong zuo biao ' ji suan zhi ding gong zuo biao Sheet1 Worksheets
  ("Sheet1").Calculate
  zhi ding qu yu 'Worksheets(1).Rows(2).Calculate
  ben shi li dui zi dong chong xin ji suan gong neng jin xing she zhi , shi Microsoft Excel bu dui di yi zhang gong zuo biao zi dong
  jin xing chong xin ji suan 。
  Worksheets(1).EnableCalculation = False
  ben shi li ji suan Sheet1 yi yong qu yu zhong A lie 、B lie he C lie de gong shi 。
  Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate
  ben shi li geng xin dang qian huo dong gong zuo bao zhong de suo you lian jie ?
  ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
  ben shi li she zhi di yi zhang gong zuo biao de gun dong qu yu ?
  Worksheets(1).ScrollArea = "a1:f10"
  ben shi li xin jian yi ge gong zuo bao , ti shi yong hu shu ru wen jian ming , ran hou bao cun gai gong zuo bao 。
  Set NewBook = Workbooks.Add
  Do
  fName = Application.GetSaveAsFilename
  Loop Until fName False
  NewBook.SaveAs Filename:=fName
  ben shi li da kai Analysis.xls gong zuo bao , ran hou yun xing Auto_Open hong 。
  Workbooks.Open "ANALYSIS.XLS"
  ActiveWorkbook.RunAutoMacros xlAutoOpen
  ben shi li dui huo dong gong zuo bao yun xing Auto_Close hong , ran hou guan bi gai gong zuo bao 。
  With ActiveWorkbook
  .RunAutoMacros xlAutoClose
  .Close
  End With
  zai ben shi li zhong ,Microsoft Excel xiang yong hu xian shi huo dong gong zuo bao de lu jing he wen jian ming cheng 。
  'Sub UseCanonical()
  Display the full path to user.
  MsgBox ActiveWorkbook.FullNameURLEncoded
  End Sub
  ben shi li xian shi dang qian gong zuo bao de lu jing ji wen jian ming ( jia ding shang wei bao cun ci gong zuo bao )。
  MsgBox ActiveWorkbook.FullName
  ben shi li guan bi Book1.xls, bing fang qi suo you dui ci gong zuo bao de geng gai 。
  Workbooks("BOOK1.XLS").Close SaveChanges:=False
  ben shi li guan bi suo you da kai de gong zuo bao 。 ru guo mou ge da kai de gong zuo bao you gai bian ,Microsoft Excel
  jiang xian shi xun wen shi fou bao cun geng gai de dui hua kuang he xiang ying ti shi 。
  Workbooks.Close
  ben shi li zai da yin zhi qian dui dang qian huo dong gong zuo bao de suo you gong zuo biao chong xin ji suan ?
  'Private Sub Workbook_BeforePrint(Cancel As Boolean)
  For Each wk In Worksheets
  wk.Calculate
  Next
  End Sub
  ben shi li dui cha xun biao yi zhong de di yi lie shu ju jin xing hui zong , bing zai shu ju qu yu xia fang xian shi di yi lie shu ju de zong
  he 。
  Set c1 = Sheets("sheet1").QueryTables(1).ResultRange.Columns(1)
  c1.Name = "Column1"
  c1.End(xlDown).Offset(2, 0).Formula = "=sum(Column1)"
  ben shi li qu xiao huo dong gong zuo bao zhong de suo you geng gai ?
  ActiveWorkbook.RejectAllChanges
  ben shi li zai shang ye wen ti zhong shi yong gui hua qiu jie han shu , yi shi zong li run da dao zui da zhi 。SolverSave han shu
  jiang dang qian wen ti bao cun dao huo dong gong zuo biao shang de mou yi qu yu 。
  Worksheets("Sheet1").Activate
  SolverReset
  SolverOptions Precision:=0.001
  SolverOK SetCell:=Range("TotalProfit"), _
  MaxMinVal:=1, _
  ByChange:=Range("C4:E6")
  SolverAdd CellRef:=Range("F4:F6"), _
  Relation:=1, _
  FormulaText:=100
  SolverAdd CellRef:=Range("C4:E6"), _
  Relation:=3, _
  FormulaText:=0
  SolverAdd CellRef:=Range("C4:E6"), _
  Relation:=4
  SolverSolve UserFinish:=False
  SolverSave SaveArea:=Range("A33")
  ben shi li yin cang Chart1、Chart3 he Chart5。
  Charts(Array("Chart1", "Chart3", "Chart5")).Visible = False
  dang ji huo gong zuo biao shi , ben shi li dui A1:A10 qu yu jin xing pai xu 。
  'Private Sub Worksheet_Activate()
  Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending
  End Sub
  ben shi li geng gai Microsoft Excel lian jie 。
  ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _
  "c:\excel\book2.xls", xlExcelLinks
  ben shi li qi yong shou bao hu de gong zuo biao shang de zi dong shai xuan jian tou ?
  ActiveSheet.EnableAutoFilter = True
  ActiveSheet.Protect contents:=True, userInterfaceOnly:=True
  ben shi li jiang huo dong gong zuo bao she wei zhi du ?
  ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly
  ben shi li shi gong xiang gong zuo bao mei san fen zhong zi dong geng xin yi ci ?
  ActiveWorkbook.AutoUpdateFrequency = 3
  xia shu Sub guo cheng qing chu huo dong gong zuo bao zhong Sheet1 shang de suo you dan yuan ge de nei rong 。
  'Sub ClearSheet()
  Worksheets("Sheet1").Cells.ClearContents
  End Sub
  ben shi li dui suo you gong zuo bao dou guan bi gun dong tiao ?
  Application.DisplayScrollBars = False
  ru guo ju you mi ma bao hu de gong zuo bao de wen jian shu xing mei you jia mi , ze ben shi li she zhi zhi ding gong zuo bao de mi ma jia
  mi xuan xiang 。
  'Sub SetPasswordOptions()
  With ActiveWorkbook
  If .PasswordEncryptionProvider "Microsoft RSA SChannel
  Cryptographic Provider" Then
  .SetPasswordEncryptionOptions _
  PasswordEncryptionProvider:="Microsoft RSA SChannel
  Cryptographic Provider", _
  PasswordEncryptionAlgorithm:="RC4", _
  PasswordEncryptionKeyLength:=56, _
  PasswordEncryptionFileProperties:=True
  End If
  End With
  End Sub
  zai ben shi li zhong , ru guo huo dong gong zuo bao bu neng jin xing xie bao hu , na me Microsoft Excel she zhi zi fu chuan
  mi ma yi zuo wei huo dong gong zuo bao de xie mi ma 。
  'Sub UseWritePassword()
  Dim strPassword As String
  strPassword = "secret"
  ' Set password to a string if allowed.
  If ActiveWorkbook.WriteReserved = False Then
  ActiveWorkbook.WritePassword = strPassword
  End If
  End Sub
  zai ben shi li zhong ,Microsoft Excel da kai ming wei Password.xls de gong zuo bao , she zhi ta de mi ma
  , ran hou guan bi gai gong zuo bao 。 ben shi li jia ding ming wei Password.xls de wen jian wei yu C:\ qu dong qi shang 。
  'Sub UsePassword()
  Dim wkbOne As Workbook
  Set wkbOne = Application.Workbooks.Open("C:\Password.xls")
  wkbOne.Password = "secret"
  wkbOne.Close
  ' zhu yi Password shu xing ke du bing fan hui “********”。
  End Sub
  ben shi li jiang Book1.xls de dang qian chuang kou geng gai wei xian shi gong shi 。
  Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate
  ActiveWindow.DisplayFormulas = True
  ' ben shi li jie shou huo dong gong zuo bao zhong de suo you geng gai ?
  ActiveWorkbook.AcceptAllChanges
  ben shi li xian shi huo dong gong zuo bao de lu jing he ming cheng
  Sub UseCanonical()
  MsgBox ' xiao xi kuang
  [b7] = ActiveWorkbook.FullName ' dang qian gong zuo bao
  [b8] = ActiveWorkbook.FullNameURLEncoded ' huo dong gong zuo bao
  End Sub
  ben shi li xian shi Microsoft Excel qi dong wen jian jia de wan zheng lu jing 。
  MsgBox Application.StartupPath
  Activate shi jian
  ji huo yi ge gong zuo bao 、 gong zuo biao 、 tu biao huo qian ru tu biao shi chan sheng ci shi jian 。
  dang ji huo gong zuo biao shi , ben shi li dui A1:A10 qu yu jin xing pai xu 。
  Private Sub Worksheet_Activate()
  Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending
  End Sub
  Calculate shi jian
  dui yu Worksheet dui xiang , zai dui gong zuo biao jin xing chong xin ji suan zhi hou chan sheng ci shi jian
  mei dang gong zuo biao chong xin ji suan shi , ben shi li jiu tiao zheng A lie dao F lie de kuan du 。
  Private Sub Worksheet_Calculate()
  Columns("A:F").AutoFit
  End Sub
  ben shi li xiang huo dong gong zuo bao tian jia xin gong zuo biao , bing she zhi gai gong zuo biao de ming cheng 。
  Set newSheet = Worksheets.Add
  newSheet.Name = "current Budget"
  ben shi li guan bi gong zuo bao Book1.xls, dan bu ti shi yong hu bao cun suo zuo geng gai 。Book1.xls zhong de suo you
  geng gai dou bu hui bao cun 。
  Application.DisplayAlerts = False
  Workbooks("BOOK1.XLS").Close
  Application.DisplayAlerts = True
  shi li xian shi mei yi ge ke yong jia zai hong de lu jing ji wen jian ming 。
  For Each a In AddIns
  MsgBox a.FullName
  Next a
  ChDir yu ju
  gai bian dang qian de mu lu huo wen jian jia 。
  ChDir path
  zai Power Macintosh zhong , mo ren qu dong qi zong shi gai wei zai path yu ju zhong zhi ding de qu dong qi 。 wan zheng
  lu jing zhi ding you juan biao ming kai shi , xiang dui lu jing you mao hao (:) kai shi . ChDir ke yi bian ren lu jing zhong zhi ding de
  bie ming :
  ChDir "MacDrive:Tmp" ' zai Macintosh zhong
  ben shi li xian shi dang qian lu jing fen ge fu 。
  MsgBox "The path separator character is " & _
  Application.PathSeparator
  Move fang fa
  jiang yi ge zhi ding de wen jian huo wen jian jia cong yi ge di fang yi dong dao ling yi ge di fang 。
  yu fa
  object.Move destination
  Move fang fa yu fa you ru xia ji bu fen :
  bu fen miao shu
  object bi xu de 。 shi zhong shi yi ge File huo Folder dui xiang de ming zi 。
  destination bi xu de 。 wen jian huo wen jian jia yao yi dong dao de mu biao 。 bu yuan xu you tong pei fu 。
  CreateFolder fang fa
  chuang jian yi ge wen jian jia 。
  yu fa
  object.CreateFolder(foldername)
  reateFolder fang fa you ru xia ji bu fen :
  bu fen miao shu
  object bi xu de 。 shi zhong shi yi ge FileSystemObject de ming zi 。
  foldername bi xu de 。 zi fu chuan biao da shi , ta biao shi chuang jian de wen jian jia 。
  ben shi li shi yong MkDir yu ju lai chuang jian mu lu huo wen jian jia 。 ru guo mei you zhi ding qu dong qi , xin mu lu huo wen jian
  jia jiang hui jian zai dang qian qu dong qi zhong 。
  MkDir "MYDIR" ' jian li xin de mu lu huo wen jian jia 。
  Name yu ju shi li
  ben shi li shi yong Name yu ju lai geng gai wen jian de ming cheng 。 shi li zhong jia she suo you shi yong dao de mu lu huo wen jian jia dou
  yi cun zai 。 zai Macintosh zhong , mo ren qu dong qi ming cheng shi “HD” bing qie lu jing bu fen you mao hao qu dai
  fan xie xian ge kai 。
  Dim OldName, NewName
  OldName = "OLDFILE": NewName = "NEWFILE" ' ding yi wen jian ming 。
  Name OldName As NewName ' geng gai wen jian ming 。
  OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
  Name OldName As NewName ' geng gai wen jian ming , bing yi dong wen jian 。
  ben shi li she zhi ti huan qi dong wen jian jia 。
  Application.AltStartupPath = "C:\EXCEL\MACROS"
  FolderExists fang fa
  ru guo zhi ding de wen jian jia cun zai fan hui True, bu cun zai fan hui False。
  yu fa
  object.FolderExists(folderspec)
  ben shi li zai dan yuan ge zhong qi yong bian ji 。
  Application.EditDirectlyInCell = True
  cheng xu shuo ming :
  ji zhong yong VBA zai dan yuan ge shu ru shu ju de fang fa :
  Public Sub Writes()
  1-- 2 fang fa , zui jian dan zai "[ ]" zhong shu ru dan yuan ge ming cheng 。
  1 [A1] = 100 ' zai A1 dan yuan ge shu ru 100。
  2 [A2:A4] = 10 ' zai A2:A4 dan yuan ge shu ru 10。
  3-- 4 fang fa , cai yong Range(" "), " " zhong shu ru dan yuan ge ming cheng 。
  3 Range("B1") = 200 ' zai B1 dan yuan ge shu ru 200。
  4 Range("C1:C3") = 300 ' zai C1:C3 dan yuan ge shu ru 300。
  5-- 6 fang fa , cai yong Cells(Row,Column),Row shi dan yuan ge xing shu ,Column shi dan yuan ge lan shu 。
  5 Cells(1, 4) = 400 ' zai D1 dan yuan ge shu ru 400。
  6 Range(Cells(1, 5), Cells(5, 5)) = 50 ' zai E1:E 5 dan yuan ge shu ru 50。
  End Sub
  VBALesson3 cheng xu shuo ming :
  ru he li yong Worksheet_SelectionChange shu ru shu ju de fang fa 。
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Target = 100
  End Sub
  VBALesson4 cheng xu shuo ming :
  ru he li yong Worksheet_SelectionChange zai xian ding de dan yuan ge shu ru shu ju de fang fa 。
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Row >= 2 And Target.Column = 2 Then
  Target = 100
  End If
  End Sub
  VBALesson5 cheng xu shuo ming :
  bi jiao Worksheet_SelectionChange() yu yong an niu CommandButton1_Click() lai zhi xing
  cheng xu er zhe de fang fa yu xie fa you he bu tong 。
  Worksheet_SelectionChange() shi jian
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Row >= 2 And Target.Column = 2 Then
  Target = 100
  End If
  End Sub
  an chou CommandButton1_Click()
  Private Sub CommandButton1_Click()
  If ActiveCell.Row >= 2 And ActiveCell.Column >= 3 Then
  ActiveCell = 100
  End If
  End Sub
  er zhe zhi xing fang fa zui da de di fang , zai yu Worksheet_SelectionChange() shi zi dong de , ni bu yong
  le jie ta shi zen me wan cheng gong zuo de 。
  an niu CommandButton1_Click() shi ren gong de , bi SelectionChange() duo yi dao shou xu ,
  jiu shi yao qu an na jie niu , cheng xu cai hui zhi xing 。
  SelectionChange() you yi ge shen shu Target ke yong ;CommandButton1_Click () mei you 。
  suo yi wo men yao yong ActiveCell nei ding han shu lai qu dai Target,ActiveCell yu Target zui da de
  bu tong dian ta zhi neng zhi ding yi ge dan yuan ge 。
  jiu shi ni xuan qu duo ge dan yuan ge ye zhi you zui shang mian de dan yuan ge hui jia shang shu ju ; yong Selection qu dai
  ActiveCell, yong fa jiu gen Target yi yang le 。
  VBALesson 6 cheng xu shuo ming :
  wan zheng de If...Then ┅ End luo ji pan duan shi 。
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Row >= 2 And Target.Column = 2 Then
  Target = 200
  ElseIf Target.Row >= 2 And Target.Column = 3 Then
  Target = 300
  ElseIf Target.Row >= 2 And Target.Column = 2 Then
  Target = 400
  Else
  Target = 500
  End If
  End Sub
  zhe shi ge wan zheng de If luo ji pan duan shi , yi si shi shuo , jia ru If hou de pan duan shi tiao jian cheng li de hua , jiu
  zhi xing di er tiao cheng xu , fou ze jia ru ElseIf hou de pan duan shi tiao jian cheng li de hua , jiu zhi xing di si tiao cheng xu
  , fou ze jia ru ling yi ge ElseIf hou de pan duan shi tiao jian cheng li de hua , jiu zhi xing di liu tiao cheng xu 。
  Else de yi si shi shuo , jia ru yi shang tiao jian dou bu cheng li de hua , jiu zhi xing di ba tiao cheng xu 。
  ta de zhi xing fang shi shi jia ru IF de tiao jian cheng li de hua , jiu bu zhi xing qi ta ElseIf ji Else de luo ji pan
  duan shi , jia ru If hou de tiao jian bu cheng li de hua cai hui zhi xing ElseIf huo Else luo ji pan duan shi 。 di er
  ge ElseIf hou de tiao jian yin wei yu IF hou de tiao jian yi yang , suo yi zhe ge pan duan shi hou mian de Target=400
  jiang shi yong yuan wu fa zhi xing dao de cheng xu 。
  VBALesson 7 cheng xu shuo ming ∶ wo men wei shen me yao yong bian shu 。
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i , j As Integer
  Dim k As Range
  i = Target.Row
  j = Target.Column
  Set k = Target
  If i >= 2 And j = 2 Then
  k = 200
  ElseIf i >= 2 And j = 3 Then
  k = 300
  ElseIf i >= 2 And j = 4 Then
  k = 400
  Else
  k = 500
  End If
  End Sub
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iRow, iCol As Integer
  iRow = Target.Row
  iCol = Target.Column
  If iRow >= 2 And iCol = 2 And Target "" Then
  Application.EnableEvents = False
  Cells(iRow, iCol + 1) = Cells(iRow, iCol) * 2
  Application.EnableEvents = True
  ElseIf iRow >= 2 And iCol = 2 And Target = "" Then
  Cells(iRow, iCol + 1) = ""
  Else
  Cells(iRow, iCol + 1) = ""
  End If
  End Sub
  qian ji ge jiao cheng dou shi yong Worksheet_SelectionChange shi jian lai ju li zi , da jia ying gai neng ti hui ta
  shi zen si yi hui shi le ba 。
  zhe ge jiao cheng jiu shi yao rang ni lai ti hui shen si shi Worksheet_Chang() shi jian 。 yin wei zhe er ge shi jian zai VBA
  dou shi fei chang you yong de , suo yi yi ding yao le jie 。
  jian dan de shuo , qian zhe shi ni shu biao yi dong dao na ge dan yuan ge , jiu chu fa na ge shi jian de zhi xing 。 hou zhe shi yao deng dao
  ni dian xuan de dan yuan ge , shu ? you le gai bian cai hui chu fa shi jian de zhi xing 。 er zhe zhi xing de shi ji yi qian yi hou 。
  Target "" shi dai biao xian ding dang qian de dan yuan ge yao shi you shu ? de , cai hui zhi xing yi xia san xing de cheng xu 。
  Cells(iRow, iCol + 1) = Cells(iRow, iCol) * 2, shi ni zai B lan shu ru shu ? shi ,C
  lan jiang ke de dao B lan er bei de shu ?。
  Target = "" shi xian ding dang qian de dan yuan ge yao shi mei you shu ? de , cai hui zhi xing yi xia yi xing de cheng xu 。
  Cells(iRow, iCol + 1) = "", shi ba C lan de shu ? qing cheng kong ge 。
  Application.EnableEvents = False yu Application.EnableEvents = True, zhe shi
  ge cheng shuang de cheng xu , dang ni yong le qian zhe ji de zai zhi xing qi ta cheng xu hou yao xie shang hou mian de cheng xu 。 ta de mu de zai
  yi zhi shi jian lian suo zhi xing 。 jian dan de shuo jiu shi , zai B zi duan suo chu fa de shi jian , bu yuan zai qi ta dan yuan ge zai
  chu fa ling yi ge Worksheet_Change() shi jian 。
  VBALesson 9 cheng xu shuo ming ∶ ti hui yi xia Worksheet_Change() shi jian lian suo fan ying 。
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iRow As Integer
  iRow = Target.Row
  Application.EnableEvents = False
  Cells(iRow, 3) = Cells(iRow, 3) + Cells(iRow, 2)
  Application.EnableEvents = True
  End Sub
  Private Sub Worksheet_Change(ByVal Target As Range)
  Dim iRow As Integer
  iRow = Target.Row
  'Application.EnableEvents = False
  Cells(iRow, 3) = Cells(iRow, 3) + Cells(iRow, 2)
  'Application.EnableEvents = True
  End Sub
  zhe ge cheng xu de mu de shi yao zai B2 shu ru xin de shu ? shi ,C2 hui jiang B2 shu ru de xin shu ? jia shang C2 yuan
  you de shu ? cheng xian zai C2 shang 。
  zhao shang mian you jia shang Application.EnableEvents = False cheng xu zhi xing dang ran mei wen ti 。
  xian zai ni zai Application.EnableEvents = False yu Application.EnableEvents =
  True qian jia shang 「 '」 kan kan 。
  cheng xu qian jia shang 「 '」 de mu de shi yao shi 「 '」 zhi hou de wen zi bian cheng shuo ming wen zi , cheng xu zhi xing shi shi hui tiao
  guo shuo ming wen zi , bu zhi xing shuo ming wen zi de nei rong 。
  cheng xu qian jia shang 「 '」 fu hao hou , wen zi hui bian cheng lv se 。
  zhi xing di er ge cheng xu shi , ni jiang fa xian C2 bu hui an ni suo yao qiu de , cheng xian jie guo 。
  zhe jiu shi suo wei de shi jian lian suo fan ying 。
  qing wen zhe ge hong gai ru he xie !
  wo xiang yun xing yi ge hong , jiu neng zai dang qian gong zuo biao B3 shang tian shang yi tiao gong shi ; zhe tiao gong shi de jie guo shi suo you gong zuo
  biao shang de B4 dan yuan ge de he . qing wen zhe ge hong gai ru he xie . xie xie !
  Sub gg()
  Dim sh As Worksheet, shname$
  For Each sh In Worksheets
  shname = sh.Name
  ActiveSheet.Range("b3").value = ActiveSheet.Range("b3").value +
  Worksheets(shname).Range("b4")
  Next
  End Sub
  VBA zhong zen yang chuang jian yi ge ming wei “table” de xin gong zuo biao
  tong guo VBA bian cheng , hen rong yi tian jia xin de gong zuo biao , dan shi xin biao de ming zi bu zhi zen yang kong zhi , dui yu xin chuang jian
  de gong zuo biao , you yu qi ming zi bing fei te ding , suo yi jiu bu hao shi yong suo chuang jian de xin biao le 。 bu zhi ge wei you he gao
  jian 。。。。
  Sheets.Add
  ActiveSheet.Name = "table"
  qing jiao : ru he yong VBA jian suo biao 1 zhong A lie yu biao 2,3,4,5..... zhong A lie xiang tong de xing bing ba hou zhe zheng xing kao
  bei dao biao 1 jian suo dao de xing zhong , xie xie !!!!
  To yxptwq∶ yong zhe cheng xu shi kan kan 。
  Sub Copy1()
  Dim Row_dn1, Row_dnN, i, j, n As Integer
  Row_dn1 = Sheet1.Range("A65536").End(xlUp).Row
  k = 1: n = 1
  For Each wSheet In ActiveWorkbook.Worksheets
  With wSheet
  If .Name "Sheet1" Then
  Row_dnN = .Range("A65536").End(xlUp).Row
  For i = 2 To Row_dn1
  For j = 2 To Row_dnN
  If .Cells(j, 1) = Sheet1.Cells(i, 1) Then
  .Rows(j & ":" & j).Copy Destination:=Sheet1.Rows(Row_dn1 +
  n & ":" & Row_dn1 + n)
  n = n + 1
  End If
  Next j
  Next i
  End If
  End With
  Next wSheet
  End Sub
  ru guo yao yong VBA cheng shi shu ru mi ma shi yong xia lie cheng shi ma
  Sub EnterNewPW()
  ' cheng shi shuo ming : li yong SendKey shu ru VBAProject mi ma
  ' zhu yi shi xiang : zhi xing ben cheng shi xu yao zai Excel shi chuang , bu neng zai VBE shi chuang
  Application.SendKeys "%{F11}", True 'Alt + F11 qie huan dao VBA shi chuang
  Application.SendKeys "%T", True 'ALT + T gong ju ( fan ti zhong wen shi (T))
  Application.SendKeys "e", True ' gong ju (T)-VBproject shu xing (E)
  Application.SendKeys "^{TAB}", True 'TAB jian ( qie huan dao PAge2 bao hu ye mian )
  Application.SendKeys "{+}", True ' xuan qu Checkbox fang kuai ( suo ding zhuan an yi gong jian
  shi )
  '({+} xuan qu , {-} qu xiao xuan qu )
  Application.SendKeys "{TAB}", True 'TAB jian ( tiao dao di yi ci shu ru mi ma
  Textbox
  myPW = "chijanzen" ' jia she mi ma chijanzen
  Application.SendKeys myPW, True ' shu ru mi ma
  Application.SendKeys "{TAB}", True 'TAB jian ( tiao dao di er ci shu ru mi ma
  Textbox
  Application.SendKeys myPW, True ' shu ru mi ma
  Application.SendKeys "{ENTER}", True ' an que ding niu ( yu she zhi )
  Application.SendKeys "%{F11}", True ' fan hui Excel shi chuang
  End Sub
  mao pao pai xu fa :
  mao pao pai xu fa zhi suo yi cheng wei “ mao pao pai xu ” shi yin wei zhi jiao xiao de huo shi jiao qing de yuan su fu dao zuo wei ji xu pai
  xu de yi zu shu de ding bu 。
  Sub Macro1()
  Dim i As Integer
  Dim j As Integer
  Dim t as integer
  Static number(1 To 10) As Integer
  For i = 1 To 10
  number(i) = inputbox“ shu ru yao pai xu de shu :”
  Next i
  For i = 10To 2 Step -1
  For j = 1 To i – 1
  ‘ xia mian jin xing wei zhi jiao huan
  If number(j) > number(j + 1) Then
  t = number(j + 1)
  number(j + 1) = number(j)
  number(j) = t
  End If
  Next j
  Next i
  For i = 1 To 20
  Print number(i)
  Next i
  End sub
  shou xian ding yi yi ge shu zu : tong guo xun huan lu ru 10 ge zheng shu , ran hou yong yi ge er chong xun huan ce shi qian yi ge shu shi fou
  da yu hou yi ge shu 。 ru guo da yu ze jiao huan liang ge shu de xia biao , ji jiao huan liang ge shu zai shu zu zhong de wei zhi , jiao huan
  tong guo yi ge bian liang lai jin xing 。
  wo xian yong chuan tong de fang fa jie jue zhe ge wen ti , jing guo bi jiao , xuan yong le jiao wei jian dan de he gao xiao de pai xu fang fa
  ——“ kuai su pai xu ”, ju ti suan fa ke shen kao shu ju jie gou deng you guan shu ji 。 dui suo you shu ju pai xu hou zai he
  bing xiang tong shu ju , he bing cheng xu jiao wei jian bian , wo kai shi shi cai yong le zhe zhong fang fa , dan hou lai fa xian dui yu zhe xie
  de shu ju , xian he bing hou pai xu su du geng kuai , yin wei you da liang xiang tong de shu ju 。 he bing shi cai yong “ biao ji ” suan
  fa , ju ti ru xia :( she shu ju yi cun fang zai sData() shu zu zhong , jie guo cun dao Queryp() shu zu ,
  Amount shi shu ju ge shu )
  ' ba xiang tong yuan su zhi 0
  For i = 1 To Amount
  If sData(i) 0 Then
  For j = i + 1 To Amount
  If sData(i) = sData(j) Then sData(j) = 0
  Next j
  End If
  Next i
  ' shan chu xiang tong yuan su
  Queryp(1) = sData(1)
  k = 1
  For i = 2 To Amount
  If Not (sData(i) = 0) Then
  k = k + 1
  Queryp(k) = sData(i)
  End If
  Next i
  kMax = k
  ReDim Preserve Queryp(kMax)
  sui ran zhe yang shi de yun suan su du you suo gao , dan shi reng ran yao jin xing da liang de xun huan yun suan , zhan ju le cheng xu da bu
  fen de yun suan shi jian 。 yu shi wo yi zhi zai xun mi yi zhong geng wei gao xiao de suan fa 。
  gong fu bu fu you xin ren , zai zai xi fen xi shu ju de te zheng , bi jiao le duo zhong fang an zhi hou , wo zhong yu zhao dao le yi
  zhong xiang dang cheng gong de suan fa , yuan lai yao 3 dao 4 miao de yun suan suo duan dao jin xu 0.1 dao 0.2 miao 。
  wo yu dao de shu ju ju you yi xia te zheng :① xiang tong shu ju hen duo ,② zui da 、 zui xiao shu zhi jian xiang cha bu dao 3,
  ③ dou shi dai liang wei xiao shu de zheng shu 。
  zhen dui shu ju de te zheng , wo cai yong le yi xia suan fa :
  zhen dui shu ju de te zheng , wo cai yong le yi xia suan fa :
  bu zhou :
  1. yong yi ge xun huan zhao chu zheng shu he xiao shu bu fen de zui da 、 zui xiao zhi 。 xiao shu bu fen de zui da 、 zui xiao zhi cheng
  yi 100 zhuan wei zheng shu 。
  2. ding yi yi ge er wei shu zu , xia biao fan wei fen bie shi zheng shu he xiao shu bu fen de zui xiao zhi dao zui da zhi 。
  3. zai yong yi ge xun huan ba suo you yuan shu ju tian ru gang cai ding yi de er wei shu zu , tian xie gui ze shi , yuan shu ju de
  zheng shu he xiao shu bu fen fen bie dui ying er wei shu zu de liang ge xia biao 。 li ru ,“13.51" tian dao “A(13,51)"
  zhong 。
  4. zui hou shun xiang huo ni xiang du qu er wei shu zu zhong de fei ling shu ju ji ke de dao cong xiao dao da huo cong da dao xiao pai lie
  de shu ju , er qie bu hui han you chong fu shu ju 。
  yong VB bian xie de cheng xu ru xia :
  '**** mi ji xing shu ju chu li ****
  Dim i As Long, j As Long, k As Long, kMax As Long
  Dim Queryp() As Single
  ReDim Queryp(Amount)
  Dim IntegerPart As Integer, DecimalPart As Integer
  Dim IPmax As Integer, IPmin As Integer
  Dim DPmax As Integer, DPmin As Integer
  Dim DiffDataArray()
  ' du qu shu ju
  ReadData
  IPmax = 0: IPmin = 1000
  DPmax = 0: DPmin = 99
  For i = 1 To Amount
  ' zhao zheng shu he xiao shu bu fen de zui da 、 zui xiao zhi
  IntegerPart = Int(sData(i))
  DecimalPart = (sData(i) - IntegerPart) * 100
  If IntegerPart > IPmax Then
  IPmax = IntegerPart
  ElseIf IntegerPart DPmax Then
  DPmax = DecimalPart
  ElseIf DecimalPart 0 Then
  k = k + 1
  Queryp(k) = DiffDataArray(i, j)
  End If
  Next j
  Next i
  kMax = k
  ReDim Preserve Queryp(kMax)
  gai fang fa dui yu ben ren yu dao de zhe zhong “ mi ji xing ” shu ju zui wei you xiao , dan shi ru guo yu shang “ xi shu xing ” shu
  ju , li ru zui da 、 zui xiao zhi xiang cha ji qian , shen zhi shang wan de shu ju , jiu mei shen me you shi le , er qie hui zhan yong
  jiao da de nei cun 。
  jing guo gai jin , wo de dao le chu li xi shu xing shu ju de gao xiao suan fa 。 gao xiao de qian ti tiao jian tong yang shi yuan shu ju ju
  you da liang xiang tong shu ju 。 si lu shi zai qian yi zhong fang fa de ji chu shang zeng jia yi ge dan wei shu zu , yong lai bao cun zheng shu
  bu fen shu ju , bao cun guo cheng zhong yong cha ru fa dui qi jin xing pai xu 。 yin wei you da liang chong fu shu ju , yao pai xu de shu
  ju liang xiang dui jiao shao 。 dang cong er wei shu zu zhong du qu shu ju shi , yong dan wei shu zu dai ru er wei shu zu de di yi ge xia
  biao , ju ti dai ma xia :
  '**** xi shu xing shu ju chu li ****
  Dim i As Long, j As Long, k As Long, kMax As Long
  Dim Queryp() As Single
  ReDim Queryp(Amount)
  Dim IntegerPart As Integer, DecimalPart As Integer
  Dim IPmax As Integer, IPmin As Integer
  Dim DPmax As Integer, DPmin As Integer
  Dim IPArray() As Integer, IPAamount As Integer
  ReDim IPArray(Amount)
  Dim DiffDataArray()
  ' du qu shu ju
  ReadData
  IPmax = 0: IPmin = 1000
  DPmax = 0: DPmin = 99
  IPAamount = 0
  For i = 1 To Amount
  ' huo qu zheng shu he xiao shu bu fen de zui da zui xiao zhi
  IntegerPart = Int(sData(i))
  DecimalPart = (sData(i) - IntegerPart) * 100
  If IntegerPart > IPmax Then
  IPmax = IntegerPart
  ElseIf IntegerPart DPmax Then
  DPmax = DecimalPart
  ElseIf DecimalPart IPArray(j) Then
  IPAamount = IPAamount + 1
  For k = IPAamount To j + 1 Step -1
  IPArray(k) = IPArray(k - 1)
  Next k
  IPArray(j) = IntegerPart
  Exit For
  ElseIf IntegerPart = IPArray(j) Then
  Exit For
  End If
  Next j
  If j > IPAamount Then
  IPAamount = IPAamount + 1
  IPArray(IPAamount) = IntegerPart
  End If
  Next i
  ReDim DiffDataArray(IPmin To IPmax, DPmin To DPmax)
  ' tian ru shu ju
  For i = 1 To Amount
  IntegerPart = Int(sData(i))
  DecimalPart = (sData(i) - IntegerPart) * 100
  DiffDataArray(IntegerPart, DecimalPart) = sData(i)
  Next i
  ' ti qu shu ju
  k = 0
  For i = 1 To IPAamount
  For j = DPmax To DPmin Step -1
  If DiffDataArray(IPArray(i), j) 0 Then
  k = k + 1
  Queryp(k) = DiffDataArray(IPArray
  (i), j)
  End If
  Next j
  Next i
  kMax = k
  ReDim Preserve Queryp(kMax)
  k
  ReDim Preserve Queryp(kMax)
  zi dong yin cang biao ge zhong wu shu ju de xing
  biao 1 shi shu ju yuan , jing chang gai bian ;
  biao 2 yin yong biao 1 zhong mou lie you shu ju de dan yuan ge ( li yong dong tai wei zhi yi shi xian 。)
  you yu biao 1 de gai bian , biao 2 de da xiao sui zhi er bian 。
  wen ti : ru he shi xian biao 2 zhong mei you shu ju de xing ( you gong shi ) zi dong yin cang ? xie xie ci jiao !
  Sub abc()
  For i = 1 To 300
  If Cells(i, 1).value = "" Then Rows(i).Hidden = True
  Next i
  End Sub
  ni xie de yu ju ke yi jie jue yin cang de wen ti , ke shi ru guo wo zhi xing le ta zhi hou , zai zai biao 1 zhong zeng jia shu ju ,
  biao 2 bu hui zi dong xian shi you le shu ju de xing 。 ru he xiu gai ?
  jiang ci hong she wei zi dong yun xing ( da kai wen jian shi )
  Sub abc()
  For i = 1 To 300
  If Cells(i, 1).value "" Then Rows(i).Hidden = false
  Next i
  End Sub
  yong VBA ru he zi dong he bing lie de nei rong ?
  yong VBA ru he zi dong he bing lie de nei rong ?
  To hongjian :
  Sub MergeTest()
  For i = 3 To 30
  Cells(i, 3) = Cells(i, 1) & Chr(10) & Cells(i, 2)
  Next
  End Sub
  1) chuang jian Excel dui xiang
  Excel dui xiang mo xing bao kuo le 128 ge bu tong de dui xiang , cong ju xing 、 wen ben kuang deng jian dan de dui
  xiang dao tou shi biao , tu biao deng fu duo de dui xiang 。 xia mian jian dan jie shao yi xia qi zhong zui chong yao , ye shi yong
  de zui duo de wu ge dui xiang 。
  (1)Application dui xiang
  Application dui xiang chu yu Excel dui xiang ceng ci jie gou de ding ceng , biao shi Excel zi shen de
  yun xing huan jing 。
  (2)Workbook dui xiang
  Workbook dui xiang zhi jie di chu yu Application dui xiang de xia ceng , biao shi yi ge Excel gong
  zuo bo wen jian 。
  (3)Worksheet dui xiang
  Worksheet dui xiang bao han yu Workbook dui xiang , biao shi yi ge Excel gong zuo biao 。
  (4)Range dui xiang
  Range dui xiang bao han yu Worksheet dui xiang , biao shi Excel gong zuo biao zhong de yi ge huo duo ge
  dan yuan ge 。
  (5)Cells dui xiang
  Cells dui xiang bao han yu Worksheet dui xiang , biao shi Excel gong zuo biao zhong de yi ge dan yuan ge 。
  ru guo yao qi dong yi ge Excel, shi yong Workbook he Worksheet dui xiang , xia mian de dai ma
  qi dong le Excel bing chuang jian le yi ge xin de bao han yi ge gong zuo biao de gong zuo bo :
  Dim zsbexcel As Excel.Application
  Set zsbexcel = New Excel.Application
  zsbexcel.Visible = True
  ru yao Excel bu ke jian , ke shi zsbexcel.Visible = False
  zsbexcel.SheetsInNewWorkbook = 1
  Set zsbworkbook = zsbexcel.Workbooks.Add
  2) she zhi dan yuan ge he qu yu zhi
  yao she zhi yi zhang gong zuo biao zhong mei ge dan yuan ge de zhi , ke yi shi yong Worksheet dui xiang de
  Range shu xing huo Cells shu xing 。
  With zsbexcel.ActiveSheet
  .Cells(1, 2).value = "100"
  .Cells(2, 2).value = "200"
  .Cells(3, 2).value = "=SUM(B1:B2)"
  .Range("A3:A9") = " zhong guo ren min jie fang jun "
  End With
  yao she zhi dan yuan ge huo qu yu de zi ti 、 bian kuang , ke yi li yong Range dui xiang huo Cells dui xiang
  de Borders shu xing he Font shu xing :
  With objexcel.ActiveSheet.Range("A2:K9").Borders ' bian kuang she zhi
  .Line = xlBorderLine
  .Weight = xlThin
  .ColorIndex = 1
  End With
  With objexcel.ActiveSheet.Range("A3:K9").Font' zi ti she zhi
  .Size = 14
  .Bold = True
  .Italic = True
  .ColorIndex = 3
  End With
  tong guo dui Excel dan yuan ge he qu yu zhi de ge zhong she zhi de shen ru le jie , ke yi chuang jian ge zhong fu
  duo 、 mei guan 、 man zu xu yao de 、 ju you zi ji te dian de bao biao 。
  3) yu lan ji da yin
  sheng cheng suo xu yao de gong zuo biao hou , jiu ke yi dui EXCEL fa chu yu lan 、 da yin zhi ling le 。
  zsbexcel.ActiveSheet.PageSetup.Orientation = xlPortrait '
  she zhi da yin fang xiang
  zsbexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4'
  she zhi da yin zhi de da xia
  zsbexcel.Caption = " da yin yu lan " ' she zhi yu lan chuang kou de
  biao ti
  zsbexcel.ActiveSheet.PrintPreview' da yin yu lan
  zsbexcel.ActiveSheet.PrintOut' da yin shu chu
  tong guo da yin fang xiang 、 da yin zhi zhang da xiao de she zhi , bu duan jin xing yu lan , zhi dao man yi wei zhi ,
  zui zhong jin xing da yin shu chu 。
  wei le zai tui chu ying yong cheng xu hou EXCEL bu ti shi yong hu shi fou bao cun yi xiu gai de wen jian , xu shi
  yong ru xia yu ju :
  zsbexcel.DisplayAlerts = False
  zsbexcel.Quit ' tui chu EXCEL
  zsbexcel.DisplayAlerts = True
  ru ci she ji de bao biao da yin shi tong guo EXCEL cheng xu lai hou tai shi xian de 。 dui yu shi yong zhe lai
  shuo , gen ben kan bu dao ju ti guo cheng , zhi kan dao yi zhang zhang piao liang de bao biao qing yi di bei da yin chu lai le 。
  4) ju ti shi li
  xia mian gei chu yi ge ju ti shi li , ta zai window98、Visual Basic 6.0、
  Microsoft Office97 de huan jing xia tiao shi tong guo 。
  zai VB zhong qi dong yi ge xin de Standard EXE gong cheng , zai “ gong cheng ” cai dan de “ yin yong ”
  xuan xiang xia yin yong Excel Object Library; ran hou zai Form zhong tian jia yi ge ming ling an niu
  cmdExcel; zui hou zai chuang ti zhong shu ru ru xia dai ma :
  Dim zsbexcel As Excel.Application
  Private Sub cmdExcel_Click()
  Set zsbexcel = New Excel.Application
  zsbexcel.Visible = True
  zsbexcel.SheetsInNewWorkbook = 1
  Set zsbworkbook = zsbexcel.Workbooks.Add
  With zsbexcel.ActiveSheet.Range("A2:C9").Borders' bian kuang she zhi
  .Line = xlBorderLine
  .Weight = xlThin
  .ColorIndex = 1
  End With
  With zsbexcel.ActiveSheet.Range("A3:C9").Font' zi ti she zhi
  .Size = 14
  .Bold = True
  .Italic = True
  .ColorIndex = 3
  End With
  zsbexcel.ActiveSheet.Rows.HorizontalAlignment =
  xlVAlignCenter' shui ping ju zhong
  zsbexcel.ActiveSheet.Rows.VerticalAlignment =
  xlVAlignCenter' chui zhi ju zhong
  With zsbexcel.ActiveSheet
  .Cells(1, 2).value = "100"
  .Cells(2, 2).value = "200"
  .Cells(3, 2).value = "=SUM(B1:B2)"
  .Cells(1, 3).value = " zhong guo ren min jie fang jun "
  .Range("A3:A9") = "50"
  End With
  zsbexcel.ActiveSheet.PageSetup.Orientation = xlPortrait '
  xlLandscape
  zsbexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4
  zsbexcel.ActiveSheet.PrintOut
  zsbexcel.DisplayAlerts = False
  zsbexcel.Quit
  zsbexcel.DisplayAlerts = True
  Set zsbexcel = Nothing
  ti gao EXCEL zhong VBA de xiao lv
  fang fa 1: jin liang shi yong VBA yuan you de shu xing 、 fang fa he Worksheet han shu
  you yu Excel dui xiang duo da bai duo ge , dui xiang de shu xing 、 fang fa 、 shi jian duo bu sheng shu , dui yu chu xue zhe lai
  shuo ke neng dui ta men bu quan bu le jie , zhe jiu chan sheng le bian cheng zhe jing chang bian xie yu Excel dui xiang de shu xing 、 fang fa xiang
  tong gong neng de VBA dai ma duan , er zhe xie dai ma duan de yun xing xiao lv xian ran yu Excel dui xiang de shu xing 、 fang fa wan cheng
  ren wu de su du xiang cha shen da 。 li ru yong Range de shu xing CurrentRegion lai fan hui Range dui xiang , gai dui
  xiang dai biao dang qian qu 。( dang qian qu zhi yi ren yi kong bai xing ji kong bai lie de zu he wei bian jie de qu yu )。 tong yang gong neng
  de VBA dai ma xu shu shi xing 。 yin ci bian cheng qian ying jin ke neng duo di le jie Excel dui xiang de shu xing 、 fang fa 。
  chong fen li yong Worksheet han shu shi ti gao cheng xu yun xing su du de ji du you xiao de fang fa 。 ru qiu ping yun gong zi
  de li zi :For Each c In Worksheet(1).Range(″A1:A1000″)
  Totalvalue = Totalvalue + c.value
  Next
  Averagevalue = Totalvalue / Worksheet(1).Range(″
  A1:A1000″).Rows.Count
  er xia mian dai ma cheng xu bi shang mian li zi kuai de duo :
  Averagevalue="/blog/Application.WorksheetFunction.Average(Worksheets
  (1).Range(″A1:A1000″))
  qi ta han shu ru Count,Counta,Countif,Match,Lookup deng deng , dou neng dai ti xiang tong gong neng de
  VBA cheng xu dai ma , ti gao cheng xu de yun xing su du 。
  fang fa 2: jin liang jian shao shi yong dui xiang yin yong , you qi zai xun huan zhong
  mei yi ge Excel dui xiang de shu xing 、 fang fa de tiao yong dou xu yao tong guo OLE jie kou de yi ge huo duo ge tiao yong ,
  zhe xie OLE tiao yong dou shi xu yao shi jian de , jian shao shi yong dui xiang yin yong neng jia kuai VBA dai ma de yun xing 。 li ru
  1. shi yong With yu ju 。
  Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Name=″Pay″
  Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Font
  ...
  ze yi xia yu ju bi shang mian de kuai
  With Workbooks(1).Sheets(1).Range(″A1:A1000″).Font
  .Name = ″Pay″
  .Font = ″Bold″
  ...
  End With
  2. shi yong dui xiang bian liang 。
  ru guo ni fa xian yi ge dui xiang yin yong bei duo ci shi yong , ze ni ke yi jiang ci dui xiang yong Set she zhi wei dui xiang bian
  liang , yi jian shao dui dui xiang de fang wen 。 ru :
  Workbooks(1).Sheets(1).Range(″A1″).value = 100
  Workbooks(1).Sheets(1).Range(″A2″).value = 200
  ze yi xia dai ma bi shang mian de yao kuai :
  Set MySheet = Workbooks(1).Sheets(1)
  MySheet.Range(″A1″).value = 100
  MySheet.Range(″A2″).value = 200
  3. zai xun huan zhong yao jin liang jian shao dui xiang de fang wen 。
  For k = 1 To 1000
  Sheets(″Sheet1″).Select
  Cells(k,1).value = Cells(1,1).value
  Next k
  ze yi xia dai ma bi shang mian de yao kuai :
  Set Thevalue = Cells(1,1).value
  Sheets(″Sheet1″).Select
  For k = 1 To 1000
  Cells(k,1).value = Thevalue
  Next k
  fang fa 3: jian shao dui xiang de ji huo he xuan ze
  ru guo ni de tong guo lu zhi hong lai xue xi VBA de , ze ni de VBA cheng xu li yi ding chong man le dui xiang de ji huo he xuan
  ze , li ru Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select deng
  , dan shi shi shang da duo shu qing kuang xia zhe xie cao zuo bu shi bi xu de 。 li ru
  Sheets(″Sheet3″).Select
  Range(″A1″).value = 100
  Range(″A2″).value = 200
  ke gai wei :
  With Sheets(″Sheet3″)
  .Range(″A1″).value = 100
  .Range(″A2″).value = 200
  End With
  fang fa 4: guan bi ping mu geng xin
  ru guo ni de VBA cheng xu qian mian san tiao zuo de bi jiao cha , ze guan bi ping mu geng xin shi ti gao VBA cheng xu yun xing su du
  de zui you xiao de fang fa , suo duan yun xing shi jian 2/3 zuo you 。 guan bi ping mu geng xin de fang fa :
  Application.ScreenUpdate = False
  qing bu yao wang ji VBA cheng xu yun xing jie shu shi zai jiang gai zhi she hui lai :
  Application.ScreenUpdate = True
  yi shang shi ti gao VBA yun xing xiao lv de bi jiao you xiao de ji zhong fang fa【原文】【汉音对照
 
 
 
 
 
 
 
 
 
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

如何磨出破洞牛仔裤?牛仔裤怎么剪破洞?

把牛仔裤磨出有线的破洞 1、具体工具就是磨脚石,下面垫一个硬物,然后用磨脚石一直磨一直磨,到把那块磨薄了,用手撕开就好了。出来的洞啊很自然的。需要猫须的话调几...

我就是扫描下图得到了敬业福和爱国福

先来看下敬业福和爱国福 今年春节,支付宝再次推出了“五福红包”活动,表示要“把欠大家的敬业福都还给大家”。 今天该活动正式启动,和去年一样,需要收集“五福”...

冰箱异味产生的原因和臭味去除的方法

有时候我们打开冰箱就会闻到一股异味,冰箱里的这种异味是因为一些物质发出的气味的混合体,闻起来让人恶心。 产生这些异味的主要原因有以下几点。 1、很多人有这种习...

 
 
ben shi li wei she zhi gong zuo biao mi ma ActiveSheet.Protect Password:=641112 ' bao hu gong zuo biao bing she zhi mi ma ActiveSheet.Unprotect Password:=641112 ' che xiao gong zuo biao bao hu bing qu xiao mi ma ' ben shi li bao cun dang qian huo dong gong zuo bao de fu ben 。 ActiveWorkbook.SaveCopyAs "C:\TEMP\XXXX.XLS" ' ben shi li tong guo jiang Saved shu xing she wei True lai guan bi bao han ben duan dai ma de gong zuo bao , bing fang qi dui gai gong zuo bao de ren he geng gai 。 ThisWorkbook.Saved = True ThisWorkbook.Close ' ben shi li dui zi dong chong xin ji suan gong neng jin xing she zhi , shi Microsoft Excel bu dui di yi zhang gong zuo biao zi dong jin xing chong xin ji suan 。 Worksheets(1).EnableCalculation = False ' xia shu guo cheng da kai C pan shang ming wei MyFolder de wen jian jia zhong de MyBook.xls gong zuo bao 。 Workbooks.Open ("C:\MyFolder\MyBook.xls") ' ben shi li xian shi huo dong gong zuo bao zhong gong zuo biao sheet1 shang dan yuan ge A1 zhong de zhi 。 MsgBox Worksheets("Sheet1").Range("A1").Value ben shi li xian shi huo dong gong zuo bao zhong mei ge gong zuo biao de ming cheng For Each ws In Worksheets MsgBox ws.Name Next ws ben shi li xiang huo dong gong zuo bao tian jia xin gong zuo biao , bing she zhi gai gong zuo biao de ming cheng ? Set NewSheet = Worksheets.Add NewSheet.Name = "current Budget" ben shi li jiang xin jian de gong zuo biao yi dao gong zuo bao de mo yi 'Private Sub Workbook_NewSheet(ByVal Sh As Object) Sh.Move After:=Sheets(Sheets.Count) End Sub ben shi li jiang xin jian gong zuo biao yi dao gong zuo bao de mo yi 'Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, _ ByVal Sh As Object) Sh.Move After:=Wb.Sheets(Wb.Sheets.Count) End Sub ben shi li xin jian yi zhang gong zuo biao , ran hou zai di yi lie zhong lie chu huo dong gong zuo bao zhong de suo you gong zuo biao de ming cheng 。 Set NewSheet = Sheets.Add(Type:=xlWorksheet) For i = 1 To Sheets.Count NewSheet.Cells(i, 1).Value = Sheets(i).Name Next i ben shi li jiang di shi xing yi dao chuang kou de zui shang mian ? Worksheets("Sheet1").Activate ActiveWindow.ScrollRow = 10 dang ji suan gong zuo bao zhong de ren he gong zuo biao shi , ben shi li dui di yi zhang gong zuo biao de A1:A100 qu yu jin xing pai xu 。 'Private Sub Workbook_SheetCalculate(ByVal Sh As Object) With Worksheets(1) .Range("a1:a100").Sort Key1:=.Range("a1") End With End Sub ben shi li xian shi gong zuo biao Sheet1 de da yin yu lan 。 Worksheets("Sheet1").PrintPreview ben shi li bao cun dang qian huo dong gong zuo bao ? ActiveWorkbook.Save ben shi li bao cun suo you da kai de gong zuo bao , ran hou guan bi Microsoft Excel。 For Each w In Application.Workbooks w.Save Next w Application.Quit xia li zai huo dong gong zuo bao de di yi zhang gong zuo biao qian mian tian jia liang zhang xin de gong zuo biao ? Worksheets.Add Count:=2, Before:=Sheets(1) ben shi li she zhi 15 miao hou yun xing my_Procedure guo cheng , cong xian zai kai shi ji shi 。 Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure" ben shi li she zhi my_Procedure zai xia wu 5 dian kai shi yun xing 。 Application.OnTime TimeValue("17:00:00"), "my_Procedure" ben shi li che xiao qian yi ge shi li dui OnTime de she zhi 。 Application.OnTime EarliestTime:=TimeValue("17:00:00"), _ Procedure:="my_Procedure", Schedule:=False mei dang gong zuo biao chong xin ji suan shi , ben shi li jiu tiao zheng A lie dao F lie de kuan du 。 'Private Sub Worksheet_Calculate() Columns("A:F").AutoFit End Sub ben shi li shi huo dong gong zuo bao zhong de ji suan jin shi yong xian shi de shu zi jing du 。 ActiveWorkbook.PrecisionAsDisplayed = True ben shi li jiang gong zuo biao Sheet1 shang de A1:G37 qu yu jian xia , bing fang ru jian tie ban 。 Worksheets("Sheet1").Range("A1:G37").Cut Calculate fang fa ji suan suo you da kai de gong zuo bao 、 gong zuo bao zhong de yi zhang te ding de gong zuo biao huo zhe gong zuo biao zhong zhi ding qu yu de dan yuan ge , ru xia biao suo shi : ' yao ji suan ' yi zhao ben shi li suo you da kai de gong zuo bao ' Application.Calculate ( huo zhi shi Calculate ) zhi ding gong zuo biao ' ji suan zhi ding gong zuo biao Sheet1 Worksheets ("Sheet1").Calculate zhi ding qu yu 'Worksheets(1).Rows(2).Calculate ben shi li dui zi dong chong xin ji suan gong neng jin xing she zhi , shi Microsoft Excel bu dui di yi zhang gong zuo biao zi dong jin xing chong xin ji suan 。 Worksheets(1).EnableCalculation = False ben shi li ji suan Sheet1 yi yong qu yu zhong A lie 、B lie he C lie de gong shi 。 Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate ben shi li geng xin dang qian huo dong gong zuo bao zhong de suo you lian jie ? ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources ben shi li she zhi di yi zhang gong zuo biao de gun dong qu yu ? Worksheets(1).ScrollArea = "a1:f10" ben shi li xin jian yi ge gong zuo bao , ti shi yong hu shu ru wen jian ming , ran hou bao cun gai gong zuo bao 。 Set NewBook = Workbooks.Add Do fName = Application.GetSaveAsFilename Loop Until fName False NewBook.SaveAs Filename:=fName ben shi li da kai Analysis.xls gong zuo bao , ran hou yun xing Auto_Open hong 。 Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen ben shi li dui huo dong gong zuo bao yun xing Auto_Close hong , ran hou guan bi gai gong zuo bao 。 With ActiveWorkbook .RunAutoMacros xlAutoClose .Close End With zai ben shi li zhong ,Microsoft Excel xiang yong hu xian shi huo dong gong zuo bao de lu jing he wen jian ming cheng 。 'Sub UseCanonical() Display the full path to user. MsgBox ActiveWorkbook.FullNameURLEncoded End Sub ben shi li xian shi dang qian gong zuo bao de lu jing ji wen jian ming ( jia ding shang wei bao cun ci gong zuo bao )。 MsgBox ActiveWorkbook.FullName ben shi li guan bi Book1.xls, bing fang qi suo you dui ci gong zuo bao de geng gai 。 Workbooks("BOOK1.XLS").Close SaveChanges:=False ben shi li guan bi suo you da kai de gong zuo bao 。 ru guo mou ge da kai de gong zuo bao you gai bian ,Microsoft Excel jiang xian shi xun wen shi fou bao cun geng gai de dui hua kuang he xiang ying ti shi 。 Workbooks.Close ben shi li zai da yin zhi qian dui dang qian huo dong gong zuo bao de suo you gong zuo biao chong xin ji suan ? 'Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each wk In Worksheets wk.Calculate Next End Sub ben shi li dui cha xun biao yi zhong de di yi lie shu ju jin xing hui zong , bing zai shu ju qu yu xia fang xian shi di yi lie shu ju de zong he 。 Set c1 = Sheets("sheet1").QueryTables(1).ResultRange.Columns(1) c1.Name = "Column1" c1.End(xlDown).Offset(2, 0).Formula = "=sum(Column1)" ben shi li qu xiao huo dong gong zuo bao zhong de suo you geng gai ? ActiveWorkbook.RejectAllChanges ben shi li zai shang ye wen ti zhong shi yong gui hua qiu jie han shu , yi shi zong li run da dao zui da zhi 。SolverSave han shu jiang dang qian wen ti bao cun dao huo dong gong zuo biao shang de mou yi qu yu 。 Worksheets("Sheet1").Activate SolverReset SolverOptions Precision:=0.001 SolverOK SetCell:=Range("TotalProfit"), _ MaxMinVal:=1, _ ByChange:=Range("C4:E6") SolverAdd CellRef:=Range("F4:F6"), _ Relation:=1, _ FormulaText:=100 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=3, _ FormulaText:=0 SolverAdd CellRef:=Range("C4:E6"), _ Relation:=4 SolverSolve UserFinish:=False SolverSave SaveArea:=Range("A33") ben shi li yin cang Chart1、Chart3 he Chart5。 Charts(Array("Chart1", "Chart3", "Chart5")).Visible = False dang ji huo gong zuo biao shi , ben shi li dui A1:A10 qu yu jin xing pai xu 。 'Private Sub Worksheet_Activate() Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending End Sub ben shi li geng gai Microsoft Excel lian jie 。 ActiveWorkbook.ChangeLink "c:\excel\book1.xls", _ "c:\excel\book2.xls", xlExcelLinks ben shi li qi yong shou bao hu de gong zuo biao shang de zi dong shai xuan jian tou ? ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True ben shi li jiang huo dong gong zuo bao she wei zhi du ? ActiveWorkbook.ChangeFileAccess Mode:=xlReadOnly ben shi li shi gong xiang gong zuo bao mei san fen zhong zi dong geng xin yi ci ? ActiveWorkbook.AutoUpdateFrequency = 3 xia shu Sub guo cheng qing chu huo dong gong zuo bao zhong Sheet1 shang de suo you dan yuan ge de nei rong 。 'Sub ClearSheet() Worksheets("Sheet1").Cells.ClearContents End Sub ben shi li dui suo you gong zuo bao dou guan bi gun dong tiao ? Application.DisplayScrollBars = False ru guo ju you mi ma bao hu de gong zuo bao de wen jian shu xing mei you jia mi , ze ben shi li she zhi zhi ding gong zuo bao de mi ma jia mi xuan xiang 。 'Sub SetPasswordOptions() With ActiveWorkbook If .PasswordEncryptionProvider "Microsoft RSA SChannel Cryptographic Provider" Then .SetPasswordEncryptionOptions _ PasswordEncryptionProvider:="Microsoft RSA SChannel Cryptographic Provider", _ PasswordEncryptionAlgorithm:="RC4", _ PasswordEncryptionKeyLength:=56, _ PasswordEncryptionFileProperties:=True End If End With End Sub zai ben shi li zhong , ru guo huo dong gong zuo bao bu neng jin xing xie bao hu , na me Microsoft Excel she zhi zi fu chuan mi ma yi zuo wei huo dong gong zuo bao de xie mi ma 。 'Sub UseWritePassword() Dim strPassword As String strPassword = "secret" ' Set password to a string if allowed. If ActiveWorkbook.WriteReserved = False Then ActiveWorkbook.WritePassword = strPassword End If End Sub zai ben shi li zhong ,Microsoft Excel da kai ming wei Password.xls de gong zuo bao , she zhi ta de mi ma , ran hou guan bi gai gong zuo bao 。 ben shi li jia ding ming wei Password.xls de wen jian wei yu C:\ qu dong qi shang 。 'Sub UsePassword() Dim wkbOne As Workbook Set wkbOne = Application.Workbooks.Open("C:\Password.xls") wkbOne.Password = "secret" wkbOne.Close ' zhu yi Password shu xing ke du bing fan hui “********”。 End Sub ben shi li jiang Book1.xls de dang qian chuang kou geng gai wei xian shi gong shi 。 Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate ActiveWindow.DisplayFormulas = True ' ben shi li jie shou huo dong gong zuo bao zhong de suo you geng gai ? ActiveWorkbook.AcceptAllChanges ben shi li xian shi huo dong gong zuo bao de lu jing he ming cheng Sub UseCanonical() MsgBox ' xiao xi kuang [b7] = ActiveWorkbook.FullName ' dang qian gong zuo bao [b8] = ActiveWorkbook.FullNameURLEncoded ' huo dong gong zuo bao End Sub ben shi li xian shi Microsoft Excel qi dong wen jian jia de wan zheng lu jing 。 MsgBox Application.StartupPath Activate shi jian ji huo yi ge gong zuo bao 、 gong zuo biao 、 tu biao huo qian ru tu biao shi chan sheng ci shi jian 。 dang ji huo gong zuo biao shi , ben shi li dui A1:A10 qu yu jin xing pai xu 。 Private Sub Worksheet_Activate() Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending End Sub Calculate shi jian dui yu Worksheet dui xiang , zai dui gong zuo biao jin xing chong xin ji suan zhi hou chan sheng ci shi jian mei dang gong zuo biao chong xin ji suan shi , ben shi li jiu tiao zheng A lie dao F lie de kuan du 。 Private Sub Worksheet_Calculate() Columns("A:F").AutoFit End Sub ben shi li xiang huo dong gong zuo bao tian jia xin gong zuo biao , bing she zhi gai gong zuo biao de ming cheng 。 Set newSheet = Worksheets.Add newSheet.Name = "current Budget" ben shi li guan bi gong zuo bao Book1.xls, dan bu ti shi yong hu bao cun suo zuo geng gai 。Book1.xls zhong de suo you geng gai dou bu hui bao cun 。 Application.DisplayAlerts = False Workbooks("BOOK1.XLS").Close Application.DisplayAlerts = True shi li xian shi mei yi ge ke yong jia zai hong de lu jing ji wen jian ming 。 For Each a In AddIns MsgBox a.FullName Next a ChDir yu ju gai bian dang qian de mu lu huo wen jian jia 。 ChDir path zai Power Macintosh zhong , mo ren qu dong qi zong shi gai wei zai path yu ju zhong zhi ding de qu dong qi 。 wan zheng lu jing zhi ding you juan biao ming kai shi , xiang dui lu jing you mao hao (:) kai shi . ChDir ke yi bian ren lu jing zhong zhi ding de bie ming : ChDir "MacDrive:Tmp" ' zai Macintosh zhong ben shi li xian shi dang qian lu jing fen ge fu 。 MsgBox "The path separator character is " & _ Application.PathSeparator Move fang fa jiang yi ge zhi ding de wen jian huo wen jian jia cong yi ge di fang yi dong dao ling yi ge di fang 。 yu fa object.Move destination Move fang fa yu fa you ru xia ji bu fen : bu fen miao shu object bi xu de 。 shi zhong shi yi ge File huo Folder dui xiang de ming zi 。 destination bi xu de 。 wen jian huo wen jian jia yao yi dong dao de mu biao 。 bu yuan xu you tong pei fu 。 CreateFolder fang fa chuang jian yi ge wen jian jia 。 yu fa object.CreateFolder(foldername) reateFolder fang fa you ru xia ji bu fen : bu fen miao shu object bi xu de 。 shi zhong shi yi ge FileSystemObject de ming zi 。 foldername bi xu de 。 zi fu chuan biao da shi , ta biao shi chuang jian de wen jian jia 。 ben shi li shi yong MkDir yu ju lai chuang jian mu lu huo wen jian jia 。 ru guo mei you zhi ding qu dong qi , xin mu lu huo wen jian jia jiang hui jian zai dang qian qu dong qi zhong 。 MkDir "MYDIR" ' jian li xin de mu lu huo wen jian jia 。 Name yu ju shi li ben shi li shi yong Name yu ju lai geng gai wen jian de ming cheng 。 shi li zhong jia she suo you shi yong dao de mu lu huo wen jian jia dou yi cun zai 。 zai Macintosh zhong , mo ren qu dong qi ming cheng shi “HD” bing qie lu jing bu fen you mao hao qu dai fan xie xian ge kai 。 Dim OldName, NewName OldName = "OLDFILE": NewName = "NEWFILE" ' ding yi wen jian ming 。 Name OldName As NewName ' geng gai wen jian ming 。 OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE" Name OldName As NewName ' geng gai wen jian ming , bing yi dong wen jian 。 ben shi li she zhi ti huan qi dong wen jian jia 。 Application.AltStartupPath = "C:\EXCEL\MACROS" FolderExists fang fa ru guo zhi ding de wen jian jia cun zai fan hui True, bu cun zai fan hui False。 yu fa object.FolderExists(folderspec) ben shi li zai dan yuan ge zhong qi yong bian ji 。 Application.EditDirectlyInCell = True cheng xu shuo ming : ji zhong yong VBA zai dan yuan ge shu ru shu ju de fang fa : Public Sub Writes() 1-- 2 fang fa , zui jian dan zai "[ ]" zhong shu ru dan yuan ge ming cheng 。 1 [A1] = 100 ' zai A1 dan yuan ge shu ru 100。 2 [A2:A4] = 10 ' zai A2:A4 dan yuan ge shu ru 10。 3-- 4 fang fa , cai yong Range(" "), " " zhong shu ru dan yuan ge ming cheng 。 3 Range("B1") = 200 ' zai B1 dan yuan ge shu ru 200。 4 Range("C1:C3") = 300 ' zai C1:C3 dan yuan ge shu ru 300。 5-- 6 fang fa , cai yong Cells(Row,Column),Row shi dan yuan ge xing shu ,Column shi dan yuan ge lan shu 。 5 Cells(1, 4) = 400 ' zai D1 dan yuan ge shu ru 400。 6 Range(Cells(1, 5), Cells(5, 5)) = 50 ' zai E1:E 5 dan yuan ge shu ru 50。 End Sub VBALesson3 cheng xu shuo ming : ru he li yong Worksheet_SelectionChange shu ru shu ju de fang fa 。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target = 100 End Sub VBALesson4 cheng xu shuo ming : ru he li yong Worksheet_SelectionChange zai xian ding de dan yuan ge shu ru shu ju de fang fa 。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row >= 2 And Target.Column = 2 Then Target = 100 End If End Sub VBALesson5 cheng xu shuo ming : bi jiao Worksheet_SelectionChange() yu yong an niu CommandButton1_Click() lai zhi xing cheng xu er zhe de fang fa yu xie fa you he bu tong 。 Worksheet_SelectionChange() shi jian Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row >= 2 And Target.Column = 2 Then Target = 100 End If End Sub an chou CommandButton1_Click() Private Sub CommandButton1_Click() If ActiveCell.Row >= 2 And ActiveCell.Column >= 3 Then ActiveCell = 100 End If End Sub er zhe zhi xing fang fa zui da de di fang , zai yu Worksheet_SelectionChange() shi zi dong de , ni bu yong le jie ta shi zen me wan cheng gong zuo de 。 an niu CommandButton1_Click() shi ren gong de , bi SelectionChange() duo yi dao shou xu , jiu shi yao qu an na jie niu , cheng xu cai hui zhi xing 。 SelectionChange() you yi ge shen shu Target ke yong ;CommandButton1_Click () mei you 。 suo yi wo men yao yong ActiveCell nei ding han shu lai qu dai Target,ActiveCell yu Target zui da de bu tong dian ta zhi neng zhi ding yi ge dan yuan ge 。 jiu shi ni xuan qu duo ge dan yuan ge ye zhi you zui shang mian de dan yuan ge hui jia shang shu ju ; yong Selection qu dai ActiveCell, yong fa jiu gen Target yi yang le 。 VBALesson 6 cheng xu shuo ming : wan zheng de If...Then ┅ End luo ji pan duan shi 。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row >= 2 And Target.Column = 2 Then Target = 200 ElseIf Target.Row >= 2 And Target.Column = 3 Then Target = 300 ElseIf Target.Row >= 2 And Target.Column = 2 Then Target = 400 Else Target = 500 End If End Sub zhe shi ge wan zheng de If luo ji pan duan shi , yi si shi shuo , jia ru If hou de pan duan shi tiao jian cheng li de hua , jiu zhi xing di er tiao cheng xu , fou ze jia ru ElseIf hou de pan duan shi tiao jian cheng li de hua , jiu zhi xing di si tiao cheng xu , fou ze jia ru ling yi ge ElseIf hou de pan duan shi tiao jian cheng li de hua , jiu zhi xing di liu tiao cheng xu 。 Else de yi si shi shuo , jia ru yi shang tiao jian dou bu cheng li de hua , jiu zhi xing di ba tiao cheng xu 。 ta de zhi xing fang shi shi jia ru IF de tiao jian cheng li de hua , jiu bu zhi xing qi ta ElseIf ji Else de luo ji pan duan shi , jia ru If hou de tiao jian bu cheng li de hua cai hui zhi xing ElseIf huo Else luo ji pan duan shi 。 di er ge ElseIf hou de tiao jian yin wei yu IF hou de tiao jian yi yang , suo yi zhe ge pan duan shi hou mian de Target=400 jiang shi yong yuan wu fa zhi xing dao de cheng xu 。 VBALesson 7 cheng xu shuo ming ∶ wo men wei shen me yao yong bian shu 。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim i , j As Integer Dim k As Range i = Target.Row j = Target.Column Set k = Target If i >= 2 And j = 2 Then k = 200 ElseIf i >= 2 And j = 3 Then k = 300 ElseIf i >= 2 And j = 4 Then k = 400 Else k = 500 End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim iRow, iCol As Integer iRow = Target.Row iCol = Target.Column If iRow >= 2 And iCol = 2 And Target "" Then Application.EnableEvents = False Cells(iRow, iCol + 1) = Cells(iRow, iCol) * 2 Application.EnableEvents = True ElseIf iRow >= 2 And iCol = 2 And Target = "" Then Cells(iRow, iCol + 1) = "" Else Cells(iRow, iCol + 1) = "" End If End Sub qian ji ge jiao cheng dou shi yong Worksheet_SelectionChange shi jian lai ju li zi , da jia ying gai neng ti hui ta shi zen si yi hui shi le ba 。 zhe ge jiao cheng jiu shi yao rang ni lai ti hui shen si shi Worksheet_Chang() shi jian 。 yin wei zhe er ge shi jian zai VBA dou shi fei chang you yong de , suo yi yi ding yao le jie 。 jian dan de shuo , qian zhe shi ni shu biao yi dong dao na ge dan yuan ge , jiu chu fa na ge shi jian de zhi xing 。 hou zhe shi yao deng dao ni dian xuan de dan yuan ge , shu ? you le gai bian cai hui chu fa shi jian de zhi xing 。 er zhe zhi xing de shi ji yi qian yi hou 。 Target "" shi dai biao xian ding dang qian de dan yuan ge yao shi you shu ? de , cai hui zhi xing yi xia san xing de cheng xu 。 Cells(iRow, iCol + 1) = Cells(iRow, iCol) * 2, shi ni zai B lan shu ru shu ? shi ,C lan jiang ke de dao B lan er bei de shu ?。 Target = "" shi xian ding dang qian de dan yuan ge yao shi mei you shu ? de , cai hui zhi xing yi xia yi xing de cheng xu 。 Cells(iRow, iCol + 1) = "", shi ba C lan de shu ? qing cheng kong ge 。 Application.EnableEvents = False yu Application.EnableEvents = True, zhe shi ge cheng shuang de cheng xu , dang ni yong le qian zhe ji de zai zhi xing qi ta cheng xu hou yao xie shang hou mian de cheng xu 。 ta de mu de zai yi zhi shi jian lian suo zhi xing 。 jian dan de shuo jiu shi , zai B zi duan suo chu fa de shi jian , bu yuan zai qi ta dan yuan ge zai chu fa ling yi ge Worksheet_Change() shi jian 。 VBALesson 9 cheng xu shuo ming ∶ ti hui yi xia Worksheet_Change() shi jian lian suo fan ying 。 Private Sub Worksheet_Change(ByVal Target As Range) Dim iRow As Integer iRow = Target.Row Application.EnableEvents = False Cells(iRow, 3) = Cells(iRow, 3) + Cells(iRow, 2) Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim iRow As Integer iRow = Target.Row 'Application.EnableEvents = False Cells(iRow, 3) = Cells(iRow, 3) + Cells(iRow, 2) 'Application.EnableEvents = True End Sub zhe ge cheng xu de mu de shi yao zai B2 shu ru xin de shu ? shi ,C2 hui jiang B2 shu ru de xin shu ? jia shang C2 yuan you de shu ? cheng xian zai C2 shang 。 zhao shang mian you jia shang Application.EnableEvents = False cheng xu zhi xing dang ran mei wen ti 。 xian zai ni zai Application.EnableEvents = False yu Application.EnableEvents = True qian jia shang 「 '」 kan kan 。 cheng xu qian jia shang 「 '」 de mu de shi yao shi 「 '」 zhi hou de wen zi bian cheng shuo ming wen zi , cheng xu zhi xing shi shi hui tiao guo shuo ming wen zi , bu zhi xing shuo ming wen zi de nei rong 。 cheng xu qian jia shang 「 '」 fu hao hou , wen zi hui bian cheng lv se 。 zhi xing di er ge cheng xu shi , ni jiang fa xian C2 bu hui an ni suo yao qiu de , cheng xian jie guo 。 zhe jiu shi suo wei de shi jian lian suo fan ying 。 qing wen zhe ge hong gai ru he xie ! wo xiang yun xing yi ge hong , jiu neng zai dang qian gong zuo biao B3 shang tian shang yi tiao gong shi ; zhe tiao gong shi de jie guo shi suo you gong zuo biao shang de B4 dan yuan ge de he . qing wen zhe ge hong gai ru he xie . xie xie ! Sub gg() Dim sh As Worksheet, shname$ For Each sh In Worksheets shname = sh.Name ActiveSheet.Range("b3").value = ActiveSheet.Range("b3").value + Worksheets(shname).Range("b4") Next End Sub VBA zhong zen yang chuang jian yi ge ming wei “table” de xin gong zuo biao tong guo VBA bian cheng , hen rong yi tian jia xin de gong zuo biao , dan shi xin biao de ming zi bu zhi zen yang kong zhi , dui yu xin chuang jian de gong zuo biao , you yu qi ming zi bing fei te ding , suo yi jiu bu hao shi yong suo chuang jian de xin biao le 。 bu zhi ge wei you he gao jian 。。。。 Sheets.Add ActiveSheet.Name = "table" qing jiao : ru he yong VBA jian suo biao 1 zhong A lie yu biao 2,3,4,5..... zhong A lie xiang tong de xing bing ba hou zhe zheng xing kao bei dao biao 1 jian suo dao de xing zhong , xie xie !!!! To yxptwq∶ yong zhe cheng xu shi kan kan 。 Sub Copy1() Dim Row_dn1, Row_dnN, i, j, n As Integer Row_dn1 = Sheet1.Range("A65536").End(xlUp).Row k = 1: n = 1 For Each wSheet In ActiveWorkbook.Worksheets With wSheet If .Name "Sheet1" Then Row_dnN = .Range("A65536").End(xlUp).Row For i = 2 To Row_dn1 For j = 2 To Row_dnN If .Cells(j, 1) = Sheet1.Cells(i, 1) Then .Rows(j & ":" & j).Copy Destination:=Sheet1.Rows(Row_dn1 + n & ":" & Row_dn1 + n) n = n + 1 End If Next j Next i End If End With Next wSheet End Sub ru guo yao yong VBA cheng shi shu ru mi ma shi yong xia lie cheng shi ma Sub EnterNewPW() ' cheng shi shuo ming : li yong SendKey shu ru VBAProject mi ma ' zhu yi shi xiang : zhi xing ben cheng shi xu yao zai Excel shi chuang , bu neng zai VBE shi chuang Application.SendKeys "%{F11}", True 'Alt + F11 qie huan dao VBA shi chuang Application.SendKeys "%T", True 'ALT + T gong ju ( fan ti zhong wen shi (T)) Application.SendKeys "e", True ' gong ju (T)-VBproject shu xing (E) Application.SendKeys "^{TAB}", True 'TAB jian ( qie huan dao PAge2 bao hu ye mian ) Application.SendKeys "{+}", True ' xuan qu Checkbox fang kuai ( suo ding zhuan an yi gong jian shi ) '({+} xuan qu , {-} qu xiao xuan qu ) Application.SendKeys "{TAB}", True 'TAB jian ( tiao dao di yi ci shu ru mi ma Textbox myPW = "chijanzen" ' jia she mi ma chijanzen Application.SendKeys myPW, True ' shu ru mi ma Application.SendKeys "{TAB}", True 'TAB jian ( tiao dao di er ci shu ru mi ma Textbox Application.SendKeys myPW, True ' shu ru mi ma Application.SendKeys "{ENTER}", True ' an que ding niu ( yu she zhi ) Application.SendKeys "%{F11}", True ' fan hui Excel shi chuang End Sub mao pao pai xu fa : mao pao pai xu fa zhi suo yi cheng wei “ mao pao pai xu ” shi yin wei zhi jiao xiao de huo shi jiao qing de yuan su fu dao zuo wei ji xu pai xu de yi zu shu de ding bu 。 Sub Macro1() Dim i As Integer Dim j As Integer Dim t as integer Static number(1 To 10) As Integer For i = 1 To 10 number(i) = inputbox“ shu ru yao pai xu de shu :” Next i For i = 10To 2 Step -1 For j = 1 To i – 1 ‘ xia mian jin xing wei zhi jiao huan If number(j) > number(j + 1) Then t = number(j + 1) number(j + 1) = number(j) number(j) = t End If Next j Next i For i = 1 To 20 Print number(i) Next i End sub shou xian ding yi yi ge shu zu : tong guo xun huan lu ru 10 ge zheng shu , ran hou yong yi ge er chong xun huan ce shi qian yi ge shu shi fou da yu hou yi ge shu 。 ru guo da yu ze jiao huan liang ge shu de xia biao , ji jiao huan liang ge shu zai shu zu zhong de wei zhi , jiao huan tong guo yi ge bian liang lai jin xing 。 wo xian yong chuan tong de fang fa jie jue zhe ge wen ti , jing guo bi jiao , xuan yong le jiao wei jian dan de he gao xiao de pai xu fang fa ——“ kuai su pai xu ”, ju ti suan fa ke shen kao shu ju jie gou deng you guan shu ji 。 dui suo you shu ju pai xu hou zai he bing xiang tong shu ju , he bing cheng xu jiao wei jian bian , wo kai shi shi cai yong le zhe zhong fang fa , dan hou lai fa xian dui yu zhe xie de shu ju , xian he bing hou pai xu su du geng kuai , yin wei you da liang xiang tong de shu ju 。 he bing shi cai yong “ biao ji ” suan fa , ju ti ru xia :( she shu ju yi cun fang zai sData() shu zu zhong , jie guo cun dao Queryp() shu zu , Amount shi shu ju ge shu ) ' ba xiang tong yuan su zhi 0 For i = 1 To Amount If sData(i) 0 Then For j = i + 1 To Amount If sData(i) = sData(j) Then sData(j) = 0 Next j End If Next i ' shan chu xiang tong yuan su Queryp(1) = sData(1) k = 1 For i = 2 To Amount If Not (sData(i) = 0) Then k = k + 1 Queryp(k) = sData(i) End If Next i kMax = k ReDim Preserve Queryp(kMax) sui ran zhe yang shi de yun suan su du you suo gao , dan shi reng ran yao jin xing da liang de xun huan yun suan , zhan ju le cheng xu da bu fen de yun suan shi jian 。 yu shi wo yi zhi zai xun mi yi zhong geng wei gao xiao de suan fa 。 gong fu bu fu you xin ren , zai zai xi fen xi shu ju de te zheng , bi jiao le duo zhong fang an zhi hou , wo zhong yu zhao dao le yi zhong xiang dang cheng gong de suan fa , yuan lai yao 3 dao 4 miao de yun suan suo duan dao jin xu 0.1 dao 0.2 miao 。 wo yu dao de shu ju ju you yi xia te zheng :① xiang tong shu ju hen duo ,② zui da 、 zui xiao shu zhi jian xiang cha bu dao 3, ③ dou shi dai liang wei xiao shu de zheng shu 。 zhen dui shu ju de te zheng , wo cai yong le yi xia suan fa : zhen dui shu ju de te zheng , wo cai yong le yi xia suan fa : bu zhou : 1. yong yi ge xun huan zhao chu zheng shu he xiao shu bu fen de zui da 、 zui xiao zhi 。 xiao shu bu fen de zui da 、 zui xiao zhi cheng yi 100 zhuan wei zheng shu 。 2. ding yi yi ge er wei shu zu , xia biao fan wei fen bie shi zheng shu he xiao shu bu fen de zui xiao zhi dao zui da zhi 。 3. zai yong yi ge xun huan ba suo you yuan shu ju tian ru gang cai ding yi de er wei shu zu , tian xie gui ze shi , yuan shu ju de zheng shu he xiao shu bu fen fen bie dui ying er wei shu zu de liang ge xia biao 。 li ru ,“13.51" tian dao “A(13,51)" zhong 。 4. zui hou shun xiang huo ni xiang du qu er wei shu zu zhong de fei ling shu ju ji ke de dao cong xiao dao da huo cong da dao xiao pai lie de shu ju , er qie bu hui han you chong fu shu ju 。 yong VB bian xie de cheng xu ru xia : '**** mi ji xing shu ju chu li **** Dim i As Long, j As Long, k As Long, kMax As Long Dim Queryp() As Single ReDim Queryp(Amount) Dim IntegerPart As Integer, DecimalPart As Integer Dim IPmax As Integer, IPmin As Integer Dim DPmax As Integer, DPmin As Integer Dim DiffDataArray() ' du qu shu ju ReadData IPmax = 0: IPmin = 1000 DPmax = 0: DPmin = 99 For i = 1 To Amount ' zhao zheng shu he xiao shu bu fen de zui da 、 zui xiao zhi IntegerPart = Int(sData(i)) DecimalPart = (sData(i) - IntegerPart) * 100 If IntegerPart > IPmax Then IPmax = IntegerPart ElseIf IntegerPart DPmax Then DPmax = DecimalPart ElseIf DecimalPart 0 Then k = k + 1 Queryp(k) = DiffDataArray(i, j) End If Next j Next i kMax = k ReDim Preserve Queryp(kMax) gai fang fa dui yu ben ren yu dao de zhe zhong “ mi ji xing ” shu ju zui wei you xiao , dan shi ru guo yu shang “ xi shu xing ” shu ju , li ru zui da 、 zui xiao zhi xiang cha ji qian , shen zhi shang wan de shu ju , jiu mei shen me you shi le , er qie hui zhan yong jiao da de nei cun 。 jing guo gai jin , wo de dao le chu li xi shu xing shu ju de gao xiao suan fa 。 gao xiao de qian ti tiao jian tong yang shi yuan shu ju ju you da liang xiang tong shu ju 。 si lu shi zai qian yi zhong fang fa de ji chu shang zeng jia yi ge dan wei shu zu , yong lai bao cun zheng shu bu fen shu ju , bao cun guo cheng zhong yong cha ru fa dui qi jin xing pai xu 。 yin wei you da liang chong fu shu ju , yao pai xu de shu ju liang xiang dui jiao shao 。 dang cong er wei shu zu zhong du qu shu ju shi , yong dan wei shu zu dai ru er wei shu zu de di yi ge xia biao , ju ti dai ma xia : '**** xi shu xing shu ju chu li **** Dim i As Long, j As Long, k As Long, kMax As Long Dim Queryp() As Single ReDim Queryp(Amount) Dim IntegerPart As Integer, DecimalPart As Integer Dim IPmax As Integer, IPmin As Integer Dim DPmax As Integer, DPmin As Integer Dim IPArray() As Integer, IPAamount As Integer ReDim IPArray(Amount) Dim DiffDataArray() ' du qu shu ju ReadData IPmax = 0: IPmin = 1000 DPmax = 0: DPmin = 99 IPAamount = 0 For i = 1 To Amount ' huo qu zheng shu he xiao shu bu fen de zui da zui xiao zhi IntegerPart = Int(sData(i)) DecimalPart = (sData(i) - IntegerPart) * 100 If IntegerPart > IPmax Then IPmax = IntegerPart ElseIf IntegerPart DPmax Then DPmax = DecimalPart ElseIf DecimalPart IPArray(j) Then IPAamount = IPAamount + 1 For k = IPAamount To j + 1 Step -1 IPArray(k) = IPArray(k - 1) Next k IPArray(j) = IntegerPart Exit For ElseIf IntegerPart = IPArray(j) Then Exit For End If Next j If j > IPAamount Then IPAamount = IPAamount + 1 IPArray(IPAamount) = IntegerPart End If Next i ReDim DiffDataArray(IPmin To IPmax, DPmin To DPmax) ' tian ru shu ju For i = 1 To Amount IntegerPart = Int(sData(i)) DecimalPart = (sData(i) - IntegerPart) * 100 DiffDataArray(IntegerPart, DecimalPart) = sData(i) Next i ' ti qu shu ju k = 0 For i = 1 To IPAamount For j = DPmax To DPmin Step -1 If DiffDataArray(IPArray(i), j) 0 Then k = k + 1 Queryp(k) = DiffDataArray(IPArray (i), j) End If Next j Next i kMax = k ReDim Preserve Queryp(kMax) k ReDim Preserve Queryp(kMax) zi dong yin cang biao ge zhong wu shu ju de xing biao 1 shi shu ju yuan , jing chang gai bian ; biao 2 yin yong biao 1 zhong mou lie you shu ju de dan yuan ge ( li yong dong tai wei zhi yi shi xian 。) you yu biao 1 de gai bian , biao 2 de da xiao sui zhi er bian 。 wen ti : ru he shi xian biao 2 zhong mei you shu ju de xing ( you gong shi ) zi dong yin cang ? xie xie ci jiao ! Sub abc() For i = 1 To 300 If Cells(i, 1).value = "" Then Rows(i).Hidden = True Next i End Sub ni xie de yu ju ke yi jie jue yin cang de wen ti , ke shi ru guo wo zhi xing le ta zhi hou , zai zai biao 1 zhong zeng jia shu ju , biao 2 bu hui zi dong xian shi you le shu ju de xing 。 ru he xiu gai ? jiang ci hong she wei zi dong yun xing ( da kai wen jian shi ) Sub abc() For i = 1 To 300 If Cells(i, 1).value "" Then Rows(i).Hidden = false Next i End Sub yong VBA ru he zi dong he bing lie de nei rong ? yong VBA ru he zi dong he bing lie de nei rong ? To hongjian : Sub MergeTest() For i = 3 To 30 Cells(i, 3) = Cells(i, 1) & Chr(10) & Cells(i, 2) Next End Sub 1) chuang jian Excel dui xiang Excel dui xiang mo xing bao kuo le 128 ge bu tong de dui xiang , cong ju xing 、 wen ben kuang deng jian dan de dui xiang dao tou shi biao , tu biao deng fu duo de dui xiang 。 xia mian jian dan jie shao yi xia qi zhong zui chong yao , ye shi yong de zui duo de wu ge dui xiang 。 (1)Application dui xiang Application dui xiang chu yu Excel dui xiang ceng ci jie gou de ding ceng , biao shi Excel zi shen de yun xing huan jing 。 (2)Workbook dui xiang Workbook dui xiang zhi jie di chu yu Application dui xiang de xia ceng , biao shi yi ge Excel gong zuo bo wen jian 。 (3)Worksheet dui xiang Worksheet dui xiang bao han yu Workbook dui xiang , biao shi yi ge Excel gong zuo biao 。 (4)Range dui xiang Range dui xiang bao han yu Worksheet dui xiang , biao shi Excel gong zuo biao zhong de yi ge huo duo ge dan yuan ge 。 (5)Cells dui xiang Cells dui xiang bao han yu Worksheet dui xiang , biao shi Excel gong zuo biao zhong de yi ge dan yuan ge 。 ru guo yao qi dong yi ge Excel, shi yong Workbook he Worksheet dui xiang , xia mian de dai ma qi dong le Excel bing chuang jian le yi ge xin de bao han yi ge gong zuo biao de gong zuo bo : Dim zsbexcel As Excel.Application Set zsbexcel = New Excel.Application zsbexcel.Visible = True ru yao Excel bu ke jian , ke shi zsbexcel.Visible = False zsbexcel.SheetsInNewWorkbook = 1 Set zsbworkbook = zsbexcel.Workbooks.Add 2) she zhi dan yuan ge he qu yu zhi yao she zhi yi zhang gong zuo biao zhong mei ge dan yuan ge de zhi , ke yi shi yong Worksheet dui xiang de Range shu xing huo Cells shu xing 。 With zsbexcel.ActiveSheet .Cells(1, 2).value = "100" .Cells(2, 2).value = "200" .Cells(3, 2).value = "=SUM(B1:B2)" .Range("A3:A9") = " zhong guo ren min jie fang jun " End With yao she zhi dan yuan ge huo qu yu de zi ti 、 bian kuang , ke yi li yong Range dui xiang huo Cells dui xiang de Borders shu xing he Font shu xing : With objexcel.ActiveSheet.Range("A2:K9").Borders  ' bian kuang she zhi .Line = xlBorderLine .Weight = xlThin .ColorIndex = 1 End With With objexcel.ActiveSheet.Range("A3:K9").Font  ' zi ti she zhi .Size = 14 .Bold = True .Italic = True .ColorIndex = 3 End With tong guo dui Excel dan yuan ge he qu yu zhi de ge zhong she zhi de shen ru le jie , ke yi chuang jian ge zhong fu duo 、 mei guan 、 man zu xu yao de 、 ju you zi ji te dian de bao biao 。 3) yu lan ji da yin sheng cheng suo xu yao de gong zuo biao hou , jiu ke yi dui EXCEL fa chu yu lan 、 da yin zhi ling le 。 zsbexcel.ActiveSheet.PageSetup.Orientation = xlPortrait   ' she zhi da yin fang xiang zsbexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4   ' she zhi da yin zhi de da xia zsbexcel.Caption = " da yin yu lan "        ' she zhi yu lan chuang kou de biao ti zsbexcel.ActiveSheet.PrintPreview      ' da yin yu lan zsbexcel.ActiveSheet.PrintOut        ' da yin shu chu tong guo da yin fang xiang 、 da yin zhi zhang da xiao de she zhi , bu duan jin xing yu lan , zhi dao man yi wei zhi , zui zhong jin xing da yin shu chu 。 wei le zai tui chu ying yong cheng xu hou EXCEL bu ti shi yong hu shi fou bao cun yi xiu gai de wen jian , xu shi yong ru xia yu ju : zsbexcel.DisplayAlerts = False zsbexcel.Quit    ' tui chu EXCEL zsbexcel.DisplayAlerts = True ru ci she ji de bao biao da yin shi tong guo EXCEL cheng xu lai hou tai shi xian de 。 dui yu shi yong zhe lai shuo , gen ben kan bu dao ju ti guo cheng , zhi kan dao yi zhang zhang piao liang de bao biao qing yi di bei da yin chu lai le 。 4) ju ti shi li xia mian gei chu yi ge ju ti shi li , ta zai window98、Visual Basic 6.0、 Microsoft Office97 de huan jing xia tiao shi tong guo 。 zai VB zhong qi dong yi ge xin de Standard EXE gong cheng , zai “ gong cheng ” cai dan de “ yin yong ” xuan xiang xia yin yong Excel Object Library; ran hou zai Form zhong tian jia yi ge ming ling an niu cmdExcel; zui hou zai chuang ti zhong shu ru ru xia dai ma : Dim zsbexcel As Excel.Application Private Sub cmdExcel_Click() Set zsbexcel = New Excel.Application zsbexcel.Visible = True zsbexcel.SheetsInNewWorkbook = 1 Set zsbworkbook = zsbexcel.Workbooks.Add With zsbexcel.ActiveSheet.Range("A2:C9").Borders   ' bian kuang she zhi .Line = xlBorderLine .Weight = xlThin .ColorIndex = 1 End With With zsbexcel.ActiveSheet.Range("A3:C9").Font  ' zi ti she zhi .Size = 14 .Bold = True .Italic = True .ColorIndex = 3 End With zsbexcel.ActiveSheet.Rows.HorizontalAlignment = xlVAlignCenter   ' shui ping ju zhong zsbexcel.ActiveSheet.Rows.VerticalAlignment = xlVAlignCenter    ' chui zhi ju zhong With zsbexcel.ActiveSheet .Cells(1, 2).value = "100" .Cells(2, 2).value = "200" .Cells(3, 2).value = "=SUM(B1:B2)" .Cells(1, 3).value = " zhong guo ren min jie fang jun " .Range("A3:A9") = "50" End With zsbexcel.ActiveSheet.PageSetup.Orientation = xlPortrait    ' xlLandscape zsbexcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4 zsbexcel.ActiveSheet.PrintOut zsbexcel.DisplayAlerts = False zsbexcel.Quit zsbexcel.DisplayAlerts = True Set zsbexcel = Nothing ti gao EXCEL zhong VBA de xiao lv fang fa 1: jin liang shi yong VBA yuan you de shu xing 、 fang fa he Worksheet han shu you yu Excel dui xiang duo da bai duo ge , dui xiang de shu xing 、 fang fa 、 shi jian duo bu sheng shu , dui yu chu xue zhe lai shuo ke neng dui ta men bu quan bu le jie , zhe jiu chan sheng le bian cheng zhe jing chang bian xie yu Excel dui xiang de shu xing 、 fang fa xiang tong gong neng de VBA dai ma duan , er zhe xie dai ma duan de yun xing xiao lv xian ran yu Excel dui xiang de shu xing 、 fang fa wan cheng ren wu de su du xiang cha shen da 。 li ru yong Range de shu xing CurrentRegion lai fan hui Range dui xiang , gai dui xiang dai biao dang qian qu 。( dang qian qu zhi yi ren yi kong bai xing ji kong bai lie de zu he wei bian jie de qu yu )。 tong yang gong neng de VBA dai ma xu shu shi xing 。 yin ci bian cheng qian ying jin ke neng duo di le jie Excel dui xiang de shu xing 、 fang fa 。 chong fen li yong Worksheet han shu shi ti gao cheng xu yun xing su du de ji du you xiao de fang fa 。 ru qiu ping yun gong zi de li zi :For Each c In Worksheet(1).Range(″A1:A1000″) Totalvalue = Totalvalue + c.value Next Averagevalue = Totalvalue / Worksheet(1).Range(″ A1:A1000″).Rows.Count er xia mian dai ma cheng xu bi shang mian li zi kuai de duo : Averagevalue="/blog/Application.WorksheetFunction.Average(Worksheets (1).Range(″A1:A1000″)) qi ta han shu ru Count,Counta,Countif,Match,Lookup deng deng , dou neng dai ti xiang tong gong neng de VBA cheng xu dai ma , ti gao cheng xu de yun xing su du 。 fang fa 2: jin liang jian shao shi yong dui xiang yin yong , you qi zai xun huan zhong mei yi ge Excel dui xiang de shu xing 、 fang fa de tiao yong dou xu yao tong guo OLE jie kou de yi ge huo duo ge tiao yong , zhe xie OLE tiao yong dou shi xu yao shi jian de , jian shao shi yong dui xiang yin yong neng jia kuai VBA dai ma de yun xing 。 li ru 1. shi yong With yu ju 。 Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Name=″Pay″ Workbooks(1).Sheets(1).Range(″A1:A1000″).Font.Font ... ze yi xia yu ju bi shang mian de kuai With Workbooks(1).Sheets(1).Range(″A1:A1000″).Font .Name = ″Pay″ .Font = ″Bold″ ... End With 2. shi yong dui xiang bian liang 。 ru guo ni fa xian yi ge dui xiang yin yong bei duo ci shi yong , ze ni ke yi jiang ci dui xiang yong Set she zhi wei dui xiang bian liang , yi jian shao dui dui xiang de fang wen 。 ru : Workbooks(1).Sheets(1).Range(″A1″).value = 100 Workbooks(1).Sheets(1).Range(″A2″).value = 200 ze yi xia dai ma bi shang mian de yao kuai : Set MySheet = Workbooks(1).Sheets(1) MySheet.Range(″A1″).value = 100 MySheet.Range(″A2″).value = 200 3. zai xun huan zhong yao jin liang jian shao dui xiang de fang wen 。 For k = 1 To 1000 Sheets(″Sheet1″).Select Cells(k,1).value = Cells(1,1).value Next k ze yi xia dai ma bi shang mian de yao kuai : Set Thevalue = Cells(1,1).value Sheets(″Sheet1″).Select For k = 1 To 1000 Cells(k,1).value = Thevalue Next k fang fa 3: jian shao dui xiang de ji huo he xuan ze ru guo ni de tong guo lu zhi hong lai xue xi VBA de , ze ni de VBA cheng xu li yi ding chong man le dui xiang de ji huo he xuan ze , li ru Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select deng , dan shi shi shang da duo shu qing kuang xia zhe xie cao zuo bu shi bi xu de 。 li ru Sheets(″Sheet3″).Select Range(″A1″).value = 100 Range(″A2″).value = 200 ke gai wei : With Sheets(″Sheet3″) .Range(″A1″).value = 100 .Range(″A2″).value = 200 End With fang fa 4: guan bi ping mu geng xin ru guo ni de VBA cheng xu qian mian san tiao zuo de bi jiao cha , ze guan bi ping mu geng xin shi ti gao VBA cheng xu yun xing su du de zui you xiao de fang fa , suo duan yun xing shi jian 2/3 zuo you 。 guan bi ping mu geng xin de fang fa : Application.ScreenUpdate = False qing bu yao wang ji VBA cheng xu yun xing jie shu shi zai jiang gai zhi she hui lai : Application.ScreenUpdate = True yi shang shi ti gao VBA yun xing xiao lv de bi jiao you xiao de ji zhong fang fa
󰈣󰈤
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
清凉一夏_清纯美丽
纯白色小礼服
靓丽可人儿_嘉嘉
我的女神_靓丽女生
主题摄影
贵州,让我欢喜,让我神伤。
莫干山
天平秋色
 
>>返回首页<<
 
 
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 热帖排行
 
 
 
 
 
©2005- 王朝网络 版权所有