综合笔记vba

========================================================================

public const PP as long=33
把一些常数设成定数，还可以放到定数模块里

========================================================================

dim a1(),a2()
redim a1(1),a2(1)
a1(0)=1
a2(0)=2
redim a1(2)
redim preserve a2(2)

可以发现a1保存的值消失了，a2的值还在。这时redim时使用preserve的作用。
还有如果数组需要redim，最开始dim 数组是不能设置arr(长度)和arr(lbound,ubound)

========================================================================

sub a1
    path1="***"
    set fso=createobject("scripting.filesystemobject")
    set folders1=fso.getfolder(path1)
    for each i in folders1.files
        debug.pring i.path                '文件的全名
        debug.pring i.name                '不带路径的文件名
    next i
    for each i in folders1.subfolders
        debug.pring i.path                '带路径的文件夹名
        debug.pring i.name                '不带路径的文件夹名
    next i
end sub
getfolder返回的对象有files和folders属性，分别保存了该路径的文件信息和文件夹信息

========================================================================

function dfs(byval folder)
    for each i in folder.files
        debug.pring i.path                
    next i
    for each i in folder.subfolders
        dfs(i)
    next i
end function
递归的打印某文件夹下所有文件名（包括子文件夹里的文件）

========================================================================

nothing表示该对象不在需要，叫系统来回收
empty表示该遍历已经被声明但是没有被赋值
erase用来情况数组的值

========================================================================

join用法同python，用指定分隔符拼接数组的值，如：
dim a(1 to 4)
a(1)=3
a(2)=""
a(3)="a"
x=join(a,"&")            '3%%a%
这里a(4)没有赋值，默认是""

========================================================================

动态运行字符串代码：
先开启权限，否则报错。在选项->安全中心->安全中心设定->宏设定(或者点击开发里的宏安全)
->勾选 信任对vba工程对象模型的访问。
然后在vba代码窗口点击工具->参照设定->勾选 mic*** vis*** bas*** for app***ext

实现的逻辑是把要执行的vba代码动态添加到某处(事先初始化该处),然后调用该处的方法

'asd里运行一个eval没问题，运行两个会打印4次asd，不知道为什么
sub asd()
    eval("msgbox ""asd""" & vbcrlf & "msgbox ""asd""")
    eval("msgbox ""asd""")
end sub

'eval_help是模块名，模块里有如下代码：
public sub eval_helper())
end sub
'其中public必须在第一行，因为下面的代码会删除模块第二行开始的所有行，并插入新代码
'这里设置成最大行数减1，即去掉第一行
'deletelines第二个参数是要删除的行数，超过能删除的最大数会报错
'这里方法名叫eval_helper是因为下面的代码要call eval_helper

'eval应该可以用function而不是sub，未测试
public sub eval(byval code as string)
    set comp=activeworkbook.vbproject.vbcomponents("eval_helper")
    set cm=comp.codemodule
    code=code & vbcrlf & "end sub"
    call cm.deletelines(2,cm/countoflines-1)
    call cmk.insertlines(2,code)
    call eval_helper
end sub

========================================================================

sheet对象没隐藏时[sheet对象.visible]返回true，否则返回false

========================================================================

like:
result= str like pattern,匹配返回true，否则返回false

|表示或，()表示分组，如 a(b|c)匹配 ab和ac

?表示前方字符出现0/1次，如    a?b匹配 b和ab
*表示前方字符出现0次或多次
+表示前方字符出现1次或多次

a{3}只匹配aaa
a{3,33}匹配x个a(3<=x<=33)
a{3,}匹配x个a(3<=x)

#匹配任何一个数字(0-9)，相当于\d
\D匹配非数字
\s匹配空格，换行符，制表符
\S匹配不是空格，换行符，制表符的字符
[a-c]匹配a,b,c则任何一个字符，[!a,b,c]匹配不在[a,b,c]的任何一个字符
如"fukk" like [!a-c]*

========================================================================

取最后一列/行的列/行数，常用方法有：
1：end方法。需要保证参照行/列所在的最后一个值在真正的最后一行/列。
2：sheet.userdrange.rows/columns.count。返回被使用的行/列数。
如果用了3到6行，那么返回4，即不从第一行开始算，慎用。
3：sheet.cells.specialcells(xlcelltypelastcell).row/column。
1和3的区别在于1一定要选对参照行/列，
3被使用过的行再清空值也会被计入，可能会多算。

========================================================================

range对象.value返回一个二维数组，按位置保存了range对象的值

========================================================================

alt+f8可以给sub/宏设置快捷键，设置了快捷键的workbook是打开状态(包括非active状态)才生效

========================================================================

application.statusbar可以修改excel文件左下角的提示信息，可以用来提示代码运行到哪里了

========================================================================

