详解excel编程常用代码 程序员必看

作者:课课家教育更新于： 2019-03-26 14:48:10

　　用过VB的人都应该知道如何声明变量，在VBA中声明变量和VB中是完全一样的！

　　使用Dim语句

　　Dimaasinteger'声明a为整型变量

　　Dima'声明a为变体变量

　　Dimaasstring'声明a为字符串变量

　　Dimaascurrency,bascurrency,cascurrency'声明a,b,c为货币变量

　　......

　　声明变量可以是：Byte、Boolean、Integer、Long、Currency、Single、Double、Decimal（当前不支持）、Date、String（只限变长字符串）、String*length（定长字符串）、Object、Variant、用户定义类型或对象类型。

　　强制声明变量

　　OptionExplicit

　　说明：该语句必在任何过程之前出现在模块中。

　　声明常数

　　用来代替文字值。

　　Const

　　'常数的默认状态是Private。

　　ConstMy=456

　　'声明Public常数。

　　PublicConstMyString="HELP"

　　'声明PrivateInteger常数。

　　PrivateConstMyIntAsInteger=5

　　'在同一行里声明多个常数。

　　ConstMyStr="Hello",MyDoubleAsDouble=3.4567

　　选择当前单元格所在区域

　　在Excel97中，有一个十分好的功能，他就是把鼠标放置在一个有效数据单元格中，执行该段代码，你就可以将连在一起的一片数据全部选中。只要将该段代码加入到你的模块中。

　　SubMy_Select

　　Selection.CurrentRegion.Select

　　Endsub

　　返回当前单元格中数据删除前后空格后的值

　　submy_trim

　　msgboxTrim(ActiveCell.Value)

　　endsub

　　单元格位移

　　submy_offset

　　ActiveCell.Offset(0,1).Select'当前单元格向左移动一格

　　ActiveCell.Offset(0,-1).Select'当前单元格向右移动一格

　　ActiveCell.Offset(1,0).Select'当前单元格向下移动一格

　　ActiveCell.Offset(-1,0).Select'当前单元格向上移动一格

　　endsub

　　如果上述程序产生错误那是因为单元格不能移动，为了解除上述错误，我们可以往

　　submy_offset之下加一段代码onerrorresumenext

　　注意以下代码都不再添加sub“代码名称”和endsub请自己添加!

　　给当前单元格赋值

　　ActiveCell.Value="你好！！！"

　　给指定单元格赋值

　　例如：Ａ１单元格内容设为"ＨＥＬＬＯ"

　　Range("a1").value="hello"

　　又如：你现在的工作簿在sheet1上，你要往sheet2的Ａ１单元格中插入"ＨＥＬＬＯ"

　　1.

　　sheets("sheet2").select

　　range("a1").value="hello"

　　或

　　2.

　　Sheets("sheet1").Range("a1").Value="hello"

　　说明：

　　1.sheet2被选中，然后在将“HELLO"赋到A1单元格中。

　　2.sheet2不必被选中，即可“HELLO"赋到sheet2的A1单元格中。

　　隐藏工作表

　　'隐藏SHEET1这张工作表

　　sheets("sheet1").Visible=False

　　'显示SHEET1这张工作表

　　sheets("sheet1").Visible=True

　　打印预览

　　有时候我们想把所有的EXCEL中的SHEET都打印预览，请使用该段代码，它将在你现有的工作簿中循环，直到最后一个工作簿结束循环预览。

　　DimmyAsWorksheet

　　ForEachmyInWorksheets

　　my.PrintPreview

　　Nextmy

　　得到当前单元格的地址

　　msgboxActiveCell.Address

　　得到当前日期及时间

　　msgboxdate&chr(13)&time

　　保护工作簿

　　ActiveSheet.Protect

　　取消保护工作簿

　　ActiveSheet.Unprotect

　　给活动工作表改名为"liu"

　　ActiveSheet.Name="liu"

　　打开一个应用程序

　　AppActivate(Shell("C:\\Windows\\CALC.EXE"))

　　增加一个工作表

　　Worksheets.Add

　　删除活动工作表

　　activesheet.delete

　　打开一个工作簿文件

　　Workbooks.OpenFileName:="C:\\MyDocuments\\Book2.xls"

　　关闭活动窗口

　　ActiveWindow.Close

　　单元格格式

