vba - CDbl() 问题和类型不匹配

Question

我对 VBA 相当陌生，并且在我负责编写的脚本方面遇到了一些问题。它应该做的是在单个单元格中使用分号间隔的数据(数据包含括号内的名称和数字)并找到具有最大数字的名称，然后将其分隔到不同的单元格中。

为了从字符串中取出数字，我使用了 ozgrid 开发的自定义函数 ExtractNumber:http://www.ozgrid.com/VBA/ExtractNum.htm

我不断得到

error 424

在尝试调用该函数时，恰好在其最后一行:

ExtractNumber = CDbl(lNum)

就像另一个用户建议的那样，我尝试在这里切换变量的类型:

ExtractNumber(rCell As String, 

但它根本没有帮助。我还尝试将 CDbl() 命令切换到任何其他 C...() 类型的命令以及完全删除它。没运气。我觉得我正在撞到一堵巨大的砖墙，我的时间慢慢地不多了。我可以再请求一次帮助吗？这是我的完整代码:

Option Explicit

Sub Divide()

Dim txt As String
Dim i As Integer
Dim j As Integer
Dim Full As Variant
Dim a As Integer
Dim b As Integer
Dim stored() As Integer

txt = (CStr(ActiveCell.Value))
Full = Split(txt, ";")
a = UBound(Full)
b = a - 1

ReDim stored(b)

For i = 0 To a
    stored(i) = ExtractNumber((Full(i)))
Next i

Dim primary_index As Integer
Dim primary_no As Integer
Dim primary_name As String
primary_index = Application.Match(Application.Max(stored), stored, 0)
primary_no = stored(primary_index)
primary_name = Full(primary_index)
stored(primary_index) = 0

If UBound(stored) > 1 Then
    Dim secondary_index As Integer
    Dim secondary_no As Integer
    Dim secondary_name As String
    secondary_index = Application.Match(Application.Max(stored), stored, 0)
    secondary_no = stored(secondary_index)
    secondary_name = Full(secondary_index)
End If

For i = 0 To 6
    ActiveCell.EntireColumn.Offset(0, 1).Insert
Next i

If UBound(stored) > 2 Then
    Dim names() As String
    ReDim names(0 To a)
    For j = 0 To a
        If Not (j = primary_index Or j = secondary_index) Then
            names(j) = Full(j)
        End If
    Next j

    ActiveCell.Offset(0, 1).Value = primary_name
    ActiveCell.Offset(0, 2).Value = primary_no
    ActiveCell.Offset(0, 3).Value = secondary_name
    ActiveCell.Offset(0, 4).Value = secondary_no
    ActiveCell.Offset(0, 5).Value = names
    ActiveCell.Offset(0, 6).Value = (ActiveCell.Offset(0, 8).Value - primary_no             - secondary_no)

ElseIf UBound(stored) = 2 Then
    ActiveCell.Offset(0, 1).Value = primary_name
    ActiveCell.Offset(0, 2).Value = primary_no
    ActiveCell.Offset(0, 3).Value = secondary_name
    ActiveCell.Offset(0, 4).Value = secondary_no
End

Else
    ActiveCell.Offset(0, 1).Value = primary_name
    ActiveCell.Offset(0, 2).Value = primary_no
End

End If
End Sub

这是我的数据示例: click

单元格内容示例: A&W All American Food (1) ; American Pie Cafe (1) ; Arby's (53) ; Auntie Anne's (13) ; Auntie Anne's Hand-Rolled Soft Pretzels (1) ; Baskin Robbins (1) ; Beef-A-Roo (1) ; Big Steer Restaurant ; Bill Ellis BBQ (1) ; Breakfast/Soup Bar (116) ; Broadway Diner (4) ; Burger King (4) ; Chester's Chicken (2) ; Cinnabon (126) ; Country Market (1) ; Country Skillet (1) ; Cuban Cuisine Restaurant (1) ; Dairy Queen (23) ; Dan's Big Slice Pizza (1) ; Day Breaker's Cafe (1) ; Deli (17) ; Denny's (97) ; Dunkin' Donuts (6) ; Family Restaurant (1) ; Full Service (4) ; Golden Corral (2) ; Gooseberry Farms (1) ; GrandMa Max's (3) ; Hardee's (1) ; Hot Food and Pizza (44) ; Hot Stuff Pizza (3) ; Huddle House (1) ; IHOP Restaurant (1) ; J's Wok and Grill (1) ; Johnny Pastrami (1) ; Junie's Restaurant (1) ; KFC (3) ; Krispy Krunchy Chicken (1) ; Long John Silver's (1) ; Max's Highway Diner (1) ; McDonald's (39) ; Mexican Grill (1) ; Milestone Diner (3) ; Moe's Southwest Grill (6) ; Nathan's Famous (1) ; Noble Roman's Pizza (1) ; Penn 80 Grill (1) ; Pizza Hut (4) ; Pizza Shop (1) ; Q Eats (1) ; Quiznos (2) ; Sam Bass Steakhouse (1) ; Sbarro (1) ; Silver Skillet (1) ; Subway (231) ; Sunshine Cafe (1) ; Taco Bell (8) ; Taco Bell/KFC (1) ; Taco John's (1) ; Wendy's Old Fashioned Hamburgers (72)

Answer 1

我想问题出在您用于浮点数的小数分隔符中。
你可以这样试试:

ExtractNumber(change_commas(Full(i)))

Public Function change_commas(ByVal myValue As Variant) As String
   Dim str_temp as String
   str_temp = CStr(myValue)
   change_commas = Replace(str_temp, ",", ".")
End Function