sub a1()
a=input("insert a number")
    select case a
    case 1,3,5: *******
    case 3:    ********
    case else:******
    end select
end sub
如果不进入else前任何case，最后进入else；
一旦进了某个case，一定不会再进其他任何case，所以case3一定不会进入，因为被case1,3,5包围了；
case 1,3,5表示a=1/3/5都进入此case。

========================================================================

err.raise "自定义信息"可以手动抛error，对于逻辑上不该出现的值或者分支，使用这个非常方便

========================================================================

vba的iif方法和excel函数if用法一模一样

========================================================================

cstr/cint/clng/cvar可以进行类型转换，对参数似乎没有类型限制。

========================================================================

typename(a)可以返回a的类型，如string/integer等。
vba的round和excel的round效果不一样
round(3.05,1)=3
round(3.15,1)=3.2
worksheetfunction.round(3.05,1)=3.1
worksheetfunction.round(3.15,1)=3.2

========================================================================

workbooks.open(path,readonly:=withreadonly,updatelinks:=false,ignorereadonlyrecommenderd:=not withreadonly)
打开workbook时可能遇到 是否更新链接/只读 等对话框，这里的参数可以处理这些对话框

========================================================================

在单元格里设置百分比的背景图片：
home->带条件的格式->选中单元格范围->data bar 选中新加格式或者在规则管理里找到已有的格式->根据单元格的值设定，
种类选数值，最小/大值填0/1，填充选第二个可以实现渐变色，还有数据表现格式。

带条件的格式->单元格强调显示规则—>重复值 可以实现重复值的变色显示

========================================================================

sheet.checkboxes("chbname").value可以得到名字叫做chbname的复选框的值，
如果该复选框被选中返回1，否则返回-4146,
右键复选框，左上角可以修改该复选框的名字，修改后按回车才能生效

========================================================================

调用function时如果不用值接受它的返回值，在function名前加call，否则可能报奇怪的错

========================================================================

instr的compare参数设置成vbtextcompare可以忽略大小写，默认不忽略，找不到返回0。
replace的compare参数设置成vbtextcompare可以忽略大小写，默认不忽略。

========================================================================

Sub 使用range.find搜索文本()
    Set ran = ThisWorkbook.Sheets(2).Range("a1:c33")
    With ran
        Set x = .Find("A1", lookat:=xlPart, MatchCase:=True)
        'Set x = .Find("3", lookat:=xlWhole)
        If Not x Is Nothing Then
            addr1 = x.Address
        End If
        While Not x Is Nothing
            Debug.Print x.Value
            Set x = ran.FindNext(x)
            If x.Address = addr1 Then
                GoTo out1
            End If
        Wend
    End With
out1:
End Sub

What    必需    Variant    要搜索的数据。 可为字符串或任意 Microsoft Excel 数据类型。
After    可选    Variant    要在其后开始搜索的单元格。 从用户界面搜索时，这对应于活动单元格的位置。
请注意，After 必须是区域内的单个单元格。 请注意，搜索在此单元格之后开始；在方法回绕到此单元格之前，
不会搜索指定的单元格。如果未指定此参数，搜索将在区域左上角的单元格后面开始。
LookIn    可选    Variant    可以是下列 XlFindLookIn 常量之一：xlFormulas、xlValues、xlComments 或 xlCommentsThreaded。
LookAt    可选    Variant    可以是下列 XlLookAt 常量之一：xlWhole 或 xlPart。
SearchOrder    可选    Variant    可以是以下 XlSearchOrder 常量之一：xlByRows 或 xlByColumns。
SearchDirection    可选    XlSearchDirection    搜索的方向。
MatchCase    可选    Variant    如果为 True，则搜索区分大小写。 默认值为 False。
MatchByte    可选    Variant    仅在选择或安装了双字节语言支持时使用。 如果为 True，则双字节字符仅匹配双字节字符。
如果为 False，则双字节字符匹配其单字节等效字符。
SearchFormat    可选    Variant    搜索格式。

========================================================================

range对象.replace str1,str2可以把range里每个单元格里str1换成str2。
而且可以传递的参数和range.find非常相似。如：

Worksheets("Sheet1").Columns("A").Replace _ 
 What:="SIN", Replacement:="COS", _ 
 SearchOrder:=xlByColumns, MatchCase:=True
 
What    必需    Variant    您希望 Microsoft Excel 搜索的字符串。
Replacement    必需    Variant    替换字符串。
LookAt    可选    Variant    可以是下列 XlLookAt 常量之一：xlWhole 或 xlPart。
SearchOrder    可选    Variant    可以是以下 XlSearchOrder 常量之一：xlByRows 或 xlByColumns。
MatchCase    可选    Variant    如果为 True，则搜索区分大小写。
MatchByte    可选    Variant    仅当在 Microsoft Excel 中选择或安装了双字节语言支持时, 才能使用此参数。 如果为 True，则双字节字符仅匹配双字节字符。 如果为 False，则双字节字符匹配其单字节等效字符。
SearchFormat    可选    Variant    该方法的搜索格式。
ReplaceFormat    可选    Variant    方法的替换格式。

