macos - 拆分mac excel vba上的无效过程调用或参数

Question

我不知道为什么，但是这个 cet = Split(strCSV, " - ")导致运行时错误 5:无效的过程调用或参数。

   strCSV = Trim(cWk.Range("P" & i)): dt = CDate(CLng(cWk.Range("H" & i)))

where str = "Cap Style:Snapback - CD / Number:07 / Color:First Avenger(+S$1.50) / Box:none - Only Purchase 3 caps and above - Free Box "

以下代码在 Windows 上完美运行，但在上述行上引发错误

Option Explicit
Option Compare Text
Sub Get_Data()
Application.ScreenUpdating = False

Dim fName, wkB2 As Workbook, cWk As Worksheet, xWk As Worksheet, frowC As Long, i As Long, j As Long, ch As String, num As String
Dim strCSV As String, dt As Date, shtName As String, cet, temp As String, rng As Range, cel As Range, cl As String, rw As Long, toF As String

On Error GoTo Err
fName = Application.GetOpenFilename
If fName <> False Then
Set wkB2 = Workbooks.Open(fName): Set cWk = wkB2.Worksheets(1): frowC = cWk.Range("P" & Rows.Count).End(xlUp).Row

'Cap Style:Baseball - CC / Number:04 / Color:Grey(+S$2) / Box:none - Only Purchase 3 caps and above - Free Box
'Cap Style:SnapBack - CC / Number:04 / Color:Grey(+S$1.50) / Box:none - Only Purchase 3 caps and above - Free Box

For i = 2 To frowC
    strCSV = Trim(cWk.Range("P" & i)): dt = CDate(CLng(cWk.Range("H" & i)))
    If strCSV <> "" And IsDate(dt) Then
     'ERROR  cet = Split(strCSV, " - "): temp = cet(LBound(cet)): cet = Split(temp, ":"): shtName = Trim(cet(UBound(cet)))
        For Each xWk In ThisWorkbook.Worksheets

        If shtName = Trim(xWk.Name) Then

        Set rng = xWk.Range("E3:BD3")
            For Each cel In rng
                If cel.Value = dt Then
                    cet = Split(cel.Address, "$"): cl = cet(UBound(cet) - 1): Exit For
                End If
            Next cel

        cet = Split(strCSV, "Number:"): temp = cet(UBound(cet)): cet = Split(temp, "/"): num = Trim(cet(LBound(cet)))
        cet = Split(strCSV, " / "): temp = cet(LBound(cet)): cet = Split(temp, " - "): ch = Trim(cet(UBound(cet))): ch = ch & "-" & num
        Debug.Print "Ch is " & ch
        Set rng = xWk.Range("A1:A" & xWk.Range("A" & Rows.Count).End(xlUp).Row)
            For Each cel In rng
                If cel.Value = ch Then
                    rw = cel.Row: Exit For
                End If
            Next cel

        cet = Split(strCSV, "Color:"): temp = cet(UBound(cet)): cet = Split(temp, "("): toF = Trim(cet(LBound(cet)))
            For j = rw To rw - 10 Step -1
                If Trim(xWk.Range("B" & j)) = toF Then
                    rw = j: Exit For
                End If
            Next j

        Debug.Print "Address is: " & cl & rw & " for row  " & i
        xWk.Range(cl & rw) = cWk.Range("O" & i)

            Exit For
            End If
        Next xWk

    End If
Next i

wkB2.Close False
Else
    Exit Sub
End If

Application.ScreenUpdating = True
MsgBox "Done"
Exit Sub

Err:
MsgBox Err.Description
End Sub

Answer 1

更新:SplitString 现在处理多字符分隔符。

我们得出结论，旧版本的 Mac Office 使用 VB5 的等效版本。由于Split函数是在VB6中引入的。一个 Invalid procedure call or argument因为 VB5 中没有 Split 功能，所以被抛出。

解决方法是创建一个像 Spli 一样工作的自定义函数。

拆分替换功能

Function SplitString(Text As String, Delimiter As String)
    Dim arr() As String, s As String
    Dim i As Long, iEnd As Long, iStart As Long, length As Long

    length = Len(Delimiter)

    ReDim Preserve arr(0)

    iStart = 1
    Do
        iEnd = InStr(Mid(Text, iStart), Delimiter) - 1

        If iEnd = -1 Then
            ReDim Preserve arr(i)
            arr(i) = Mid(Text, iStart)
            Exit Do
        Else
            ReDim Preserve arr(i)
            arr(i) = Mid(Text, iStart, iEnd)
            iStart = iStart + iEnd + length
            i = i + 1
        End If
    Loop Until iStart = 0

    SplitString = arr
End Function

这是我运行的测试

Sub BatchTest()
    Dim strCSV As String, Temp As String, Delimiter As String
    Dim a
    strCSV = "Cap Style Snapback - CD / Number 07 / Color First Avenger(+S$1.50) / Box none - Only Purchase 3 caps and above - Free Box"
    a = SplitString(strCSV, "/")

    TestSplit strCSV, " / "
    TestSplit strCSV, " /"
    TestSplit strCSV, "/"
    TestSplit strCSV, " Color First"

End Sub

Sub TestSplit(Text As String, Delimiter As String)
    Dim arr As Variant, sReplcement As String

    arr = SplitString(Text, Delimiter)
    sReplcement = Replace(Text, Delimiter, "|")
    Debug.Print sReplcement
    Debug.Print Join(arr, "|")
    Debug.Print sReplcement = Join(arr, "|")

End Sub

测试结果

Sub TestRegEx()
    MsgBox RegexExtract("sdi 99090 dfddf sdi 5666", "(sdi \d+)", ", ") = "sdi 99090, sdi 5666"

End Sub

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional seperator As String = "") As String

Dim i As Long, j As Long
Dim result As String
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & seperator & allMatches.Item(i).submatches.Item(j)
    Next
Next

If Len(result) <> 0 Then
    result = Right(result, Len(result) - Len(seperator))
End If

RegexExtract = result

End Function