　　选定单元格左对齐

　　Selection.HorizontalAlignment=xlLeft

　　选定单元格居中

　　Selection.HorizontalAlignment=xlCenter

　　选定单元格右对齐

　　Selection.HorizontalAlignment=xlRight

　　选定单元格为百分号风格

　　Selection.Style="Percent"

　　选定单元格字体为粗体

　　Selection.Font.Bold=True

　　选定单元格字体为斜体

　　Selection.Font.Italic=True

　　选定单元格字体为宋体20号字

　　WithSelection.Font

　　.Name="宋体"

　　.Size=20

　　EndWith

　　With语句

　　With对象

　　.描述

　　EndWith

　　清除单元格

　　ActiveCell.Clear'删除所有文字、批注、格式

　　返回选定区域的行数

　　MsgBoxSelection.Rows.Count

　　返回选定区域的列数

　　MsgBoxSelection.Columns.Count

　　返回选定区域的地址

　　Selection.Address

　　忽略所有的错误

　　ONERRORRESUMENEXT

　　遇错跳转

　　onerrorgotoerr_handle

　　'中间的其他代码

　　err_handle:'标签

　　'跳转后的代码

　　删除一个文件

　　kill"c:\\1.txt"

　　定制自己的状态栏

　　Application.StatusBar="现在时刻:"&Time

　　恢复自己的状态栏

　　Application.StatusBar=false

　　用代码执行一个宏

　　Application.Runmacro:="text"

　　滚动窗口到a1的位置

　　ActiveWindow.ScrollRow=1

　　ActiveWindow.ScrollColumn=1

　　定制系统日期

　　DimMyDate,MyDay

　　MyDate=#12/12/69#

　　MyDay=Day(MyDate)

　　返回当天的时间

　　DimMyDate,MyYear

　　MyDate=Date

　　MyYear=Year(MyDate)

　　MsgBoxMyYear

　　inputbox<输入框>

　　XX=InputBox("Enternumberofmonthstoadd")

　　得到一个文件名

　　DimkkAsString

　　kk=Application.GetOpenFilename("EXCEL(*.XLS),*.XLS",Title:="提示：请打开一个EXCEL文件：")

　　msgboxkk

　　打开zoom对话框

　　Application.Dialogs(xlDialogZoom).Show

　　激活字体对话框

　　Application.Dialogs(xlDialogActiveCellFont).Show

　　打开另存对话框

　　DimkkAsString

　　kk=Application.GetSaveAsFilename("excel(*.xls),*.xls")

　　Workbooks.Openkk

　　导出带有逗号和引号分隔符的文本文件

　　Excel没有自动将数据导出为文本文件的菜单命令，因此导出的文本文件同时带有逗号和引号分隔符。例如，没有命令能自动创建包含以下内容的文本文件：

　　"Text1","Text2","Text3"

　　但是，您可以使用VBA宏在Excel中创建该功能。这种文件格式是在诸如MicrosoftOfficeAccess2003和MicrosoftOfficeWord2003之类的应用程序中导入文本数据时常见的格式。

　　您可以在如下所示的VBA宏中使用Print语句，导出同时带有逗号和引号分隔符的文本文件。要使该程序正常运行，必须在运行该程序之前选择包含数据的单元格。

　　使用以下示例之前，请执行以下步骤：

　　1.打开一个新工作簿。

　　2.在“工具”菜单中，指向“宏”，然后单击“VisualBasic编辑器”（或者简单地按下ALT+F11组合键）。在“VisualBasic编辑器”中，单击“插入”菜单，然后单击“模块”。

　　3.将以下示例代码键入或粘贴到模块中：

　　SubQuoteCommaExport()

　　DimDestFileAsString

　　DimFileNumAsInteger

　　DimColumnCountAsInteger

　　DimRowCountAsInteger

　　'提示用户指定目标文件名。

　　DestFile=InputBox("Enterthedestinationfilename"&_

　　Chr(10)&"(withcompletepathandextension):",_

　　"Quote-CommaExporter")

　　'获取下一个可用的文件句柄编号。

　　FileNum=FreeFile()

　　'关闭错误检查功能。

　　OnErrorResumeNext

　　'尝试打开目标文件以供输出。

　　OpenDestFileForOutputAs#FileNum

　　'如果出现错误，则报告错误并结束程序。

　　IfErr<>0Then

