在VBA中使用Excel工作表公式

在VBA中使用Excel工作表公式
在VBA中使用Excel工作表公式

在VBA中使用Excel工作表公式

在VBA中可以利用Excel工作表函数的强大功能,将VBA和Excel公式与函数联合,使得应用程序更为强大和灵活。

使用WorksheetFunction对象

WorksheetFunction对象的成员就是Excel内置函数,因此,利用该对象可以直接在VBA中使用这些函数。

例如,求单元格区域A1:A5的数值之和,假如将该区域命名为“myRange”,则可以在VBA 中使用下列代码:

Application.WorksheetFunction.Sum(Range("myRange"))

Application.WorksheetFunction.Sum(Range("A1:A5"))

注意,在VBA中当输入WorksheetFunction后再输入一个句点时,就会显示所有的函数列表,如下图1所示。

图1:显示VBA可用工作表函数列表Evaluate方法

使用Worksheet对象或Application对象的Evaluate函数。该函数接受字符串参数,返回引用的公式的值(字符串为公式)或者引用的单元格区域(字符串代表单元格或单元格区域,A1样式)。其语法为:

表达式.Evaluate(Name)

将Excel名称转换为对象或值,其中:

表达式对于Application,表达式可选;对于Chart、DialogSheet和Worksheet,表达式必需。Name必需的字符串。满足Excel命名规范的对象的名称,包括:

(1)A1样式引用。可以以A1样式的引用方式使用任意对单个单元格的引用,引用都被考虑是绝对引用。

(2)单元格区域。可以使用区域、交叉和联合操作符(冒号、空格和逗号)引用单元格区域。

(3)定义的名称。

(4)外部引用。可以使用!操作符引用另一工作簿中的单元格或定义的名称。例如Evaluate("[BOOK1.XLS]Sheet1!A1")。

(5)Chart对象。可以指定任意图表对象名称,例如“图例”、“绘图区”或“系列1”,来访问该对象的属性和方法。例如,Charts("Chart1").Evaluate("Legend")https://www.360docs.net/doc/c015379464.html,返回图例中使用的字体名。

也可以使用方括号代替evaluate函数,使用方括号(例如,"[A1:C5]")与调用带字符串参数的Evaluate方法是相同的。例如,下面的代码等价:

[a1].Value = 25

Evaluate("A1").Value = 25

trigVariable = [SIN(45)]

trigVariable = Evaluate("SIN(45)")

Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).[A1]

Set firstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

使用方括号的优势是代码更简短。使用Evaluate方法的的优势是参数是字符串,因此可以在代码中构造字符串或使用变量。

例如,下面的代码将Sheet1中单元格A1加粗:

Worksheets("Sheet1").Activate

boldCell = "A1"

Application.Evaluate(boldCell).Font.Bold = True

更简短一些,如设置当前工作表中单元格B1为加粗:

[B1].Font.Bold = True

下面的代码返回当前工作表中单元格A1的值:

Evaluate("A1")

下面的代码返回当前工作表中单元格区域A1:A5的值之和:

Evaluate("SUM(A1:A5)")

又例如:

图2:示例工作表

For i = 2 To 10

Debug.Print Evaluate("A" & i), Evaluate("COUNTIF(A1:A" & (i - 1) & ",A" & i & ")") Next i

将打印出A1:A10区域内,某单元格的值在其上面的单元格中出现的次数。

输出结果为:

图3:运行后的结果

可以将上述代码稍作变化,以统计A列中的值,即可以动态调整。

For i = 2 To [COUNTA(A:A)]

Debug.Print Evaluate("A" & i), Evaluate("COUNTIF(A1:A" & (i - 1) & ",A" & i & ")") Next i

注意,上面的代码中使用[COUNTA(A:A)]来统计A列中有多少个数值单元格。

下面的代码选择单元格区域A1:C3:

Range(“offset(a1,,,3,3)”).Select

使用Evaluate函数,可以使用在VBA中不能使用的工作表函数。例如,ISBLANK函数。下面的示例判断单元格A1是否为空,如果为空返回True,不为空则返回False。

MsgBox Evaluate(“=ISBLANK(A1)”)

MsgBox [ISBLANK(A1)]

可以更加灵活地编写代码。

Sub IsActiveCellEmpty()

Dim strFunctionName As String, strCellReference As String

strFunctionName = "ISBLANK"

strCellReference = ActiveCell.Address

MsgBox Evaluate(strFunctionName & "(" & strCellReference & ")")

End Sub

但是,如果只是使用方括号,则不能够在其中包含变量表达式。

示例1

首先,创建隐藏的名称来保存密码。(在名称定义对话框中看不到隐藏的名称,因此可以非常方便地存储信息)

Names.Add Name:="PassWord", RefersTo:="Bazonkas", Visible:=False

接着,在表达式中使用隐藏的数据:

strUserInput = InputBox("请输入密码:")

If strUserInput = [Password] Then

...

示例2

下面的表达式生成一个二维的Variant型数组,100行1列,值为101到200。

vRowArray = [Row(101:200)]

下面的代码将101至200的值赋给单元格区域B1:B100:

[B1:B100] = [Row(101:200)]

以上都比For…Next循环更有效率。

示例3:自定义函数——FindOffset函数

该函数将在某区域内搜索某值,然后从所发现的结果位置偏移x行和x列。例如:

=FindOffset($A$1:$E$10,"Dog",2,3)

