EXCEL VBA常用到的技巧

创建新工作簿

更好的方法是将其分配给一个对象变量。

下例中，由 Add 方法返回的 Workbook 对象分配给了对象变量 newBook。然后，又设置了 newBook 的若干属性。使用对象变量可以很容易地控制新工作簿。
Sub AddNew()
Set NewBook = Workbooks.Add
    With NewBook
        .Title = "All Sales"
        .Subject = "Sales"
        .SaveAs Filename:="Allsales.xls"
    End With
End Sub

 

将活动单元格从A5移到A6，并将A6单元格的数值保存到x变量中
range("A5 ").select此命令就是选择A5单元格
Activecell.offset(1,0).select
X=activecell.value

 

vba处理单元格数据

本sheet中复制：

　　Sub Macro1()
　　    Range("A1").Copy Range("B1")
　　End Sub

sheet间复制：

 　　Sub mycopy()
　　   Workbooks("book1").Sheets(1).[a1:a10].Copy _
　　   Workbooks("book2").Sheets(1).[b1:b10]
　　End Sub
提示：一行的代码过长，可以用空格加下划线进行换行。

剪切单元格
 
　　Sub mycut()
　　  [a1:a10].Cut [b1]
　　End Sub

删除单元格
　　[a1].Delete Shift:=xlUp '   下方单元格上移
　　[a1].Delete Shift:=xlToLeft '    右侧单元格左移
　　[a1].EntireRow.Delete '   整行删除
　　[a1].EntireColumn.Delete '   整列删除

 

Option Explicit  在程序代码前使用该命令，则变量要在声明后才能使用，否则编译程序无法识别该变量，从而产生错误信息

 

Load语句用于加载窗体，加载后将占用内存，Hide方法使窗体隐藏，但仍在内存中，故不再使用窗体时应使用Uuload语句及时卸载，将内存交还系统，Show方法用来显示一个窗体，格式： 窗体名称.show　模式，模式可取0或1，为1时，不能到其他窗体操作，只有关闭该窗体后才能对其他窗体操作

 

Private Sub CheckBox1_Click()
If CheckBox1.Value Then
MsgBox "333"
Else
MsgBox "666"
End If
End Sub
本段的主要用意在于明白了If CheckBox1.Value Then与If CheckBox1.Value=True Then

 

在一个语句要分行显示的地方加一个或多个空格，加一个下划线＿然后回车转入下一物理行，作用在于代码一行写不下时续行，程序代码中一行较短时可加：把多行连成一行显示

 

if a>15 then
b=10
else
b=100
end if
可改为如下if语句
b=iif(a.15,10,100)

 

x=shell(calc.exe,1)可打开计算器程序

 

call语句格式：Call<子过程名>[(<实际参数表>)]如果过程本身没有参数，则实参和括号可省略，并报参数放在括号中，另一个调用Sub过程的方法是: <子过程名>[<实际参数表>]比前一个少了Call和括号，子过程调用语句的实参在数目、类型、排列上与子过程定义语句的形式参数表一致

 

自定义函数一例,求abc三个数的平均
Private Function Passed(By Val a As integer, By Val b As integer, By Val c As integer)
ave=(a+b+c)/3
End Sub
在定义时必须向函数过程名赋值，而子程序名不能赋值

 

在工作表任意单元格输入=Cell("filename")可获得文件的完整路径、文件名和工作表名

在打开Excel文件时按住Shift键，将不运行VBA过程，可防止宏病毒，单击文件　关闭命令，在点关闭时按住Shift键将在不运行VBA过程的情况下关闭工作簿，可防止关闭时自动运行的宏病毒。

让一个变量得到单元格A1到A5的总和（变量设为X）,
X=Sum(Range(“A1:A5”)) 错
X=Application.WorksheetFunction.Sum(Range(“A1:A5”))正确
也就是说必须通过Application的WorksheetFunction属性间接调用工作表函数

 

ActiveSheet.Next.Select选择活动工作表下一张工作表，
ActiveSheet.Previous.Select选上一张

单元格B2：B10数值不全为空用IF语句表达
For Each cl in ActiveSheet.Range("B2:B10")
If cl.Value <> "" then
msgbox "有非空单元格"
Exit For
endif
next

 

