常用的几个VBA代码
1、将选定区域的单元格的列宽和行高调整为最合适的值，可使用代码：
Sub SetColumnAndRow() With ActiveWindow.RangeSelection .Columns.AutoFit .Rows.AutoFit End With End Sub
2、将选定区域中各单元格的列宽和行高设置为指定的数值，代码：
Sub SetColumnAndRow()
With ActiveWindow.RangeSelection
.ColumnWidth = 5
.RowHeight = 20
End With
End Sub
3、在工作簿新建一个名称为“数值汇总”的工作表，代码：
Sub AddWorksheet()
On Error Resume Next
Worksheets.Add().Name = "数值汇总"
End Sub
4、在最后一个工作表的前面新建2个工作表：
Sub Add2Worksheets()
Worksheets.Add Before:=Worksheets(Worksheets.Count), Count:=2
End Sub
5、在A1:B100中生成10位长度的随机字符串，代码：
Sub MakeRandomString()
Dim J As Integer
Dim K As Integer
Dim iTemp As Integer
Dim sNumber As String
Dim RandomStr(1 To 100, 1 To 1) As String
Dim bOK As Boolean
Randomize
For J = 1 To 100
sNumber = ""
For K = 1 To 10
Do
iTemp = Int((122 - 48 + 1) * Rnd + 48)
Select Case iTemp
Case 48 To 57, 65 To 90, 97 To 122
bOK = True
Case Else
bOK = False
End Select
Loop Until bOK
bOK = False
sNumber = sNumber & Chr(iTemp)
Next K
RandomStr(J, 1) = sNumber
Next J
Range("A1:B100").Value = RandomStr
End Sub
6、获取某个文件夹中所有的文件列表，代码：
Sub Hqwjjlb()
Dim strFolder As String
Dim varFileList As Variant
Dim FSO As Object, myFile As Object
Dim myResults As Variant
Dim l As Long
'显示打开文件夹对话框
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then Exit Sub '未选择文件夹
strFolder = .SelectedItems(1)
End With
'获取文件夹中的所有文件列表
varFileList = fcnGetFileList(strFolder)
If Not IsArray(varFileList) Then
MsgBox "未找到文件", vbInformation
Exit Sub
End If
'获取文件的详细信息，并放到数组中
ReDim myResults(0 To UBound(varFileList) + 1, 0 To 5)
myResults(0, 0) = "文件名"
myResults(0, 1) = "大小（字节）"
myResults(0, 2) = "创建时间"
myResults(0, 3) = "修改时间"
myResults(0, 4) = "访问时间"
myResults(0, 5) = "完整路径"
Set FSO = CreateObject("Scripting.FileSystemObject")
For l = 0 To UBound(varFileList)
Set myFile = FSO.GetFile(strFolder & "\" & CStr(varFileList(l)))
myResults(l + 1, 0) = CStr(varFileList(l))
myResults(l + 1, 1) = myFile.Size
myResults(l + 1, 2) = myFile.DateCreated
myResults(l + 1, 3) = myFile.DateLastModified
myResults(l + 1, 4) = myFile.DateLastAccessed
myResults(l + 1, 5) = myFile.Path
Next l
fcnDumpToWorksheet myResults
Set myFile = Nothing
Set FSO = Nothing
End Sub
