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


评论

0 评论