在$A$1:$E$10区域查找“Dog”,2(可选的)告诉该函数从内容为“Dog”的单元格处偏移2列,3(可选的)告诉该函数从内容为“Dog”的单元格处偏移3行。因此,如果“Dog”在单元格B5,那么该函数将返回单元格D8(离B5单元格右侧2列下方3行)中的值。

代码如下:

Function FindOffset(LookInRange As Range, FindVal, _

Optional ColOffset As Long, Optional RowOffset As Long)

Dim lCount As Long, lRow As Long

On Error Resume Next

For lCount = 1 To LookInRange.Columns.Count

lRow = Application.WorksheetFunction.Match _

(FindVal, LookInRange.Columns(lCount), 0)

If lRow > 0 Then

FindOffset = LookInRange.Cells(lRow, lCount) _

(RowOffset + 1, ColOffset + 1)

Exit For

End If

Next lCount

On Error GoTo 0

End Function

说明:

For循环的循环次数为Range变量LookInRange的列数,即

LookInRange.Columns.Count

在每次循环中,WorksheetFunction.Match用于查找FindVal代表的值在单元格区域LookInRange中的列数,如果找到,即lRow > 0,将返回真并进入If语句。If语句里面的代码指定开始偏移的单元格位置,即

LookInRange.Cells(lRow, lCount)

变量lRow包含要偏称的行数,lCount为要偏移的列数。因此,

=FindOffset($A$1:$E$10,"Dog",2,3)

如果“Dog”在单元格B5中,LookInRange.Cells(lRow, lCount)将为LookInRange.Cells(5, 2) ,与LookInRange ($A$1:$E$10)相关的单元格为B5,即$A$1:$E$10区域的第5行和第2列。

接着使用Item属性从该单元偏移,也就是(RowOffset + 1, ColOffset + 1)。加1是因为Offset

属性的Item方法基于0,即基点单元格。(注:Offset属性可以接受负值,而Item属性不能) 一旦找到单元格,Exit For及时退出该函数并显示结果。

示例4:在VBA中使用工作表公式查找值

问题:在G列中查找A列中的值,如果找到则将G列中相应行对应的H列中的值复制到该值在A列中相应行的B列中。

图4:示例工作表

例如,单元格A2中的“砖基础”在单元格G3中,则将单元格H3中的值复制到B2中。结果如下:

图5:运行结果

这样的数据也许会有成千上万上,如果使用VBA程序来处理,将大大节约时间以及减小出错的机率。

首先定义一个动态的名称,以便G列中添加项目时能够自动更新。

名称:LookupRange

=OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1)

然后,编写代码如下:

Sub CopyData()

Dim lLastRowA As Long

Dim rngA As Range

Dim rngValueA As Range

Dim lRow As Long

On Error Resume Next

'列A中有数据的最后一行的行号

lLastRowA = Cells(Rows.Count, 1).End(xlUp).Row

'设置需要处理的数据区域

Set rngA = Range("A2:" & "A" & lLastRowA)

'遍历需要处理的数据区域

For Each rngValueA In rngA

'使用工作表函数查找数据所在的行并返回行号

lRow = Application.WorksheetFunction.Match(rngValueA, [LookupRange], 0) + 1

'如果找到则进行相应的操作

If lRow > 0 Then

Range("B" & rngValueA.Row) = Range("H" & lRow)

lRow = 0

End If

Next

End Sub

本来也可以使用两个循环来解决问题,也就是在里面再使用一个For Each循环遍历G列中的内容,但使用工作表公式使得程序代码更简洁,也更快捷。

excelvba常见字典用法集锦及代码详解(全)

常见字典用法集锦及代码详解 前言 凡是上过学校的人都使用过字典,从新华字典、成语词典,到英汉字典以及各种各样数不胜数的专业字典,字典是上学必备的、经常查阅的工具书。有了它们,我们可以很方便的通过查找某个关键字,进而查到这个关键字的种种解释,非常快捷实用。 凡是上过EH论坛的想学习VBA里面字典用法的,几乎都看过研究过northwolves狼版主、oobird版主的有关字典的精华贴和经典代码。我也是从这里接触到和学习到字典的,在此,对他们表示深深的谢意,同时也对很多把字典用得出神入化的高手们致敬,从他们那里我们也学到了很多,也得到了提高。 字典对象只有4个属性和6个方法,相对其它的对象要简洁得多,而且容易理解使用方便,功能强大,运行速度非常快,效率极高。深受大家的喜爱。 本文希望通过对一些字典应用的典型实例的代码的详细解释来

给初次接触字典和想要进一步了解字典用法的朋友提供一点备查的参考资料,希望大家能喜欢。 给代码注释估计是大家都怕做的,因为往往是出力不讨好的,稍不留神或者自己确实理解得不对,还会贻误他人。所以下面的这些注释如果有不对或者不妥当的地方,请大家跟帖时指正批评,及时改正。 字典的简介 字典(Dictionary)对象是微软Windows脚本语言中的一个很有用的对象。 附带提一下,有名的正则表达式(RegExp)对象和能方便处理驱动器、文件夹和文件的(FileSystemObject)对象也是微软Windows脚本语言中的一份子。 字典对象相当于一种联合数组,它是由具有唯一性的关键字(Key)和它的项(Item)联合组成。就好像一本字典书一样,是

excelVBA常用函数

