ExcelVBA常用代碼VSTO版

1、Excel VBA常用代碼VSTO版(C#)1-1 使用Range屬性this.Range"A3:F6, B1:C5".Select();1-2 使用Cells屬性 for(int icell=1;icell<=100;icell+) this.Application.Worksheets2.cellsicell, 1.value = icell; 1-3 使用快捷記號(hào)#N/A1-4 使用Offset屬性this.Range"A1:A3".Offset3, 3.Select();1-5 使用Resize屬性this.Range"A1&qu

2、ot;.Resize3, 3.Select();1-6 使用Union屬性this.Application.Union(this.Range"A1:D4", this.Range"E5:H8").Select();1-7 使用UsedRange屬性this.UsedRange.Select();1-8 使用CurrentRegion屬性this.Range"A5".CurrentRegion.Select();2-1 使用Select方法this.Application.Worksheets3.Activate(); this.App

3、lication.Worksheets3.Range"A1:B10".Select();2-2 使用Activate方法this.Application.Worksheets3.Activate(); this.Application.Worksheets3.Range"A1:B10".Activate(); 注:此處的代碼,可以運(yùn)行,但是只會(huì)選中A1這一個(gè)單元格2-3 使用Goto方法this.Application.Goto(this.Application.Worksheets3.Range"A1:B10", true);3-1

4、 獲得指定行,列中的最后一個(gè)非空單元格Excel.Range rng = this.Range"A65535".EndExcel.XlDirection.xlUp; MessageBox.Show("A列中最后一個(gè)非空單元格是" + rng.Address0, 0 + ",行號(hào)" + rng.Row.ToString() + ",數(shù)值" + rng.Text);4-1 定位單元格 Excel.Range rng = this.UsedRange.SpecialCells(Excel.XlCellType.xlCel

5、lTypeFormulas); rng.Select(); MessageBox.Show("工作表中有公式的單元格為:" + rng.Address);5-1 查找單元格Excel.Range rng, Rng; Rng = this.Range"A:A" string strFind = textBox1.Text; if (strFind.Trim() != string.Empty) rng = Rng.Find(strFind, Rng.CellsRng.Cells.Count, Excel.XlFindLookIn.xlValues, Exc

6、el.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false); if (rng != null) this.Application.Goto(rng, true); else MessageBox.Show("沒(méi)有找到單元格!"); 注:C#中沒(méi)有InputBox,這里用文本框代替,另,C#中沒(méi)有withEnd with語(yǔ)句.5-1 查找單元格重復(fù)數(shù)據(jù) Excel.Range rng, Rng; string FindAddress = string.Emp

7、ty; Rng = this.Range"A:A" string strFind = textBox1.Text; if (strFind.Trim() != string.Empty) rng = Rng.Find(strFind, Rng.CellsRng.Cells.Count, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false); if (rng != null) FindAd

8、dress = rng.Address; do rng.Interior.ColorIndex=6; rng=Rng.FindNext(rng); while(rng != null && rng.Address != FindAddress); 5-2 使用Like運(yùn)算符 C#中沒(méi)有Like運(yùn)算符,可以用正則表達(dá)式來(lái)處理.6-1 替換單元格內(nèi)字符串this.Range"A1:A5".Replace("通州", "南通");7-1 復(fù)制單元格區(qū)域this.Application.DisplayAlerts = fals

9、e; this.Range"A1".CurrentRegion.Copy(this.Application.Worksheets2.Range"A1"); this.Application.DisplayAlerts = true;7-2 復(fù)制單元格區(qū)域時(shí)帶列寬大小this.Range"A1".CurrentRegion.Copy(); Excel.Range rng = this.Application.Worksheets3.Range"A1" rng.PasteSpecial(Excel.XlPasteTyp

10、e.xlPasteColumnWidths); rng.PasteSpecial(Excel.XlPasteType.xlPasteAll); this.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;8-1 使用選擇性粘貼this.Range"A1".CurrentRegion.Copy(); Excel.Range rng = this.Application.Worksheets3.Range"A1" rng.PasteSpecial(Excel.XlPasteType.xlPasteValu

11、es); this.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;8-2 直接賦值的方法Excel.Range rng = this.Application.Worksheets3.Range"A1" Excel.Range Rng = this.Range"A1".CurrentRegion; rng.ResizeRng.Rows.Count, Rng.Columns.Count.Value = Rng.Value;9-1 單元格自動(dòng)進(jìn)入編輯狀態(tài)先在”VSTO 設(shè)計(jì)器生成的代碼”內(nèi)加入this.

12、SelectionChange += new Excel.DocEvents_SelectionChangeEventHandler(工作表1_SelectionChange);然后在事件代碼中輸入 if (Target.Column = 3 && Target.Count = 1) if (Target.Text = string.Empty) this.Application.SendKeys("F2"); 10-1 禁用單元格拖放功能if (this.Application.Intersect(Target, this.Range"A1:A1

13、5") != null) this.Application.CellDragAndDrop = false; else this.Application.CellDragAndDrop = true; 11-1 單元格字體格式設(shè)置Excel.Font rng = this.Range"A1".Font; rng.Name = "宋體" rng.FontStyle = "Bold" rng.Size = 18; rng.ColorIndex = 3; rng.Underline = 2;11-2 設(shè)置單元格內(nèi)部格式 Excel

14、.Interior rng = this.Range"A1".Interior; rng.ColorIndex = 3; rng.Pattern = Excel.XlPattern.xlPatternCrissCross; rng.PatternColorIndex = 6;11-3 為單元格區(qū)域添加邊框 Excel.Borders rng = this.Range"B4:G10".Borders; rng.LineStyle = Excel.XlLineStyle.xlContinuous; rng.Weight = Excel.XlBorderWei

15、ght.xlThin; rng.ColorIndex = 5; Excel.XlColorIndex col = (Excel.XlColorIndex)5; this.Range"B4:G10".BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium,col);11-3 為單元格區(qū)域應(yīng)用多種邊框格式 Excel.XlColorIndex col = (Excel.XlColorIndex)5; Excel.Border rng = this.Range"B4:G10&

16、quot;.BordersExcel.XlBordersIndex.xlInsideHorizontal; Excel.Border Rng = this.Range"B4:G10".BordersExcel.XlBordersIndex.xlInsideVertical; rng.LineStyle = Excel.XlLineStyle.xlDot; rng.Weight = Excel.XlBorderWeight.xlThin; rng.ColorIndex = col; Rng.LineStyle = Excel.XlLineStyle.xlContinuous;

17、 Rng.Weight = Excel.XlBorderWeight.xlThin; Rng.ColorIndex = col; this.Range"B4:G10".BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, col);11-4 靈活設(shè)置單元格的行高列寬 Excel.Range rng1 = this.Range"A1" Excel.Range rng2 = this.Range"B1" rng1.RowHeight = t

18、his.Application.CentimetersToPoints(2); rng1.ColumnWidth = this.Application.CentimetersToPoints(1.5); rng2.RowHeight = this.Application.CentimetersToPoints(1.2); rng2.ColumnWidth = this.Application.CentimetersToPoints(0.3);12-1 單元格中建立數(shù)據(jù)有效性 Excel.Range rng = this.Range"A1:A10" rng.Validatio

19、n.Delete(); rng.Validation.Add(Excel.XlDVType.xlValidateList,Excel.XlDVAlertStyle.xlValidAlertStop,Excel.XlFormatConditionOperator.xlBetween,"1,2,3,4,5,6,7,8");12-2 判斷單元格是否存在數(shù)據(jù)有效性 try if (this.Range"A12".Validation.Type >= 0) MessageBox.Show("單元格中有數(shù)據(jù)有效性!"); catch Mes

20、sageBox.Show("單元格中沒(méi)有數(shù)據(jù)有效性!"); 12-3 動(dòng)態(tài)的數(shù)據(jù)有效性void 工作表1_SelectionChange(Excel.Range Target) if (Target.Column = 1 && Target.Count = 1 && Target.Row > 1) Target.Validation.Delete();Target.Validation.Add( Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, E

21、xcel.XlFormatConditionOperator.xlBetween, "主機(jī),顯示器"); 12-4 自動(dòng)展開(kāi)數(shù)據(jù)有效性下拉列表this.Application.SendKeys("%down");13-1 在單元格中寫(xiě)入公式this.Range"C1:C10".Formula="=sum(A1,B1)"13-1 寫(xiě)入單元格區(qū)域數(shù)組公式this.Range"C1".FormulaArray = "=A1:A2*B1:B2"13-2 檢查單元格是否含有公式 Exc

22、el.Range rng = this.Application.Selection; if (Convert.IsDBNull(rng.HasFormula) MessageBox.Show("公式區(qū)域?yàn)?" + rng.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, 23).Address0, 0); else if(rng.HasFormula) MessageBox.Show("全部單元格為公式!"); else MessageBox.Show("全部單元格不為公式!"); 注

23、:因?yàn)镠asFormula返回的是一個(gè)dynamic類型的值,C#的swith貌似并不支持.13-3 判斷單元格公式是否存在錯(cuò)誤未研究出來(lái),如何調(diào)用VBA.IsError,用了Excel自帶函數(shù)來(lái)處理的. Excel.Range rng = this.Range"A1".Offset0, 1; rng.Formula = "=iserror(A1)" if (rng.Value) MessageBox.Show("A1單元格錯(cuò)誤類型為:" + this.Range"A1".Text); else MessageBo

