VBA 自定义常用函数(备用)

1.获取当前路径所有文件名

Function get_file_arr()
Dim file_arr(), i
i = 1
this_path = ThisWorkbook.Path '获取当前工作簿所在路径
file_list = Dir(this_path & "\" & "*.*") '获取当前路径下的所有文件
Do While Len(file_list)
If file_list <> ThisWorkbook.Name Then
ReDim Preserve file_arr(1 To i)
file_arr(i) = this_path & "\" & file_list
i = i + 1
End If
file_list = Dir
Loop
End Function

2.数组去重

Function arr_deduplication(arr)
Dim i
Set d = CreateObject("scripting.dictionary")
For Each i In arr
'Debug.Print i
If Not d.exists(i) Then d.Add i, ""
Next
arr_deduplication = d.keys
End Function

3.判断sheet是否存在

判断sheet是否存在，返回False或True

Function sheet_is_exist(sheet_name) As Boolean
Dim i As Integer
For i = 1 To Sheets.Count
If Sheets(i).Name = sheet_name Then
sheet_is_exist = True
Exit Function
End If
Next
sheet_is_exist = False
End Function