(1) Option Explicit '强制对模块内所有变量进行声明 Option Private Module '标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示 Option Compare Text '字符串不区分大小写 Option Base 1 '指定数组的第一个下标为1 (2) On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler '当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 '恢复正常的错误提示 (5) Application.DisplayAlerts=False '在程序执行过程中使出现的警告框不显示 (6) Application.ScreenUpdating=False '关闭屏幕刷新 Application.ScreenUpdating=True '打开屏幕刷新 (7) Application.Enable.CancelKey=xlDisabled '禁用Ctrl+Break中止宏运行的功能 工作簿 (8) Workbooks.Add() '创建一个新的工作簿 (9) Workbooks(“book1.xls”).Activate '激活名为book1的工作簿 (10) ThisWorkbook.Save '保存工作簿 (11) ThisWorkbook.close '关闭当前工作簿 (12) ActiveWorkbook.Sheets.Count '获取活动工作薄中工作表数 (13) https://www.360docs.net/doc/c015379464.html, '返回活动工作薄的名称

EXCEL VBA 编程常用时间函数

时间函数 Now 返回一个Variant (Date),根据计算机系统设置的日期和时间来指定日期和时间。Date 返回包含系统日期的Variant (Date)。 Time 返回一个指明当前系统时间的Variant (Date)。 Timer 返回一个Single,代表从午夜开始到现在经过的秒数。 TimeSerial(hour, minute, second) 返回一个Variant (Date),包含具有具体时、分、秒的时间。DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]]) 返回Variant (Long) 的值,表示两个指定日期间的时间间隔数目 Second(time) 返回一个Variant (Integer),其值为0 到59 之间的整数,表示一分钟之中的某个秒 Minute(time) 返回一个Variant (Integer),其值为0 到59 之间的整数,表示一小时中的某分钟 Hour(time) 返回一个Variant (Integer),其值为0 到23 之间的整数,表示一天之中的某一钟点 Day(date) 返回一个Variant (Integer),其值为1 到31 之间的整数,表示一个月中的某一日 Month(date) 返回一个Variant (Integer),其值为1 到12 之间的整数,表示一年中的某月Year(date) 返回Variant (Integer),包含表示年份的整数。 Weekday(date, [firstdayofweek]) 返回一个Variant (Integer),包含一个整数,代表某个日期是星期几

Excel中的VBA内部函数有哪些

Excel中的VBA内部函数有哪些 一.测试函数 IsNumeric(x)‘是否为数字,返回Boolean结果,TrueorFalse IsDate(x)‘是否是日期,返回Boolean结果,TrueorFalse IsEmpty(x)‘是否为Empty,返回Boolean结果,TrueorFalse IsArray(x)‘指出变量是否为一个数组。 IsError(expression)‘指出表达式是否为一个错误值 IsNull(expression)‘指出表达式是否不包含任何有效数据(Null)。 IsObject(identifier)‘指出标识符是否表示对象变量 二.数学函数 Sin(X)、Cos(X)、Tan(X)、Atan(x)三角函数,单位为弧度 Log(x)返回x的自然对数 Exp(x)返回ex Abs(x)返回绝对值 Int(number)、Fix(number)都返回参数的整数部分,区别:Int 将-8.4转换成-9,而Fix将-8.4转换成-8 Sgn(number)返回一个Variant(Integer),指出参数的正负号 Sqr(number)返回一个Double,指定参数的平方根 VarType(varname)返回一个Integer,指出变量的子类型 Rnd(x)返回0-1之间的单精度数据,x为随机种子 三.字符串函数

Trim(string)去掉string左右两端空白 Ltrim(string)去掉string左端空白 Rtrim(string)去掉string右端空白 Len(string)计算string长度 Left(string,x)取string左段x个字符组成的字符串 Right(string,x)取string右段x个字符组成的字符串 Mid(string,start,x)取string从start位开始的x个字符组成的字符串 Ucase(string)转换为大写 Lcase(string)转换为小写 Space(x)返回x个空白的字符串 Asc(string)返回一个integer,代表字符串中首字母的字符代码 Chr(charcode)返回string,其中包含有与指定的字符代码相关的字符 四.转换函数 CBool(expression)转换为Boolean型 CByte(expression)转换为Byte型 CCur(expression)转换为Currency型 CDate(expression)转换为Date型 CDbl(expression)转换为Double型 CDec(expression)转换为Decemal型 CInt(expression)转换为Integer型

在Excel VBA中调用Excel自带函数

在Excel VBA中调用Excel自带函数-Excel VBA学习 2010-03-29 19:45:25| 分类: computer|字号订阅 最近在写一个VBA的程序,想自己生成一些报表.便遇到了在VBA中无法使用SUM函数.解决的方法如下: 前段时间为公司实验室编制测试数据库,其中遇到一个问题,怎样在VBA中使用Excel自带的一些函数呢?如min,max,average等等。 本以为能在Excel表格中直接使用的函数也能直接移植到VBA中,结果发现根本行不通,程序报错。后来通过多方查询发现,虽然不能直接引用,但是我们可以曲线救国,excel中表格可以用max函数,在VBA中也可以用,只需通过WorksheetFunction对象就可以引用,后面加函数名称,然后加range来引用单元格。举个例子: Application.WorksheetFunction.Max(range("A1:C5")) 下面就是能在VBA中使用excel自带函数: A__ Acos Acosh And Asin Asinh Atan2 Atanh AveDev Average B__ BetaDist BetaInv BinomDist C__ Ceiling ChiDist ChiInv ChiTest Choose Clean Combin Confidence Correl Cosh Count CountA CountBlank CountIf