========================================================================

复制粘贴：
.cells(2,4).resize(3,6).copy    .cells(9,9)

========================================================================

inputbox有个default参数，可以用来设置inputbox的默认输入值

========================================================================

.cells(1,1).numberformatlocal="yyyy/m/d h:mm:ss"可以设置成用户自定义的时间格式。
即右键单元格查看格式，不是显示自带的时间格式，而是用户自定义。

========================================================================

range对象.borders(xledgetop).weight=xlmedium修改某范围的顶部边框的厚度

========================================================================

定义function时全加上byval防止意外的bug；
加上optional的参数可有可无，不过这样的参必须排在右边

========================================================================

workbook对象.savecopyas "c:/123/**.xlsx"    另存为

========================================================================

d=createobject("scripting.dictionary")
取值前先检查key是否存在，否则如果不存在，会自动设置d(key)=""，
检查方法是d.exists(key)=true就存在，否则不存在。
put值使用d(key)=val，
d.keys和d.items可以拿到d的key数组和value数组。

========================================================================

excel方法phonetic(ran)可以把ran区域的非空值拼接成一个值

========================================================================

插入->图形可以插入图形，如工具里常见的图形组件

========================================================================

with application
    .screenupdating=false
    .calculation=xlcalculationmanual
    .calculationbeforesave=false
end with

'********************code*********************

with application
    .screenupdating=true
    .calculation=xlcalculationautomatic
end with

以上代码用来关闭屏幕更新和公式自动计算，可以提高运行效率。
把公式计算改成手动，然后恢复成自动时会计算一次，所以一般先关闭再恢复；
但是关闭时默认也会计算一次，所以设置calculationbeforesave=false避免计算。

此外还可以把上面的代码封装成两个方法，需要时调用。

========================================================================

常用技巧：
技巧1：
在一些逻辑上不可能或者不应该的地方手动err.raise，方便调试

技巧2：
让function只有一个出口，方便调试和修改代码

技巧3：
方法名和变量名争取做到 见名知意

技巧4：
function的形参不要和public变量同名，否则编译错误。形参都加上byval

技巧5：
读取有层级关系的excel表格，比如后列是前列的子属性，同列不同行的是并列属性，
可以用数组arr记录每一层的当前值，arr(i)对应第i列，按行遍历excel，
对每一行更新再遍历arr，拼接得到属性链。

========================================================================

strconv(str,vbwide)            
strconv(str,vbnarrow)
strconv(str,vbkatakana)
strconv(str,vbhiragana)
vbkatakana和vbhiragana可以实现平假片假的互转，仅限日文系统；
vbwide和vbnarrow可以实现字符宽度的转换，但并不是全角半角的转换，所以似乎没用。

========================================================================

Sub 把每个工作表的激活单元格设为a1()
    Set ws = ThisWorkbook
    With ws
        For i = .Sheets.Count To 1 Step -1
            .Sheets(i).Activate
            .Sheets(i).Range("a1").Select
        Next i
    End With
End Sub

========================================================================

Sub selection对象的一些属性()
    Set x = Selection
    Debug.Print x.ColumnWidth
    Debug.Print x.Width
    x.ColumnWidth = 22                            '可以设置所在行高和列宽，不能设置单个的宽高
    'x.Width = 44           '报错
    Debug.Print x.ColumnWidth
    
    Debug.Print x.RowHeight
    Debug.Print x.Height
    x.RowHeight = 22
    x.Height = 44           '报错
    Debug.Print x.RowHeight

    x.ColumnWidth = 22
    Debug.Print x.ColumnWidth
    
    Debug.Print x.Top                            'selection到顶部和左侧的距离
    Debug.Print x.Left
End Sub

========================================================================

changesize把插入的截图/形状改变尺寸并置底
addredrange在选中的地方加入100*50的红边透明矩形

Sub changesize()
    On Error Resume Next
    With Selection.ShapeRange
        .ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
        .ZOrder msoSendToBack
    End With
End Sub

Sub addredrange()
    On Error Resume Next
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, Selection.Left, Selection.Top, 100, 50).Select
    Selection.ShapeRange.Fill.Visible = False
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Weight = 2
    End With
End Sub

========================================================================

shell入门用法：
Sub u()
    Shell ("cmd.exe /c ping 127.0.0.1"，0)
    Shell ("cmd.exe /c xcopy f:\1.jpg f:\360Downloads"，0)
    '这里路径必须用\，用/无效；第二个参数设为0能让excel文件不失去焦点
End Sub

========================================================================

========================================================================