EXCEL 如何制作瀑布图,对于有些数据做图表非常适合用瀑布图，比如生活开支......
常用的几个VBA代码
来源：互联网
2023-02-20 17:33:14 297
今天小编和大家分享几段经常用到的VBA代码，比如调整列宽行高、生成随机字符、创建新的工作表等，可直接复制使用。
工具/原料
- 电脑
- excel
方法/步骤
将选定区域的单元格的列宽和行高调整为最合适的值，可使用代码：
Sub SetColumnAndRow() With ActiveWindow.RangeSelection .Columns.AutoFit .Rows.AutoFit End With End Sub
将选定区域中各单元格的列宽和行高设置为指定的数值，代码：
Sub SetColumnAndRow()
With ActiveWindow.RangeSelection
.ColumnWidth = 5
.RowHeight = 20
End With
End Sub
在工作簿新建一个名称为“数值汇总”的工作表，代码：
Sub AddWorksheet()
On Error Resume Next
Worksheets.Add().Name = "数值汇总"
End Sub
在最后一个工作表的前面新建2个工作表：
Sub Add2Worksheets()
Worksheets.Add Before:=Worksheets(Worksheets.Count), Count:=2
End Sub
在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
获取某个文件夹中所有的文件列表，代码：
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
注意事项
- 插入模块输入代码后，可设置VBA运行快捷键方便调用。
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考！
上一篇：excel中怎么计算百分比 下一篇：条形码制作方法技巧