Covar CritBinom D__ DAverage Days360 Db DCount DCountA Ddb Degrees DevSq DGet DMax DMin Dollar DProduct DStDev DStDevP DSum DVar DVarP E__ Even ExponDist F__ Fact FDist Find FindB FInv Fisher FisherInv Fixed Floor Forecast Frequency FTest Fv G__ GammaDist

vba常用代码大全

前言 我们平时在工作表单元格的公式中常常使用函数,Excel自带的常用的函数多达300多个,功能强大,丰富多彩,但是在VBA中不能直接应用,必须在函数名前面加上对象,比如:Application.WorksheetFunction.Sum(arg1,arg2,arg3)。 而能在VBA中直接应用的函数也有几十个,下面将逐一详细介绍常用的40个VBA函数,以供大家学习参考。 第1.1例 ASC函数 一、题目: 要求编写一段代码,运行后得到字符串”Excel”的首字母和”e”的ASCII值。 二、代码: Sub 示例_1_01() Dim myNum1%, myNum2% myNum1 = Asc("Excel") '返回69 myNum2 = Asc("e") '返回101 [a1] = "myNum1= ": [b1] = myNum1 [a2] = "myNum2= ": [b2] = myNum2 End Sub 三、代码详解 1、Sub 示例_1_01():宏程序的开始语句。 2、Dim myNum1%, myNum2%:变量myNum1和myNum2声明为整型变量。 也可以写为 Dim myNum1 As Integer 。Integer 变量存储为 16位(2 个字节)的数值形式,其范围为 -32,768 到 32,767 之间。Integer 的类型声明字符是百分比符 号 (%)。

3、myNum1 = Asc("Excel"):把Asc函数的值赋给变量myNum1。 Asc函数返回一个 Integer,代表字符串中首字母的字符的ASCII代码。 语法 Asc(string) 必要的 string(字符串)参数可以是任何有效的字符串表达式。如果 string 中没有包含任何字符,则会产生运行时错误。 4、myNum2 = Asc("e"):把Asc函数的值赋给变量myNum2。这里返回小写字母e 的ASCII代码101。 5、[a1] = "myNum1= ": [b1] = myNum1:把字符串“myNum1= “赋给A1单元格,把变量myNum1的值赋给B1单元格。 6、[a2] = "myNum2= ": [b2] = myNum2:把字符串“myNum2= “赋给A2单元格,把变量myNum2的值赋给B2单元格。 7、End Sub:程序的结束语句,和“Sub示例_1_01()”相对应。 第1.2例 Chr函数 一、题目"": 要求编写一段代码,运用Chr函数将ASCII值转换为对应的字符。 二、代码: Sub 示例_1_02() Dim myChar1$, myChar2$ myChar1 = Chr(69) ' 返回 E。 myChar2 = Chr(101) ' 返回 e。 [a1] = "myChar1= ": [b1] = myChar1 [a2] = "myChar2= ": [b2] = myChar2

Excel中最常用的300句VBA编程语句

最近研究了一下vba编程,主要是针对excel的,现在为了学习,特别从网上搜集了300句常用语句,以备不时之需,也留给网友,不方便的时候查询一下。 定制模块行为 (1) Option Explicit ‘强制对模块内所有变量进行声明 Option Private Module ‘标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示 Option Compare Text ‘字符串不区分大小写 Option Base 1 ‘指定数组的第一个下标为1 (2) On Error Resume Next ‘忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler ‘当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 ‘恢复正常的错误提示 (5) Application.DisplayAlerts=False ‘在程序执行过程中使出现的警告框不显示 (6) Application.ScreenUpdating=False ‘关闭屏幕刷新 Application.ScreenUpdating=True ‘打开屏幕刷新 (7) Appli cation.Enable.CancelKey=xlDisabled ‘禁用Ctrl+Break中止宏运行的功能 工作簿 (8) Workbooks.Add() ‘创建一个新的工作簿 (9) Workbooks(“book1.xls”).Activate ‘激活名为book1的工作簿 (10) ThisWorkbook.Save ‘保存工作簿 (11) ThisWorkbook.close ‘关闭当前工作簿 (12) ActiveWorkbook.Sheets.Count ‘获取活动工作薄中工作表数 (13) https://www.360docs.net/doc/c015379464.html, ‘返回活动工作薄的名称 (14) https://www.360docs.net/doc/c015379464.html, ‘返回当前工作簿名称 ThisWorkbook.FullName ‘返回当前工作簿路径和名称 (15) ActiveWindow.EnableResize=False ‘禁止调整活动工作簿的大小 (16) Application.Window.Arrange xlArrangeStyleTiled ‘将工作簿以平铺方式排列 (17) ActiveWorkbook.WindowState=xlM aximized ‘将当前工作簿最大化 工作表 (18) https://www.360docs.net/doc/c015379464.html,edRange.Rows.Count ‘当前工作表中已使用的行数 (19) Rows.Count ‘获取工作表的行数(注:考虑向前兼容性) (20) Sheets(Sheet1).Name= “Sum” ‘将Sheet1命名为Sum (21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) ‘添加一个新工作表在第一工作表前 (22) ActiveSheet.Move After:=ActiveWorkbook. _ Sheets(ActiveWorkbook.Sheets.Count) ‘将当前工作表移至工作表的最后(23) Worksheets(Array(“sheet1”,”sheet2”)).Select ‘同时选择工作表1和工作表2 (24) Sheets(“sheet1”).Delete或Sheets(1).Delete ‘删除工作表1 (25) ActiveWorkbook.Sheets(i).Name ‘获取工作表i的名称 (26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines

VBA使用Excel工作表函数

在VBA如何使用Excel工作表函数 发布: | 作者: Microsoft | 来源: Microsoft | 查看: 5743次 摘自EXCEL帮助 在 Visual Basic 中使用 Microsoft Excel 工作表函数 在 Visual Basic 语句中可以使用大多数 Microsoft Excel 工作表函数。若要查看可以使用的工作表函数列表,请参阅可用于 Visual Basic 的工作表函数列表。 注释一些工作表函数在 Visual Basic 中是不实用的。例如:Concatenate 函数就不实用,因为在 Visual Basic 中可使用 & 运算符来连接多个文本值。 从 Visual Basic 中调用工作表函数 在 Visual Basic 中,通过 WorksheetFunction 对象可使 用 Microsoft Excel 工作表函数。 以下 Sub 过程使用 Min 工作表函数来决定在某个单元格区域中的最小值。首先,将变量 myRange 声明为 Range 对象,然后将其设置为 Sheet1 上 的 A1:C10 单元格区域。指定另一个变量 answer 为对 myRange 应用 Min 函数的结果。最后,answer 的值就被显示在消息框中。 Sub UseFunction() Dim myRange As Range Set myRange = Worksheets("Sheet1").Range("A1:C10") answer = Application.WorksheetFunction.Min(myRange) MsgBox answer End Sub 如果您使用的工作表函数需要一个单元格区域引用作为参数,则必须指定一 个 Range 对象。例如:您可以用 Match 工作表函数搜索单元格区域。可以在工作表单元格中输入公式,如“=MATCH(9,A1:A10,0)”。但是,您应 在 Visual Basic 过程中指定一个 Range 对象来获取相同的结果。

VBA中的CreateObject函数

Excel VBA之CreateObject 函数 先看一下下面的句子,体会一哈: Set d = CreateObject("") '建立字典对象变量d Set xlApp = CreateObject("") '建立Excel对象变量xlApp Set fso = CreateObject("") '建立文件系统对象变量fso CreateObject 函数创建并返回一个对 ActiveX 对象的引用。 语法 CreateObject(class,[servername]) CreateObject函数的语法有如下部分: class

说明 每个支持自动化的应用程序都至少提供一种对象类型。例如,一个字处理应用程序能会提供 Application对象,Document对象,以及 Toolbar对象。 要创建 ActiveX 对象,只需将 CreateObject返回的对象赋给一个对象变量:'声明一个对象变量来存放该对象 '的引用。Dim as Object 采用后期绑定方式。 Dim ExcelSheet As Object Set ExcelSheet = CreateObject("") 上述代码将启动该应用程序创建该对象,在本例中就是创建一个 Microsoft Excel 电子数据表。对象创建后,就可以在代码中使用自定义的对象变量来引用该对象。在下面的示例中,可以使用对象变量 ExcelSheet 来访问新建对象的属性和方法,以及访问Microsoft Excel 的其它对象,包括应用程序对象和单元格集合。 '设置 Application 对象使 Excel 可见 = True '在表格的第一个单元中写些文本 1).Value = "This is column A, row 1" '将该表格保存到C:\ 目录

常用VBA函数精选

常用VBA函数精选 前言 我们平时在工作表单元格的公式中常常使用函数,Excel自带的常用的函数多达300多个,功能强大,丰富多彩,但是在VBA中不能直接应用,必须在函数名前面加上对象,比如:Application.WorksheetFunction.Sum(arg1,arg2,arg3)。而能在VBA中直接应用的函数也有几十个,下面将逐一详细介绍常用的40个VBA函数,以供大家学习参考。 第1.1例ASC函数 一、题目: 要求编写一段代码,运行后得到字符串 ”Excel”的首字母和”e”的ASCII值。 二、代码: Sub 示例_1_01() Dim myNum1%, myNum2% myNum1 = Asc("Excel") '返回69 myNum2 = Asc("e") '返回101 [a1] = "myNum1= ": [b1] = myNum1 [a2] = "myNum2= ": [b2] = myNum2

End Sub 三、代码详解 1、Sub 示例_1_01():宏程序的开始语句。 2、Dim myNum1%, myNum2%:变量myNum1和myNum2声明为整型变量。 也可以写为Dim myNum1 As Integer 。Integer 变量存储为16位(2 个字节)的数值形式,其范围为-32,768 到32,767 之间。Integer 的类型声明字符是百分比符号(%)。 3、myNum1 = Asc("Excel"):把Asc函数的值赋给变量myNum1。 Asc函数返回一个Integer,代表字符串中首字母的字符的ASCII代码。 语法 Asc(string) 必要的string(字符串)参数可以是任何有效的字符串表达式。如果string 中没有包含任何字符,则会产生运行时错误。 4、myNum2 = Asc("e"):把Asc函数的值赋给变量myNum2。这里返回小写字母e的ASCII代码101。 5、[a1] = "myNum1= ": [b1] = myNum1:把字符串 “myNum1= “赋给A1单元格,把变量

