长文本的拆分功能失败，VALUE！错误 [英] Split function for long text fails with VALUE! error

问题描述

 函数EXTRACTELEMENT（Txt，n，Separator）As String 
 EXTRACTELEMENT = Split（Application.Trim（Txt），Separator）（n-1）
结束函数
  

它分割了一组数据，如下所示：

sRN LMDscandata sRA LMDscandata 1 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 6ED 6E1 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返回错误窗口，没有数据被拆分。我如何修复？

这是完整的代码，我正在编码它来测试一个传感器输出，其中我收到一些重要的数据在hex& ascii然后转换去除和制作一些图表。这是转换值的函数。
如果有人也可以在Sub上给出一些提示，我将不胜感激。

  Sub ler（）
 
 Dim ncell 
 Dim vArr，col 
 Dim counter，elem_end As Integer 
 Dim rng1，rng2 As String 
 
设置ascii = ThisWorkbook.Worksheets ASCII）
设置medidas = ThisWorkbook.Worksheets（Medidas）
'Valor daúltimalinha preenchida da coluna A'
 ncell = ascii.Range（A65536）。End xlUp）.Row 
'Númerode elementos'
 elem_end = ascii.Range（B& ncell）.Value 
 
对于counter = 1 To elem_end 
 
 counterplus = counter + 2 
 vArr = Split（Cells（1，counterplus）.Address（True，False），$）
 Col_Letter = vArr（0）
 col = Col_Letter 
让rng1 = col& ncell 
让rng2 =A& ncell 
 ascii.Range（rng1）.NumberFormat =@
 ele = EXTRACTELEMENT（ascii.Range（rng2），counter，）
 ascii.Range（rng1）.FormulaR1C1 =使用ascii.Range（Cells（ncell，1），Cells（ncell，counterplus））
设置（ncell，counterplus）
 
 
 
 
 
 $ b dist = .Find（DIST1，LookIn：= xlValues）
如果不是dist是Nothing然后
 firstAddress = dist.Address 
 Do 
 dist1 = firstAddress 
设置dist = .FindNext（dist）
循环而不是dist是Nothing和dist.Address<> firstAddress 
如果
结束
 
 
 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 
 
对于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 
 
下一个
 
 
 End Sub 
  

$ b $在B列中，有一个函数可以计算列A中数据元素的数量。

解决方案

 您需要声明变量，您会得到类型不匹配，至少需要声明。函数EXTRACTELEMENT（Txt As String，n As Long，Separator As String）As String 
 EXTRACTELEMENT = Split（Application.Trim（Txt），Separator）（n-1）
结束函数
  

I am using a function that i saw here on Stackoverflow:

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

It was spliting an array of data, like this:

sRN LMDscandata sRA LMDscandata 1 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 6ED 6E1 0 0 0 0 0 0

But when i tried to increase the amount of data:

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

An error is occuring and the VBA returns an error window and no data is splitted. How can i Fix it?

This is the full code, i am coding it to test a sensor output, where i receive some important data in hex&ascii and then transform to dec and make some graphs. This is the function that convert the values. If someone can also give some tips on the Sub, i would appreciate.

    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

In Column B, there is a function that calculates the number of elements of the data in column A

解决方案

You need to declare the variables, you are getting a Type mismatch, at least the first needs to be declared.

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

这篇关于长文本的拆分功能失败，VALUE！错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！