ExcelVBA常用代码VSTO版.doc

Excel VBA常用代码VSTO版(C#)1-1 使用Range属性this.RangeA3:F6, B1:C5.Select();1-2 使用Cells属性 for(int icell=1;icell= 0) MessageBox.Show(單元格中有數據有效性!); catch MessageBox.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, Excel.XlFormatConditionOperator.xlBetween, 主機,顯示器); 12-4 自動展開數據有效性下拉列表this.Application.SendKeys(%down);13-1 在單元格中寫入公式this.RangeC1:C10.Formula=sum(A1,B1);13-1 寫入單元格區域數組公式this.RangeC1.FormulaArray = =A1:A2*B1:B2;13-2 檢查單元格是否含有公式 Excel.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(全部單元格不為公式!); 注:因為HasFormula返回的是一個dynamic類型的值,C#的swith貌似并不支持.13-3 判斷單元格公式是否存在錯誤未研究出來,如何調用VBA.IsError,用了Excel自帶函數來處理的. Excel.Range rng = this.RangeA1.Offset0, 1; rng.Formula = =iserror(A1); if (rng.Value) MessageBox.Show(A1單元格錯誤類型為: + this.RangeA1.Text); else MessageBox.Show(A1單元格結果為: + this.RangeA1.Text); 13-4 取得單元格中公式的引用單元格 Excel.Range rng = this.RangeC1.Precedents; MessageBox.Show(公式所引用的單元格有: + rng.Address);13-5 將單元格中的公式轉換為數值 Excel.Range rng = this.RangeC1:C10; rng.Formula = =sum(A1:B1); rng.Value = rng.Value;14-1 判斷單元格是否存在指注 if (this.RangeA1.Comment = null) MessageBox.Show(A1單元格中沒有批注); else MessageBox.Show(A1單元格中批注內容為: + n + this.RangeA1.Comment.Text(); 14-2 為單元格添加批注 Excel.Range rng = this.RangeA1; if (rng.Comment = null) rng.AddComment(rng.Text); rng.Comment.Visible = true; 14-3 刪除單元格中的批注 Excel.Range rng = this.RangeA1; if (rng.Comment != null) rng.Comment.Delete(); 15-1 判斷單元格區域是否存在合并單元格 Excel.Range rng = this.Application.Selection; if (Convert.IsDBNull(rng.MergeCells) MessageBox.Show(區域中包含合并單元格!); 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.DisplayAlerts = false; rng.Merge(); rng.Value = s; this.Application.DisplayAlerts = true;15-3 合并內容相同的連續單元格 int rEnd = this.RangeA65535.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.Offset-1, 0.Value) this.Application.Union(rng, rng.Offset-1, 0).Merge(); 15-4 取消合并單元格時在每個單元格中保留內容 int rEnd = this.RangeA65535.EndExcel.XlDirection.xlUp.Row; int m = this.CellsrEnd, 1.MergeArea.Count-1; this.RangeCells1, 1, CellsrEnd, 1.UnMerge(); this.Application.DisplayAlerts = false; for (int 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 雙擊被保護單元格時不顯示提示消息框 if (Target.Locked) MessageBox.Show(此單元格已保護,不能編輯); Cancel = true; 18-1 重新計算工作表指定區域Excel.XlCalculation oldCalcultion = this.Application.Calculation;this.Application.Calculation = Excel.XlCalculation.xlCalculationManual;this.RangeA1:D10.Calculate();this.Application.Calculation = 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 使用Column屬性和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.RangeA1:A10, this.RangeC1:C10);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 用ActiveSheet属性引用活动工作表 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 = 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(工作簿中含有以下工作表: + 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; 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 = 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;可以找到删除按钮,但是无法禁止,也无法加载单击事件,非常奇怪. 而且在Office 2010里,也无法禁用某个按钮,但是整个菜单是可以的. 27-1 自动建立工作表目录 int i = this.Application.Worksheets.Count; for (int n = 1; n 1 & Target.Row=1; i+) if (this.Application.WorksheetFunction.CountA(this.Rowsi) = 0) this.Rowsi.Delete(); 32-1 删除工作表的重复行 int rngEnd = this.RangeA65535.EndExcel.XlDirection.xlUp.Row; for (int i = rngEnd; i=1; i-) if (this.Application.WorksheetFunction.CountIf(this.Columns1, this.Cellsi, 1) 1) this.Rowsi.Delete(); 33-1 定位删除特定内容所在的行(删除A列中包含”Excel”字符的行 this.Application.DisplayAlerts = false; int rngEnd = this.RangeA65535.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.Rowsi.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;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.RangeA1.CurrentRegion.AdvancedFilter( Excel.XlFilterAction.xlFilterCopy, System.Type.Missing, this.Application.Worksheets2.Cells1, 1, true);38-1 工作表的保护与解除保护 this.Unprotect(12345); this.Cells1,1.Value=100; this.Protect(12345);39-1 奇偶页打印 int pg = this.PageSetup.Pages.Count; for (int i = 1; i = pg; i=i+2) this.PrintOutEx(1, i);40-1 使用工作簿的名称 string str = this.Application.Workbooks工作簿的引用方法.xlsx.Path; MessageBox.Show(str);40-3 使用Th