excelvba常用的100条语法

VBA语句集 (第1辑) 定制模块行为 (1) Option Explicit '强制对模块内所有变量进行声明 Option Private Module '标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示 Option Compare Text '字符串不区分大小写 Option Base 1 '指定数组的第一个下标为1 (2) On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler '当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 '恢复正常的错误提示 (5) =False '在程序执行过程中使出现的警告框不显示 (6) =False '关闭屏幕刷新 =True '打开屏幕刷新 (7) '禁用Ctrl+Break中止宏运行的功能 工作簿 (8) () '创建一个新的工作簿 (9) Workbooks(“”).Activate '激活名为book1的工作簿 (10) '保存工作簿 (11) '关闭当前工作簿 (12) '获取活动工作薄中工作表数

(13) '返回活动工作薄的名称 (14) '返回当前工作簿名称 '返回当前工作簿路径和名称 (15) =False '禁止调整活动工作簿的大小 (16) xlArrangeStyleTiled '将工作簿以平铺方式排列 (17) =xlMaximized '将当前工作簿最大化 工作表 (18) '当前工作表中已使用的行数 (19) '获取工作表的行数(注:考虑向前兼容性) (20) Sheets(Sheet1).Name= “Sum” '将Sheet1命名为Sum (21) Before:=Worksheets(1) '添加一个新工作表在第一工作表前 (22) After:=ActiveWorkbook. _ Sheets '将当前工作表移至工作表的最后 (23) Worksheets(Array(“sheet1”,”sheet2”)).Select '同时选择工作表1和工作表2 (24) Sheets(“sheet1”).Delete或Sheets(1).Delete '删除工作表1 (25) (i).Name '获取工作表i的名称 (26) =Not '切换工作表中的网格线显示,这种方法也可以用在其它方面进行相互切换,即相当于开关按钮 (27) =Not '切换工作表中的行列边框显示 (28) '删除当前工作表中所有的条件格式 (29) '取消当前工作表所有超链接

excelVBA常用函数

Option Private Module '标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示 Option Compare Text '字符串不区分大小写 Option Base 1 '指定数组的第一个下标为1 (2) On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler '当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 '恢复正常的错误提示 (5) =False '在程序执行过程中使出现的警告框不显示 (6) =False '关闭屏幕刷新 =True '打开屏幕刷新 (7) '禁用Ctrl+Break中止宏运行的功能 工作簿 (8) () '创建一个新的工作簿 (9) Workbooks(“”).Activate '激活名为book1的工作簿 (10) '保存工作簿 (11) '关闭当前工作簿 (12) '获取活动工作薄中工作表数 (13) '返回活动工作薄的名称 (14) ‘返回当前工作簿名称

‘返回当前工作簿路径和名称 (15) =False ‘禁止调整活动工作簿的大小 (16) xlArrangeStyleTiled ‘将工作簿以平铺方式排列 (17) =xlMaximized ‘将当前工作簿最大化工作表 (18) ‘当前工作表中已使用的行数 (19) ‘获取工作表的行数(注:考虑向前兼容性) (20) Sheets(Sheet1).Name= “Sum” '将Sheet1命名为Sum (21) Before:=Worksheets(1) '添加一个新工作表在第一工作表前 (22) After:=ActiveWorkbook. _ Sheets '将当前工作表移至工作表的最后 (23) Worksheets(Array(“sheet1”,”sheet2”)).Select '同时选择工作表1和工作表2 (24) Sheets(“sheet1”).Delete或 Sheets(1).Delete '删除工作表1 (25) (i).Name '获取工作表i的名称 (26) =Not '切换工作表中的网格线显示,这种方法也可以用在其它方面进行相互切换,即相当于开关按钮 (27) =Not ‘切换工作表中的行列边框显示 (28) ‘删除当前工作表中所有的条件格式 (29) ‘取消当前工作表所有超链接 (30) 或 '将页面设置更改为横向

Excel中宏函数VBA常用语

Excel中宏函数 vba常用语句 (1)Option Explicit 强制对模块内所有变量进行声明 Option Private Module 标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示Option Compare Text 字符串不区分大小写 Option Base 1 指定数组的第一个下标为 1 (2)On Error Resume Next 忽略错误继续执行 VBA 代码,避免出现错误消息 (3)On Error GoTo ErrorHandler 当错误发生时跳转到过程中的某个位置 (4)On Error GoTo 0 恢复正常的错误提示 (5)Application.DisplayAlerts=False 在程序执行过程中使出现的警告框不显示 (6)Application.ScreenUpdating=False 关闭屏幕刷新 (7)Application.ScreenUpdating=True 打开屏幕刷新 (8)Application.Enable.CancelKey=xlDisabled 禁用 Ctrl+Break 中止宏运行的功能

(1)Workbooks.Add() 创建一个新的工作簿 (2)Workbooks("book1.xls").Activate 激活名为 book1 的工作簿 (3)ThisWorkbook.Save 保存工作簿 (4)ThisWorkbook.close 关闭当前工作簿 (5)ActiveWorkbook.Sheets.Count 获取活动工作薄中工作表数 (6)https://www.360docs.net/doc/c015379464.html, 返回活动工作薄的名称 (7)https://www.360docs.net/doc/c015379464.html, 返回当前工作簿名称 (8)ThisWorkbook.FullName 返回当前工作簿路径和名称 (9)ActiveWindow.EnableResize=False 禁止调整活动工作簿的大小 (10)A pplication.Window.Arrange xlArrangeStyleTiled 将工作簿以平铺方式排列 (11)A ctiveWorkbook.WindowState=xlMaximized 将当前工作簿最大化