用Range引用单元格和单元格区域
Range("A1") 单元格A1
Range("A1:B5")　从单元格A1到B5区域
Range("A1:B5 ,B1:B7")　多块的选定区域
Range("A:A")　A列
Range("1:1")　第一行
Range("A:C")　A列到C列的区域
Range("1:5")　第1行到第5行的区域
Range("1:1,3:3")　第1、行
Range("A:A,C:C")　A列、C列

 

用Cells及编号引用单元格
Cells(6,1)A6单元格
如果对工作表用Cells属性时不指定索引，表示引用工作表上的所有单元格，下例清除活动工作簿中工作表Sheet1上所有单元格的内容
Worksheets(“sheet1”).Cells.ClearContents

可用变量代入单元格索引值，故Cells属性非常适用于在单元格区域中循环，如：
For counter=1 To 20
Worksheets(“sheet1”).Cells(counter,3).value=counter
Next counter

引用行或列
Rows(1) 第1行
Rows　所有行
Columns(1)  第1列
Columns(“C”) 第3列
Columns 工作表上所有列

可用方括号将A1样式的引用或命名区域的名称括起来，作为Range属性的快捷方式，这样就不必键入Range 和引号，如
Worksheets(“sheet1”).[A1:B2].clearContents
[MyRange].Value=30

用Offset处理按相对于其他单元格的某一位置的常用办法是使用Offset属性，本例将活动工作表上活动单元格下一行和右边三列的单元格的内容设置为下划线，如：
ActiveCell.Offset(1,3).font.Underline=XlDouble

把别的工作表Sheet2数据，读到当前工作表的方法列举
1）[A1]=Sheet2.[A1]    把Sheet2A1单元格的数据，读到A1单元格
2）[A2:A4]=Sheet2.[B1]  把Sheet2单元格B1的数据读到A2:到A4单元格
3）Range(B1”)=Sheet2.Range(“B1”)  把Sheet2工作表单元格B1数据，读到B1单元格
4)Range(“C1:C3”)=Sheet2.Range(“C1”) 把Sheet2工作表单元格C1数据，读到C1:C3
5)Cells(1,4)=Sheet2Cells(1,4)   把Sheet2工作表单元格D1数据，读到D1 单元格
6)Range(Cells(1,5),Cells(5,5)=Sheet2.Cells(1,5) 把sheet2工作表单元格E1数据，读到E1:E5单元格
7)Selection.Value=Sheet2.[F1]  把Sheet2 工作表单元格[F1]数据，读到任何你点选的单元格

 

Sub前有个Private表示是私有子程序，这个子程序不会出现在“宏”对话框中

Sub test()
ActiveSheet.Calculate
End Sub重算活动工作表

 

使用With语句
Workbooks(1).Sheets(1).Range(“A1:A1000”).font.Name=”Pay”
Workbooks(1).Sheets(1).Range(“A1:A1000”).Font.Fontstyle=”Bold”…
改用With语句则运行速度加快
例：With Workbooks(1).Sheets(1).Range(“A1:A1000”).font
.Name=”Pay”
.Fontstyle= Bold”
…
End With

 

用set设置对象变量，以减少对象的访问，如：
Set MyRange=Workbooks(1).Sheets(1)
Mysheet.Range(“A1”).Value=100
Mysheet.Range(“A2”).Value=200
比直接用Workbooks(1).Sheets(1). Range(“A1”).Value=100
Workbooks(1).Sheets(1). Range(“A2”).Value=200运行快

在循环中要尽是减少对象的访问
For k=1 To 100
Sheets(“sheet1”).select
Cells(k,1).value=Cells(1,1).Value
Next k
更快的代码是
set TheValue=Cells(1,1).Value
Sheets(“sheet1”).select
For k=1 To 100
Cells(k,1).value=TheValue

 

减少对象的激活和选择
如果你是通过录制宏的来学习VBA的程序里一定充满了对象的激活和选择，如Workbooks(XXX).active 、Sheets(XXX).Select  、Range(XXX).Select等，但事实上大多数情况下这些操作不是必须的，如
Sheets(“sheet1”).Select
Range(“A1”).Value=100
Range(“A2”).Value=200
可改为With sheets(“Sheet3”)
.Range (“A1”)=100
.Range (“A2”)=200

关闭屏幕更新是提高运行速度的最有效的办法，推荐使用
Application.ScreenUpdate=False
程序运行后再改回来

 

ActiveWindow.DisplyGridlines=False  此句用来关闭网格线。

 

