时间戳

首页 > 解决方案 > 如何让这个数组代码正确输出到我的消息框中？

excel - 如何让这个数组代码正确输出到我的消息框中？

问题描述

我有一个电子表格，用户可以与之交互以指定需要打开以运行某些宏的 4 个不同文件的文件路径。该代码包括检查他们输入的文件路径是否有效（效果很好）。但是，我想要做的是如果有什么不起作用，就会出现一个消息框，然后告诉用户哪个不起作用。

我的代码确实做到了这一点（尽管我认为这是一种非常复杂的方式）但是由于数组设置为具有 4 个值，这意味着如果最终文件不存在，它将在消息框中开始文本 4 行而不是在顶部。

我相信，我想要做的是ReDim仅包含丢失文件数量的数组，以便MsgBox第一句下方不是 3 个空行。我有点想通了，但我就是无法让它正常工作，现在我很难过。

Sub Open_month_0()

On Error GoTo ErrHand

ThisWorkbook.ActiveSheet.Calculate

    Dim i As String
    Dim j As String
    Dim k As String
    Dim l As String
    Dim m As String
    Dim n As String
    Dim o As String
    Dim p As String
    Dim arr(4) As Variant
    Dim File_Missing As Integer

    'Used as a counter to prompt either an error or successful result
    File_Missing = 0

        i = Range("LUX_Full_file_path")
        j = Range("LUX_Full_file_name")

        k = Range("JUP_Full_file_path_M")
        l = Range("JUP_Full_file_name_M")

        m = Range("JUP_Full_file_path_Q")
        n = Range("JUP_Full_file_name_Q")

        o = Range("JUP_Full_file_path_A")
        p = Range("JUP_Full_file_name_A")

        'The if not's check to see if the file path is valid. If it isn't, gets added to array and File_missing begins
        If Not Dir(i, vbDirectory) = vbNullString Then
            Workbooks.Open (i)
            Windows(j).Visible = False
        Else
            arr(1) = "Lux file"
            File_Missing = File_Missing + 1
        End If

        If Not Dir(k, vbDirectory) = vbNullString Then
            Workbooks.Open (k)
            Windows(l).Visible = False
        Else
            arr(2) = "Monthly file"
            File_Missing = File_Missing + 1
        End If

        If Not Dir(m, vbDirectory) = vbNullString Then
            Workbooks.Open (m)
            Windows(n).Visible = False
        Else
            arr(3) = "Quarterly file"
            File_Missing = File_Missing + 1
        End If

        If Not Dir(o, vbDirectory) = vbNullString Then
            Workbooks.Open (o)
            Windows(p).Visible = False
        Else
            arr(4) = "Annual file"
            File_Missing = File_Missing + 1
        End If

        'Basic error handling procedure that retains function.
        If File_Missing > 0 Then
            MsgBox ("The following files could not be found. Please check the file paths and try again" & vbCrLf & Join(arr, vbCrLf))
        Else
            MsgBox "Files opened successfully."
        End If

Exit Sub

ErrHand: MsgBox "There has been a critical error with opening the chosen workbooks. If the problem persists, please contact your administrator for assistance."

End Sub

用图片编辑：

消息框当前输出截图
消息框当前输出截图

我希望消息框的外观
我希望消息框的外观

标签： excelvba

解决方案

由于您Join稍后只使用该数组，您也可以只使用String变量MyMissingFiles而不是该数组并附加文件名。

File_Missing如果您不感兴趣，您甚至不需要计算其中的文件。

Dim MyMissingFiles As String

If Not Dir(i, vbDirectory) = vbNullString Then
    Workbooks.Open (i)
    Windows(j).Visible = False
Else
    MyMissingFiles = MyMissingFiles & vbCrLf & "Lux file"
End If

' … all the others accordingly here …

If MyMissingFiles <> vbNullString Then
    MsgBox ("The following files could not be found. Please check the file paths and try again" & MyMissingFiles)
Else
    MsgBox "Files opened successfully."
End If

推荐阅读