Excel之VBA编程常用语句300句之一

Excel之VBA编程常用语句300句之一 定制模块行为 (1) Option Explicit '强制对模块内所有变量进行声明 Option Private Module '标记模块为私有,仅对同一工程中其它模块有用,在宏对话框中不显示 Option Compare Text '字符串不区分大小写 Option Base 1 '指定数组的第一个下标为1 (2) On Error Resume Next '忽略错误继续执行VBA代码,避免出现错误消息 (3) On Error GoTo ErrorHandler '当错误发生时跳转到过程中的某个位置 (4) On Error GoTo 0 '恢复正常的错误提示 (5) Application.DisplayAlerts=False '在程序执行过程中使出现的警告框不显示 (6) Application.ScreenUpdating=False '关闭屏幕刷新 Application.ScreenUpdating=True '打开屏幕刷新 (7) Application.Enable.CancelKey=xlDisabled '禁用Ctrl+Break中止宏运行的功能 工作簿 (8) Workbooks.Add() '创建一个新的工作簿 (9) Workbooks(“book1.xls”).Activate '激活名为book1的工作簿 (10) ThisWorkbook.Save '保存工作簿 (11) ThisWorkbook.close '关闭当前工作簿 (12) ActiveWorkbook.Sheets.Count '获取活动工作薄中工作表数 (13) https://www.360docs.net/doc/c015379464.html, '返回活动工作薄的名称 (14) https://www.360docs.net/doc/c015379464.html, ‘返回当前工作簿名称 ThisWorkbook.FullName ‘返回当前工作簿路径和名称 (15) ActiveWindow.EnableResize=False ‘禁止调整活动工作簿的大小 (16) Application.Window.Arrange xlArrangeStyleTiled ‘将工作簿以平铺方式排列 (17) ActiveWorkbook.WindowState=xlMaximized ‘将当前工作簿最大化 工作表 (18) https://www.360docs.net/doc/c015379464.html,edRange.Rows.Count ‘当前工作表中已使用的行数 (19) Rows.Count ‘获取工作表的行数(注:考虑向前兼容性) (20) Sheets(Sheet1).Name= “Sum”'将Sheet1命名为Sum (21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) '添加一个新工作表在第一工作表前 (22) ActiveSheet.Move After:=ActiveWorkbook. _ Sheets(ActiveWorkbook.Sheets.Count) '将当前工作表移至工作表的最后 (23) Worksheets(Array(“sheet1”,”sheet2”)).Select '同时选择工作表1和工作表2 (24) Sheets(“sheet1”).Delete或Sheets(1).Delete '删除工作表1 (25) ActiveWorkbook.Sheets(i).Name '获取工作表i的名称 (26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines '切换工作表中的网格线显示,这种方法也可以用在其它方面进行相互切换,即相当于开关按钮 (27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings ‘切换工作表中的行

excelvba常用代码总结1

Excel VBA常用代码总结1 改变背景色 Range("A1"). = xlNone ColorIndex一览 改变文字颜色 Range("A1"). = 1 获取单元格 Cells(1, 2) Range("H7") 获取范围 Range(Cells(2, 3), Cells(4, 5)) Range("a1:c3") '用快捷记号引用单元格Worksheets("Sheet1").[A1:B5]

选中某sheet Set NewSheet = Sheets("sheet1") 选中或激活某单元格 '“Range”对象的的Select方法可以选择一个或多个单元格,而Activate方法可以指定某一个单元格为活动单元格。 '下面的代码首先选择A1:E10区域,同时激活D4单元格: Range("a1:e10").Select Range("d4:e5").Activate '而对于下面的代码: Range("a1:e10").Select Range("f11:g15").Activate '由于区域A1:E10和F11:G15没有公共区域,将最终选择F11:G15,并激活F11单元格。 获得文档的路径和文件名 '路徑 '名稱 '路徑+名稱 '或将ActiveWorkbook换成thisworkbook 隐藏文档 = False 禁止屏幕更新 = False 禁止显示提示和警告消息 = False

文件夹做成 strPath = "C:\temp\" MkDir strPath 状态栏文字表示 = "计算中" 双击单元格内容变换 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If >= 5And <= 8) Then If = "●"Then = "" Else = "●" End If Cancel = True End If End Sub 文件夹选择框方法1 Set objShell = CreateObject("") Set objFolder = (0, "文件", 0, 0) If Not objFolder Is Nothing Then path= & "\" end if Set objFolder = Nothing Set objShell = Nothing

Excel VBA 常用代码50例

