VBA 基础相关

1. 复选框

添加

Add()里面是位置，也可以用Cells(1,1).Left这样

Dim i
For i = 1 To 10
    ActiveSheet.CheckBoxes.Add(10,10,20,20).Select
    Selection.Characters.Text = "abc"
next

选择

全选

不是打钩

ActiveSheet.CheckBoxes.Select

全打钩

ActiveSheet.CheckBoxes.value = True

全取消打钩

ActiveSheet.CheckBoxes.value = False

删除

注意，没删除一个就会少一个count，所有每次都删第一个

Dim i 
For i = 1 to ActiveSheet.CheckBoxes().Count
    ActiveSheet.CheckBoxes(1).Delete
Next

ActiveX控件

和表达控件差不多，这个有属性。

选择设计模式，然后选中控件，右击，内容。可以看到很多属性，Name就是id，可以在代码中用。Caption是显示的内容。

2. 字符串

删除所有空格

'Delete all space
Function deleteSpace(str As String)
    Dim regx
    Set regx = CreateObject("vbscript.regexp")
    str = Trim(str)
    
    With regx
        .Global = True
        .Pattern = "\s+"
        deleteSpace = .Replace(str, "")
    End With
End Function

3.文件

创建一个excel

Function createFile()
    Workbooks.Add
    ActiveWorkbook.SaveAs fileName:=“路径”, _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Function

打开文件

打开选择对话框，选择文件后，返回文件的路径

Function getFile()
     Dim sFileName, iFile_dir, iFile As String
     Dim DialogObject As Variant
     Dim fso As Object
     
     With Application.FileDialog(msoFileDialogOpen)
         .InitialFileName = ThisWorkbook.Path
         .AllowMultiSelect = False
         '.Show
         'If Not IsArray(DialogObject) Then
         If .Show = 0 Then ' clicked the Cancel button
           Exit Function
         End If
         sFileName = .SelectedItems(1)
     End With
     
     getFile = sFileName
    
'    Set fso = CreateObject("Scripting.FileSystemObject")
'
'    iFile_dir = fso.GetParentFolderName(sFileName)
'    iFile = fso.GetBaseName(sFileName) & "." & fso.GetExtensionName(sFileName)
'    iFile_first = fso.GetBaseName(sFileName)
'    iFile_ext = fso.GetExtensionName(sFileName)
'
'    Range("I14") = iFile
'    Range("I13") = iFile_dir & "\"
'
'    Set fso = Nothing

End Function

选择文件夹

Function getFolder()
     Dim sFolderName
     
     With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a folder "
        If .Show = 0 Then ' clicked the Cancel button
            Exit Function
        End If
        sFolderName = .SelectedItems.Item(1)
     End With
     
     getFolder = sFolderName
End Function

4. 日期

可以用各种个数。yy'mm      dd-mm-yy  等等

Format(Date, "yyyymm")