Excel VBA常用代码VSTO

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.Worksheets[2].cells[icell, 1].value = icell;}1-3使用快捷记号#N/A1-4使用Offset属性this.Range["A1:A3"].Offset[3, 3].Select();1-5使用Resize属性this.Range["A1"].Resize[3, 3].Select();1-6使用Union属性this.Application.Union(this.Range["A1:D4"], this.Range["E5:H8"]).Select();1-7使用UsedRange属性edRange.Select();1-8使用CurrentRegion属性this.Range["A5"].CurrentRegion.Select();2-1 使用Select方法this.Application.Worksheets[3].Activate();this.Application.Worksheets[3].Range["A1:B10"].Select();2-2 使用Activate方法this.Application.Worksheets[3].Activate();this.Application.Worksheets[3].Range["A1:B10"].Activate();注:此处的代码,可以运行,但是只会选中A1这一个单元格2-3 使用Goto方法this.Application.Goto(this.Application.Worksheets[3].Range["A1:B10"], true);3-1 获得指定行,列中的最后一个非空单元格Excel.Range rng = this.Range["A65535"].End[Excel.XlDirection.xlUp];MessageBox.Show("A列中最后一个非空单元格是" + rng.Address[0, 0] + ",行号" + rng.Row.ToString() + ",数值" + rng.Text);4-1 定位单元格Excel.Range rng = edRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas);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.Cells[Rng.Cells.Count], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false);if (rng != null){this.Application.Goto(rng, true);}else{MessageBox.Show("没有找到单元格!");}}注:C#中没有InputBox,这里用文本框代替,另,C#中没有with……End with语句.5-1 查找单元格重复数据Excel.Range rng, Rng;string FindAddress = string.Empty;Rng = this.Range["A:A"];string strFind = textBox1.Text;if (strFind.Trim() != string.Empty){rng = Rng.Find(strFind, Rng.Cells[Rng.Cells.Count], Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false);if (rng != null){FindAddress = 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 = false;this.Range["A1"].CurrentRegion.Copy(this.Application.Worksheets[2].Range["A1"]);this.Application.DisplayAlerts = true;7-2 复制单元格区域时带列宽大小this.Range["A1"].CurrentRegion.Copy();Excel.Range rng = this.Application.Worksheets[3].Range["A1"];rng.PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths);rng.PasteSpecial(Excel.XlPasteType.xlPasteAll);this.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;8-1 使用选择性粘贴this.Range["A1"].CurrentRegion.Copy();Excel.Range rng = this.Application.Worksheets[3].Range["A1"];rng.PasteSpecial(Excel.XlPasteType.xlPasteValues);this.Application.CutCopyMode = Excel.XlCutCopyMode.xlCut;8-2 直接赋值的方法Excel.Range rng = this.Application.Worksheets[3].Range["A1"];Excel.Range Rng = this.Range["A1"].CurrentRegion;rng.Resize[Rng.Rows.Count, Rng.Columns.Count].Value = Rng.Value;9-1 单元格自动进入编辑状态先在”VSTO 设计器生成的代码”内加入this.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:A15"]) != null){this.Application.CellDragAndDrop = false;}else{this.Application.CellDragAndDrop = true;}11-1 单元格字体格式设置Excel.Font rng = this.Range["A1"].Font; = "宋体";rng.FontStyle = "Bold";rng.Size = 18;rng.ColorIndex = 3;rng.Underline = 2;11-2 设置单元格内部格式Excel.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.XlBorderWeight.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"].Borders[Excel.XlBordersIndex.xlInsideHorizontal];Excel.Border Rng = this.Range["B4:G10"].Borders[Excel.XlBordersIndex.xlInsideVertical];rng.LineStyle = Excel.XlLineStyle.xlDot;rng.Weight = Excel.XlBorderWeight.xlThin;rng.ColorIndex = col;Rng.LineStyle = Excel.XlLineStyle.xlContinuous;Rng.Weight = Excel.XlBorderWeight.xlThin;Rng.ColorIndex = col;this.Range["B4:G10"].BorderAround2(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, col);。