长文本的分割功能失败，并显示VALUE！错误

Question

我使用，我在这里看到了＃2的功能：

Function EXTRACTELEMENT(Txt, n, Separator) As String 
EXTRACTELEMENT = Split(Application.Trim(Txt), Separator)(n - 1) 
End Function 

有人劈裂数据数组，像这样：

SRN LMDscandata SRA LMDscandata 1 F97BBF 0 0 6D2A 6D2D 71F5A0FA 71F5FD85 0 0 7 0 0 1388 168 0 1 DIST1 3F800000 00000000 D9490 1388 5 6E2 6DC 6E3 6E1 6ED 0 0 0 0 0 0

但是，当我试图增加的数据量：

SRN LMDscandata SRA LMDscandata 1 1 F97BBF 0 0 FCDF FCE2 9DC90606 9DC9637B 0 0 7 0 0 1388 168 0 1 DIST1 3F800000 00000000 C3500 1388 3D 525 50B 518 508 51D 50A 51A 502 514 50F 502 51C 50E 51C 50E 4FF 509 505 50B 4F9 505 51B 513 516 501 50F 509 4FE 505 508 50C 507 50C 50E 51A 511 514 528 511 519 524 52E 526 522 524 535 534 52E 527 52F 52E 53D 52F 550 535 547 548 559 551 557 558 0 0 0 0 0 0

发生错误，VBA返回错误窗口，并且没有数据被分割。我该如何解决它？

这是完整的代码，我编写它来测试传感器输出，在那里我收到一些重要的数据在十六进制& ascii，然后转换为十进制并制作一些图。这是转换值的函数。 如果有人还可以给分的一些技巧，我将不胜感激。

Sub ler() 

Dim ncell 
Dim vArr, col 
Dim counter, elem_end As Integer 
Dim rng1, rng2 As String 

Set ascii = ThisWorkbook.Worksheets("ASCII") 
Set medidas = ThisWorkbook.Worksheets("Medidas") 
'Valor da última linha preenchida da coluna A' 
    ncell = ascii.Range("A65536").End(xlUp).Row 
    'Número de elementos' 
    elem_end = ascii.Range("B" & ncell).Value 

    For counter = 1 To elem_end 

    counterplus = counter + 2 
    vArr = Split(Cells(1, counterplus).Address(True, False), "$") 
Col_Letter = vArr(0) 
col = Col_Letter 
Let rng1 = col & ncell 
Let rng2 = "A" & ncell 
    ascii.Range(rng1).NumberFormat = "@" 
    ele = EXTRACTELEMENT(ascii.Range(rng2), counter, " ") 
    ascii.Range(rng1).FormulaR1C1 = ele 


    Next 


    With ascii.Range(Cells(ncell, 1), Cells(ncell, counterplus)) 
Set dist = .Find("DIST1", LookIn:=xlValues) 
    If Not dist Is Nothing Then 
     firstAddress = dist.Address 
     Do 
      dist1 = firstAddress 
      Set dist = .FindNext(dist) 
     Loop While Not dist Is Nothing And dist.Address <> firstAddress 
    End If 
End With 


    data_col = ascii.Range(dist1).Column + 5 
    data_num = ascii.Cells(ncell, data_col).Value 
    dec_num = CLng("&H" & data_num) 
    medidas.Range("A" & ncell).Value = dec_num 

    For counter2 = 1 To data_num 
    asc_value = ascii.Cells(ncell, data_col + counter2).Value 
    Dec = CLng("&H" & asc_value) 
    medidas.Cells(ncell, counter2 + 1).Value = Dec 

    Next 


End Sub 

B列中，有一个计算列A

Answer 1

您需要声明的变量，你得到一个类型不匹配，至少首先需要的数据元素的数的函数被宣布。

Function EXTRACTELEMENT(Txt As String, n As Long, Separator As String) As String 
EXTRACTELEMENT = Split(Application.Trim(Txt), Separator)(n - 1) 
End Function