若A1-A6中有大于0和小于0的数，请问怎样将其中小于0的数所在的行自动删除。
for i=6 to 1 step -1
if cells(i,1)<0 then rows(i).Delete
next i

 

如何获取一个月的最大天数？
:"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1为"2001-03-01

 

能否在EXCEL中定时打印?
Private Sub Workbook_Open()
Application.OnTime "9:30:00", "wlqPrint"
'将"9:30:00"改为要自动打印的时间
End Sub
Sub wlqPrint() '打印
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

勾怎么输入
按住ALT键输入41420后放开ALT键√

 

如果一个单元格中既有数字又有字母，怎么提取其中的数字呢？
Function getnumber(rng As String) As String
'自定义函数作用：提取当前单元格中的数字
Dim mylen As Integer
Dim mystr As String
mylen = Len(rng)
For I = 1 To mylen
 mystr = Mid(rng, I, 1)
 If Asc(mystr) >= 48 And Asc(mystr) <= 57 Then
getnumber = getnumber & mystr
   End If
Next I
End Function

 

在B1中同步显示A列中最后一行的内容
最后一行为文本：
=offset($b$1,MATCH(CHAR(65535),b:b)-1,)
最后一行为数字：
=offset($b$1,MATCH(9.9999E+307,b:b)-1,)
或者：=lookup(2,1/(b1:b1000<>""),b1:b1000)

 

请问如何 设置单元格，令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror(find(" ",a1))
数据－－有效性－－自定义－－公式
=len(a1)=len(trim(a1))

 

原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....
现在想用offset来简化公式，我只会用比如a4=offset(a1,3,0)的，不会弄出一个数组出来实现上面的效果
sum(n(offset(a1,(row(1:10)-1)*3,)))
row(1:10)={1;2;3;4;5;6;7;8;9;10}
(row(1:10)-1)*3={0;3;6;9;12;15;18;21;24;27}
自A1向下偏移，就是a1、a4、a7、a10、a13、、、a28

 

工作表的A1单元格为B1:H1的总和,B1:H1又有其它公式,如何让A1当为负数时,让它不显示负数而显示0,其它时候为正常总和!
if(sum(B1:H1)<0,0,sum(B1:H1))

 

每次默认的都是自动更新,所以我通过[编辑]-[链接]-[启动提示]里设置选择[不显示该警告,但是更新链接],可是设置好了以后,每次重新打开工作薄,都提示是否更新一下链接文件内容...我不知道为什么会这样,我希望得到帮助
这个管用，而且自动更新链接。
在“工具”菜单上，单击“选项”，再单击“编辑”选项卡， 清除“请求自动更新链接”复选框。

 

转换A1与R1C1引用样式
Sub RC_A1()
    With Application
        If .ReferenceStyle = xlR1C1 Then
            .ReferenceStyle = xlA1
        Else
            .ReferenceStyle = xlR1C1
        End If
    End With
End Sub

 

目的：表中>50000的单元格红色显示。做法：选择整张表，在条件格式命令中，设置了“>50000以红色填充单元格“的条件，出现的问题：表头(数值为文本)的单元格也呈红色显示。我知道，原因是因为区域选择得不对，如果只选择数字区域不会出现这种情况，如果表结构简单，则好处理，如果表格结构复杂，这样选择就很麻烦。有没有办法选择整张表，但是表头(数值为文本)的单元格不被条件格式。
答：条件格式设置公式=--A1>50000

 

如何打印行号列标？
   答：文件菜单-----页面设置---工作表----在打印选项中的行号列标前打勾。

 

如何用函数来获取单元格地址
=ADDRESS(ROW(),COLUMN())

 

如何用公式求出最大值所在的行？
如A1：A10中有10个数，怎么求出最大的数在哪个单元格？
=MATCH(LARGE(A1:A10,1),A1:A10,0)
=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)
=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)

 

在一年中，显示是第几天用什么函数呢？
=a1-date(year(a1),1,0)

 

如何让工作表奇数行背景是红色偶数行背景是蓝色？
用条件格式
=ROW()/2=INT(ROW()/2)设定颜色
条件格式: 公式为 =MOD(ROW(),2)=0

 

 

 

 =B3&"-"&H3&"-"&INT(RAND()*10)
=CONCATENATE(B2,"-",H2,"-",INT(RAND()*10))今天玩excel字串合并