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 使用快捷记号#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(); 注:此处的代码,可以运行,但是只会选中A1这一个单元格2-3 使用Goto方法this.Application.Goto(this.Application.Worksheets3.Range"A1:B10", true);3-1

4、 获得指定行,列中的最后一个非空单元格Excel.Range rng = this.Range"A65535".EndExcel.XlDirection.xlUp; MessageBox.Show("A列中最后一个非空单元格是" + rng.Address0, 0 + ",行号" + rng.Row.ToString() + ",数值" + 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("没有找到单元格!"); 注:C#中没有InputBox,这里用文本框代替,另,C#中没有withEnd with语句.5-1 查找单元格重复数据 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运算符 C#中没有Like运算符,可以用正则表达式来处理.6-1 替换单元格内字符串this.Range"A1:A5".Replace("通州", "南通");7-1 复制单元格区域this.Application.DisplayAlerts = fals

9、e; this.Range"A1".CurrentRegion.Copy(this.Application.Worksheets2.Range"A1"); this.Application.DisplayAlerts = true;7-2 复制单元格区域时带列宽大小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 单元格自动进入编辑状态先在”VSTO 设计器生成的代码”内加入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 单元格字体格式设置Excel.Font rng = this.Range"A1".Font; rng.Name = "宋体" rng.FontStyle = "Bold" rng.Size = 18; rng.ColorIndex = 3; rng.Underline = 2;11-2 设置单元格内部格式 Excel

14、.Interior rng = this.Range"A1".Interior; rng.ColorIndex = 3; rng.Pattern = Excel.XlPattern.xlPatternCrissCross; rng.PatternColorIndex = 6;11-3 为单元格区域添加边框 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 为单元格区域应用多种边框格式 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 靈活設置單元格的行高列寬 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 單元格中建立數據有效性 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 判斷單元格是否存在數據有效性 try if (this.Range"A12".Validation.Type >= 0) MessageBox.Show("單元格中有數據有效性!"); catch Mes

20、sageBox.Show("單元格中沒有數據有效性!"); 12-3 動態的數據有效性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, "主機,顯示器"); 12-4 自動展開數據有效性下拉列表this.Application.SendKeys("%down");13-1 在單元格中寫入公式this.Range"C1:C10".Formula="=sum(A1,B1)"13-1 寫入單元格區域數組公式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("公式區域為:" + rng.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, 23).Address0, 0); else if(rng.HasFormula) MessageBox.Show("全部單元格為公式!"); else MessageBox.Show("全部單元格不為公式!"); 注

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

24、x.Show("A1單元格結果為:" + this.Range"A1".Text); 13-4 取得單元格中公式的引用單元格 Excel.Range rng = this.Range"C1".Precedents; MessageBox.Show("公式所引用的單元格有:" + rng.Address);13-5 將單元格中的公式轉換為數值 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單元格中沒有批注"); else MessageBox.Show("A1單元格中批注內容為:" + "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 判斷單元格區域是否存在合并單元格 Excel.Range rng = this.Application.Selection; if (Convert.IsDBNull(rng.MergeCells) MessageBox.Show("區域中包含合

27、并單元格!"); else if (rng.MergeCells) MessageBox.Show("區域中全部為合并單元格!"); else MessageBox.Show("區域中沒有合并單元格!"); 15-2 合并單元格時連接每個單元格的文本 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 合并內容相同的連續單元格 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 取消合并單元格時在每個單元格中保留內容 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 高亮顯示單元格區域Excel.Range rng = this.Application.Selection;Cells.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;rng.Interior.ColorIndex = 8;17-1 雙擊被保護單元

31、格時不顯示提示消息框 if (Target.Locked) MessageBox.Show("此單元格已保護,不能編輯"); Cancel = true; 18-1 重新計算工作表指定區域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 錄入數據后單元格自動保護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 使用工作的索引号this.Application.Worksheets2.Activate();21-3 使用工作表的代码名称MessageBox.Show(this.Application.ActiveSheet.CodeName); 21-4 用ActiveS

35、heet属性引用活动工作表 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语句 string s = string.Empty; foreach (Excel.Worksheet wk in this.Application.Worksheets) s = s + wk.Name + "n" MessageBox.Show("

37、工作簿中含有以下工作表:" + "n" + s);24-1 在工作表中向下翻页 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 = "数据"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() + "个工作表" 26-1 禁止删除指定工作表 Office.CommandBarControl cmdCtl =this.Application.CommandBars41.Controls2;可以找到删除按钮,但是无法禁止,也无法

40、加载单击事件,非常奇怪. 而且在Office 2010里,也无法禁用某个按钮,但是整个菜单是可以的. 27-1 自动建立工作表目录 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 删除工作表的重复行 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 定位删除特定内容所在的行(删除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("你没有选中了一整行"); 35-1 限制工作表的滚动区域this.ScrollArea = "B4:H12&q

46、uot;36-1 复制自动筛选后的数据区域 this.Application.Worksheets2.Cells.Clear(); if (this.FilterMode) this.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Copy( this.Application.Worksheets2.Cells1, 1); 37-1 使用高级筛选获得不重复记录 Excel.Range rngSheet2 = this.Application.Worksheets2.Cells; rngSheet2.Clear(); this.Range"A1".CurrentRegion.AdvancedFil