Excel VBA 常用代码50例 001。用命令按扭打印一个sheet1中B2:M30区域中的内容? 我想在Sheet2中制件一个命令按扭, 打印表Sheet1中的[B2:M30] 区域中的内容? 解答:可以将打印区域设为b2:m30,然后打印,如:sheets("sheet1").printarea="b2:m30" sheets("sheet1").printout 随手写的,你可以试试看。最简单的方法是:你先录制宏,在录制宏过程中,跑到页面设置里面,把打印范围设置到你想要的范围。 然后退出,停止录制宏,你就可以得到一些代码! 002。能否对一列中的文字统一去掉最后一个字?这些文字不统一,有些字数多,有些字数少。如何处理?我用{"&-}不行 解答:=REPLACE(A1,LEN(A1),1," ")(在过渡列进行) 003.能否根据单元格数值自动标记序号? 各位大佬,一工作表有两列,“序号”及“金额”,能否将金额不等于0的行自动标上序号呢?如无现成的函数,应怎样设置? 解答:Dim xuhao As Integer

xuhao = 1 Range("b2").Select Do While Selection <> "" If Selection <> 0 Then ActiveCell.Previous.Value = xuhao xuhao = xuhao + 1 End If ActiveCell.Offset(1, 0).Range("a1").Select Loop 004.求教自定义函数 查询了一些自定义函数的例子都是单变量的。自定义函数能否建立“(As Range) As Interger”的函数,应该可以的,请各位大师赐教!请以“∑x2”为例,万分感谢!(该用"For Each ...Next",就是还不知道如何引用Range中的每个值,请高手指点。) 解答:参数使用Range而函数值为Integer是可以的 用for each next循环思路也是对的,应该这样作: dim rg as range dim ivalue as integer for each rg in 参数区域 ivalue=ivalue+rg.value

excel中各种vba写法 - 彷徨豁然开朗 - 博客园

Excel中各种VBA写法- 彷徨......豁然开朗- 博客园 Excel中各种VBA写法 https://www.360docs.net/doc/c015379464.html,/xioxu/archive/2007/09/19/898282. html 先贴一段网上找的excel的各种事件 工作簿对象事件一览表 当工作簿更改、工作簿中的任何工作表更改、加载宏更改或数据透视表更改时,将引发工作簿事件。工作簿上的事件在默认情况下是可用的。若要查看工作簿的事件过程,请用鼠标右键单击处于还原状态或最小化状态的工作簿窗口标题栏,再单击快捷菜单上的“查看代码”。在 “过程”下拉列表框内选择事件名称。 事件说明 Activate激活工作薄时 AddinInstall当工作簿作为加载宏安装时 AddinUninstall工作簿作为加载宏卸载时 BeforeClose关闭工作薄前 BeforePrint打印工作薄(或其中任何内容)之前

BeforeSave保存工作薄前 Deactivate工作簿从活动状态转为非活动状态时NewSheet在工作簿中新建工作表时 Open打开工作簿时 PivotTableCloseConnection在数据透视表关闭与其数据源的连接之后 PivotTableOpenConnection在数据透视表打开与其数据源的连接之后 SheetActivate激活任何一张表时SheetBeforeDoubleClick双击任何工作表时SheetBeforeRightClick鼠标右键单击任一工作表时SheetCalculate工作表重新计算时 SheetChange更改工作表中的单元格时SheetDeactivate任一工作表由活动状态转为非活动状态时SheetFollowHyperlink单击Microsoft Excel 中的任意超链接时 SheetPivotTableUpdate数据透视表的工作表更新之后SheetSelectionChange工作簿中的数据透视表更新之后WindowActivate工作簿的窗口激活时WindowDeactivate工作簿的窗口变为非活动状态时WindowResize工作簿窗口调整大小时工作表事件一览表工作表上的事件在默认情况下是可用的。若要查看工作表的

Excel常用VBA函数实用经典案例

Excel常用VBA函数实用经典案例 ASC函数 一、题目: 要求编写一段代码,运行后得到字符串”Excel”的首字母和”e”的ASCII值。 二、代码: S UB示例_1_01() D IM MY N UM1%, MY N UM2% MY N UM1 = A SC("E XCEL") '返回69 MY N UM2 = A SC("E") '返回101 [A1] = "MY N UM1= ": [B1] = MY N UM1 [A2] = "MY N UM2= ": [B2] = MY N UM2 E ND S UB 三、代码详解 1、Sub 示例_1_01():宏程序的开始语句。 2、Dim myNum1%, myNum2%:变量myNum1和myNum2声明为整型变量。也可以写为 Dim myNum1 As Integer 。Integer 变量存储为 16位(2 个字节)的数值形式,其范围 为 -32,768 到 32,767 之间。Integer 的类型声明字符是百分比符号 (%)。 3、myNum1 = Asc("Excel"):把Asc函数的值赋给变量myNum1。 Asc函数返回一个 Integer,代表字符串中首字母的字符的ASCII代码。语法 Asc(string) 必要的 string(字符串)参数可以是任何有效的字符串表达式。如果 string 中没有包含任何字符,则会产生运行时错误。 4、myNum2 = Asc("e"):把Asc函数的值赋给变量myNum2。这里返回小写字母e的ASCII 代码101。 5、[a1] = "myNum1= ": [b1] = myNum1:把字符串“myNum1= “赋给A1单元格,把变量myNum1的值赋给B1单元格。 6、[a2] = "myNum2= ": [b2] = myNum2:把字符串“myNum2= “赋给A2单元格,把变量myNum2的值赋给B2单元格。 7、End Sub:程序的结束语句,和“Sub示例_1_01()”相对应。 Chr函数 一、题目"": 要求编写一段代码,运用Chr函数将ASCII值转换为对应的字符。 二、代码: S UB示例_1_02()

相关文档
最新文档