24、x.Show("A1單元格結(jié)果為:" + this.Range"A1".Text); 13-4 取得單元格中公式的引用單元格 Excel.Range rng = this.Range"C1".Precedents; MessageBox.Show("公式所引用的單元格有:" + rng.Address);13-5 將單元格中的公式轉(zhuǎn)換為數(shù)值 Excel.Range rng = this.Range"C1:C10" rng.Formula = "=sum(A1:B1)" rng

25、.Value = rng.Value;14-1 判斷單元格是否存在指注 if (this.Range"A1".Comment = null) MessageBox.Show("A1單元格中沒(méi)有批注"); else MessageBox.Show("A1單元格中批注內(nèi)容為:" + "n" + this.Range"A1".Comment.Text(); 14-2 為單元格添加批注 Excel.Range rng = this.Range"A1" if (rng.Comment

26、= null) rng.AddComment(rng.Text); rng.Comment.Visible = true; 14-3 刪除單元格中的批注 Excel.Range rng = this.Range"A1" if (rng.Comment != null) rng.Comment.Delete(); 15-1 判斷單元格區(qū)域是否存在合并單元格 Excel.Range rng = this.Application.Selection; if (Convert.IsDBNull(rng.MergeCells) MessageBox.Show("區(qū)域中包含合

27、并單元格!"); else if (rng.MergeCells) MessageBox.Show("區(qū)域中全部為合并單元格!"); else MessageBox.Show("區(qū)域中沒(méi)有合并單元格!"); 15-2 合并單元格時(shí)連接每個(gè)單元格的文本 Excel.Range rng = this.Application.Selection; string s = string.Empty; foreach(Excel.Range Rng in rng) s = s + Rng.Text; this.Application.DisplayAler

28、ts = false; rng.Merge(); rng.Value = s; this.Application.DisplayAlerts = true;15-3 合并內(nèi)容相同的連續(xù)單元格 int rEnd = this.Range"A65535".EndExcel.XlDirection.xlUp.Row; this.Application.DisplayAlerts = false; for (int i = rEnd; i >= 2; i-) Excel.Range rng = this.Cellsi, 1; if (rng.Value = rng.Offse

29、t-1, 0.Value) this.Application.Union(rng, rng.Offset-1, 0).Merge(); 15-4 取消合并單元格時(shí)在每個(gè)單元格中保留內(nèi)容 int rEnd = this.Range"A65535".EndExcel.XlDirection.xlUp.Row; int m = this.CellsrEnd, 1.MergeArea.Count-1; this.RangeCells1, 1, CellsrEnd, 1.UnMerge(); this.Application.DisplayAlerts = false; for (i

30、nt i = 1; i < rEnd+m; i+) Excel.Range rng = this.Cellsi, 1; if (rng.Offset1, 0.Text = string.Empty) rng.Offset1, 0.Value = rng.Value; 16-1 高亮顯示單元格區(qū)域Excel.Range rng = this.Application.Selection;Cells.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;rng.Interior.ColorIndex = 8;17-1 雙擊被保護(hù)單元

31、格時(shí)不顯示提示消息框 if (Target.Locked) MessageBox.Show("此單元格已保護(hù),不能編輯"); Cancel = true; 18-1 重新計(jì)算工作表指定區(qū)域Excel.XlCalculation oldCalcultion = this.Application.Calculation;this.Application.Calculation = Excel.XlCalculation.xlCalculationManual;this.Range"A1:D10".Calculate();this.Application.Ca

32、lculation = oldCalcultion;19-1 錄入數(shù)據(jù)后單元格自動(dòng)保護(hù)if (this.ProtectContents) this.Unprotect("123456"); if (Target.Text != string.Empty) Target.Locked = true; this.Protect("123456"); 20-1 使用單元格的Address屬性if (Target.Address0,0="A1") MessageBox.Show("你選擇了A1單元格");20-2 使用Co

33、lumn屬性和Row屬性 int i=0; if (Target.Column = 1 && Target.Row < 11 && int.TryParse(Target.Text,out i) Target.Offset0, 1.Value = i * 3; 20-3 使用Intersect方法Excel.Range rng = this.Application.Intersect(Target, this.Application.Union(this.Range"A1:A10", this.Range"C1:C10&quo

34、t;);if (rng != null) MessageBox.Show("你選擇了" + Target.Address0, 0 + "單元格"); 21-1 使用工作表的名稱this.Application.Worksheets"工作表2".Activate();21-2 使用工作的索引號(hào)this.Application.Worksheets2.Activate();21-3 使用工作表的代碼名稱MessageBox.Show(this.Application.ActiveSheet.CodeName); 21-4 用ActiveS

35、heet屬性引用活動(dòng)工作表 this.Application.Worksheets2.Select(); MessageBox.Show( this.Application.ActiveSheet.Name);22-1 選擇工作表的方法this.Application.Worksheets2.Select();this.Application.Worksheets2.Activate(); 23-1 使用For遍歷工作表 int wkCount = this.Application.Worksheets.Count; string s = string.Empty; for (int i =

36、1; i <= wkCount; i+) s = s + this.Application.Worksheetsi.Name + "n" MessageBox.Show("工作簿中含有以下工作表:" + "n" + s);23-2 使用ForEach語(yǔ)句 string s = string.Empty; foreach (Excel.Worksheet wk in this.Application.Worksheets) s = s + wk.Name + "n" MessageBox.Show("

37、工作簿中含有以下工作表:" + "n" + s);24-1 在工作表中向下翻頁(yè) Excel.Sheets shs=Globals.ThisWorkbook.Worksheets; Excel.Worksheet wkThis = shs.Application.ActiveSheet; Excel.Worksheet wkNext; int wkIndex = wkThis.Index; int wkCount = shs.Count; if (wkIndex < wkCount) wkNext = (Excel.Worksheet)wkThis.Next;

38、 wkNext.Select(); 25-1 工作表的添加與刪除Excel.Sheets wksThis = this.Application.Worksheets; Excel.Worksheet wsAdd = this.Application.Worksheets.Add(System.Type.Missing, wksThiswksThis.Count);wsAdd.Name = "數(shù)據(jù)"25-1 批量添加工作表 Excel.Sheets wksThis = this.Application.Worksheets; Excel.Worksheet wksNew =

39、null; if (wksThis.Count <= 3) for (int i = 1; i <= 10; i+) wksNew = wksThis.Add(System.Type.Missing, wksThiswksThis.Count); wksNew.Name = "第" + i.ToString() + "個(gè)工作表" 26-1 禁止刪除指定工作表 Office.CommandBarControl cmdCtl =this.Application.CommandBars41.Controls2;可以找到刪除按鈕,但是無(wú)法禁止,也無(wú)法

40、加載單擊事件,非常奇怪. 而且在Office 2010里,也無(wú)法禁用某個(gè)按鈕,但是整個(gè)菜單是可以的. 27-1 自動(dòng)建立工作表目錄 int i = this.Application.Worksheets.Count; for (int n = 1; n <= i; n+) this.Cellsn+1, 1.Value = this.Application.Worksheetsn.Name; 27-1 建立工作表鏈接 int m = this.Application.Worksheets.Count; if (Target.Count = 1) if (Target.Column=1) i

41、f (Target.Row>1 && Target.Row<=(m+1) this.Application.SheetsTarget.Value.Select(); 28-1 工作表的深度隱藏 this.Application.Sheets2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;29-1 防止更改工作表的名稱 void ThisWorkbook_BeforeClose(ref bool Cancel) if (this.Sheets1.Name != "Excel Home")

42、this.Sheets1.Name = "Excel Home" this.Save(); 30-1 工作表中一次插入多行 Excel.Range rng = this.Rows3; rng.Resize3.Insert();31-1 刪除工作表中的空行 Excel.Range rng = this.UsedRange; int rngEnd = this.Cellsrng.Rows.Count,rng.Columns.Count.EndExcel.XlDirection.xlUp.Row; for (int i = rngEnd; i >=1; i+) if (th

43、is.Application.WorksheetFunction.CountA(this.Rowsi) = 0) this.Rowsi.Delete(); 32-1 刪除工作表的重復(fù)行 int rngEnd = this.Range"A65535".EndExcel.XlDirection.xlUp.Row; for (int i = rngEnd; i>=1; i-) if (this.Application.WorksheetFunction.CountIf(this.Columns1, this.Cellsi, 1) > 1) this.Rowsi.Del

44、ete(); 33-1 定位刪除特定內(nèi)容所在的行(刪除A列中包含”Excel”字符的行 this.Application.DisplayAlerts = false; int rngEnd = this.Range"A65535".EndExcel.XlDirection.xlUp.Row; string str = "Excel.*" for (int i = rngEnd; i >= 1; i-) Excel.Range rng = this.Cellsi, 1; if (Regex.IsMatch(rng.Text, str) this.Ro

45、wsi.Delete(); 注:需引用using System.Text.RegularExpressions;34-1 判斷是否選中整行int i = this.Columns.Count; Excel.Range rng = this.Application.Selection; if (rng.Columns.Count = i) MessageBox.Show("你選中了一整行"); else MessageBox.Show("你沒(méi)有選中了一整行"); 35-1 限制工作表的滾動(dòng)區(qū)域this.ScrollArea = "B4:H12&q

46、uot;36-1 復(fù)制自動(dòng)篩選后的數(shù)據(jù)區(qū)域 this.Application.Worksheets2.Cells.Clear(); if (this.FilterMode) this.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy( this.Application.Worksheets2.Cells1, 1); 37-1 使用高級(jí)篩選獲得不重復(fù)記錄 Excel.Range rngSheet2 = this.Application.Worksheets2.Cells; rngSheet2.Clear(); this.Range"A1".CurrentRegion.AdvancedFil