　　MsgBox"Cannotopenfilename"&DestFile

　　End

　　EndIf

　　'打开错误检查功能。

　　OnErrorGoTo0

　　'循环选择的每一行。

　　ForRowCount=1ToSelection.Rows.Count

　　'循环选择的每一列。

　　ForColumnCount=1ToSelection.Columns.Count

　　'将当前单元格中的文本写入到文件中，文本用引号括起来。

　　Print#FileNum,""""&Selection.Cells(RowCount,_

　　ColumnCount).Text&"""";

　　'检查单元格是否位于最后一列。

　　IfColumnCount=Selection.Columns.CountThen

　　'如果是，则写入一个空行。

　　Print#FileNum,

　　Else

　　'否则，则写入一个逗号。

　　Print#FileNum,",";

　　EndIf

　　'开始ColumnCount循环的下一个迭代。

　　NextColumnCount

　　'开始RowCount循环的下一个迭代。

　　NextRowCount

　　'关闭目标文件。

　　Close#FileNum

　　EndSub

　　4.运行该宏之前，请选择要导出的数据，然后在“工具”菜单中指向“宏”并单击“宏”。

　　5.选择QuoteCommaExport宏，然后单击“运行”。

　　计算包含公式、文本或数字的单元格数量

　　在Excel中，您可以对包含公式、文本或数字的工作表中的单元格数量进行计算，方法是使用“定位条件”对话框选择单元格，然后运行计算所选单元格数量的宏。例如，当您需要设置表格以确定合计列的每一行是否都包含公式而不用手动检查每一行时，此方法可能很有用。

　　选择单元格

　　要选择公式、文本或数字，请执行以下步骤：

　　1.在“编辑”菜单中，单击“定位”，然后单击“定位条件”。

　　2.在“定位条件”对话框中，要选择所有公式，请单击“公式”并确保选中“数字”、“文本”、“逻辑值”以及“错误”复选框。要选择文本，请选择“常量”选项，然后仅单击并选中“文本”复选框。要选择数字，请选择“常量”选项，然后仅单击并选中“数字”复选框。

　　计算所选单元格数量的VBA代码

　　要计算所选的单元格数量并在消息框中显示计算结果，请使用以下程序：

　　SubCount_Selection()

　　DimcellAsObject

　　DimcountAsInteger

　　count=0

　　ForEachcellInSelection

　　count=count+1

　　Nextcell

　　MsgBoxcount&"item(s)selected"

　　EndSub

　　您可以将此程序指定给一个命令按钮，这样，当您单击该按钮时，将显示所选项的数量。

　　使用Saved属性确定工作簿是否已发生更改

　　可以通过检查工作簿的Saved属性来确定工作簿是否已发生更改。根据工作簿是否发生了更改，Saved属性将返回True或False值。

　　注意：用户除了可以通过“事件”设置Saved属性外，还可以通过代码将其设置为True或False。本节包含的示例宏说明了如何在这两种情况下使用Saved属性。

　　工作表中的各种情况（例如存在可变函数）都可能会影响Saved属性。可变函数是指工作表中每次发生更改时都会重新计算的函数，而不管发生的更改是否影响到这些函数。某些常见的可变函数包括RAND()、NOW()、TODAY()和OFFSET()。

　　如果活动工作簿包含未保存的更改，第一个宏将显示如下消息：

　　SubTestForUnsavedChanges()

　　IfActiveWorkbook.Saved=FalseThen

　　MsgBox"Thisworkbookcontainsunsavedchanges."

　　EndIf

　　EndSub

　　下一个宏将关闭包含示例代码的工作簿并放弃对工作簿所做的所有更改：

　　SubCloseWithoutChanges()

　　ThisWorkbook.Saved=True

　　ThisWorkbook.Close

　　EndSub

　　下面的示例宏也将关闭工作簿并放弃更改：

　　SubCloseWithoutChanges()

　　ThisWorkbook.CloseSaveChanges:=False

　　EndSub

　　合并数据列

　　在Excel中，可以使用宏合并两个相邻列中的数据并在包含数据的右侧列中显示结果，完全不需要手动设置公式。本节包含的示例宏就可以实现此功能。

　　SubConcatColumns()

　　DoWhileActiveCell<>""'一直循环，直到活动单元格为空。

　　ActiveCell.Offset(0,1).FormulaR1C1=_

　　ActiveCell.Offset(0,-1)&""&ActiveCell.Offset(0,0)

　　ActiveCell.Offset(1,0).Select

　　Loop

　　EndSub

　　要使用宏，请执行以下步骤：

　　1.打开包含数据的工作簿。

　　2.按ALT+F11组合键激活“VisualBasic编辑器”。

　　3.在“插入”菜单中，单击“模块”以插入一个模块。在模块的代码窗口中键入上面的宏。

　　4.单击“文件”菜单中的“关闭并返回到MicrosoftExcel”。

　　5.选择包含要合并的数据的工作表。

　　6.单击要合并的右侧数据列的第一个单元格。例如，如果单元格A1:A100和B1:B100包含数据，则单击单元格B1。

　　7.在“工具”菜单中，指向“宏”并单击“宏”。选择ConcatColumns宏并单击“运行”。

　　注意：可以用语句ActiveCell.Offset(0,1).Formula替换语句ActiveCell.Offset(0,1).FormulaR1C1。如果仅使用文本和数字（不包含公式），那么两个语句的效果相同。第一个语句末尾使用的R1C1表示第一行的第一列，这是Excel帮助主题中大多数示例使用的形式。

　　数组中的总行数和总列数

　　在Excel中，可以使用数组来计算和操作工作表中的数据，还可以使用宏将某个范围内的单元格中的值存储到一个数组中。本节中的示例宏代码将在一个矩形单元格区域中添加一行和一列，以包含该区域中每一行和每一列中的单元格总数。

　　具体的步骤是，代码从活动工作表上活动单元格周围的当前单元格区域中读取数据。宏将这些数据存储在一个数组中，计算每一行和每一列中的单元格总数，然后将输出显示在工作表中。数组的大小由当前区域中的单元格数量决定。

　　注意：此宏不会在工作表中添加任何公式，因此如果该范围内的单元格总数有变化，则必须重新运行宏。

　　使用以下示例之前，请执行以下步骤：

　　1.打开一个新工作簿。

　　2.在“工具”菜单中，指向“宏”，然后单击“VisualBasic编辑器”（或者简单地按下ALT+F11组合键）。在“VisualBasic编辑器”中的“插入”菜单中，单击“模块”。

　　将以下示例代码键入或粘贴到模块中：

　　SubTotalRowsAndColumns()

　　'此宏假定您已从

　　'要计算单元格总数的矩形区域内

　　'选择了一个单元格或一组单元格。行和列的单元格总数将出现在

　　'当前区域下面的行和右侧的列中。

　　DimrAsInteger

　　DimcAsInteger

　　DimiAsInteger

　　DimjAsInteger

　　DimmyArrayAsVariant

　　'将myArray声明为变量将使数组可以接收

　　'一组单元格。此时，数组将自动转换为

　　'以下标myArray(1,1)开始的数组。

　　'指当前所选单元格周围的区域。

　　WithSelection.CurrentRegion

　　r=.Rows.Count

　　c=.Columns.Count

　　'重新计算总行数和总列数并将结果存储到数组中。

　　myArray=.Resize(r+1,c+1)

　　'在下面的嵌套循环中，变量i跟踪

　　'行号，变量j跟踪

　　'列号。j在可用列中每循环一次，

　　'i就递增一，而j

　　'则重新从一到c循环一次。

　　Fori=1Tor

　　Forj=1Toc

　　'行i的总数

　　myArray(i,c+1)=myArray(i,c+1)+myArray(i,j)

　　'列j的总数

　　myArray(r+1,j)=myArray(r+1,j)+myArray(i,j)

　　'总计

　　myArray(r+1,c+1)=myArray(r+1,c+1)+myArray(i,j)

　　Nextj

　　Nexti

　　'将数组返回工作表，数组中现在包含一个

　　'新行和一个新列，用于存储总数。

　　.Resize(r+1,c+1)=myArray

　　EndWith

　　EndSub

　　3.突出显示要求和区域中的一个单元格，在“工具”菜单中，指向“宏”并单击“宏”。

　　4.选择TotalRowsAndColumns宏，然后单击“运行”。

　　注意：要执行与本示例中的运算类似的运算，可以修改宏代码。例如，要对选定范围内的单元格中包含的值进行减法、乘法或除法运算，可以更改数学运算符。