最初“回答”后剩余的问题

考虑字段1：

• 如果值为-123，则保存为“-123”还是“123-”？
• 输出格式为2字节，对于“s9（3）comp-3”是正确的。但没有空间“在每个挤满的领域前面留一个空间”。

“固定NL ASCII文本文件”是否意味着您有78字节的行，由换行符分隔？
你说你“有一个宏可以分解excel文件”。你想更新宏还是想读取文本文件并输出Cobol文件的宏？
请通过更新您的问题来回答我的问题

可能答案

我假设你在工作中发布了你的问题，直到周一你才能回答我的问题。下周我将有有限的互联网接入，所以我张贴什么，我希望是一个令人满意的答案。我会回答你的问题，当我可以。
我对你的问题的解释是，你有一个宏，它构建了一个358行的文件，每行78字节，以换行符结束。每行包含11个字段，其中前六个是数字。您使用转换服务来创建一个文件，除了每行的六个数字字段从ASCII字符串转换为Cobol的comp 3格式之外，该文件是不变的。
在你的宏中，你将有如下语句：

RowCrnt = RowCrnt & Range.Value

其中RowCrnt是一个字符串，其中的行正在构建中，准备输出，Range.Value是从工作表中获取的ASCII数字字符串。
我写了一个函数，这样你就可以用下面的语句替换上面的语句：

RowCrnt = RowCrnt & AsciiToComp3(P1, P2, P3, Range.Value)

稍后我将解释P1、P2和P3。通过此更改，由宏创建的文件应采用Cobol格式。
我已经直观地检查了我的函数是否创建了我期望的输出。但是，我没有独立的测试方法。也许我对comp 3格式的理解是错误的。也许有一个微妙的错误，很难发现视觉。也许有更多的转换比我欣赏。
假设现有的宏为不同的数据集创建了文件A1、A2等。您的供应商已将文件A1、A2和A3转换为文件B1、B2和B3。您需要检查由更新的宏创建的文件是否匹配B1、B2和B3。
如果我的函数发现它不喜欢的东西，它会使用Debug.Assert False来停止执行。这在开发过程中非常方便，但在生产宏中会吓到用户。这些Debug.Assert False语句都不应该被访问，因为它们表明函数的使用或要处理的数据中存在错误。这两种情况都不应该发生，因此您可以决定保持函数不变。只有你知道什么是合适的。
参数P1、P2和P3指定要转换的ASCII值的格式。由该函数创建的comp 3格式是保存此类ASCII值的最小格式。这对您的数据应该是令人满意的。如果希望comp 3值更大，则指定ASCII数据的最大大小大于实际最大值。
P1标识符号在ASCII数据中的位置：

• -1表示前导符号。例如：-123或+123或123。
• 0表示无标志
• 1表示尾随符号。例如：123-或123+或123。

Comp 3格式始终为尾随符号保留半个字节，即使不使用它。
P2是整数位的最大数目。因此，如果最大值为999，则P2=3;如果最大值为99，999，则P2=5。
P3是十进制数位的最大数目。如果ASCII值为整数，则设置P3 =0。在ASCII值中使用本地十进制符号（句点或逗号）。
祝你好运

Option Explicit
Function AsciiToComp3(ByVal SignFmt As Long, ByVal NumIntegerDigitsFmt, _
                      ByVal NumDecimalDigitsFmt As Long, _
                      ByVal AsciiValue As String) As String

  ' * Returns a string which is a Comp-3 version of AsciiValue.

  ' * SignFmt             -1 if AsciiValue has an optional leading sign.  For
  '                          example 12.34 or -12.34
  '                        0 if AsciiValue has no sign.  For example 12.34
  '                        1 if AsciiValue has an optional trailinging sign.
  '                          For example 12.34 or 12.34-
  ' * NumIntegerDigitsFmt  If AsciiValue may not contain a decimal separator,
  '                        the maximum number of digits.
  '                        If AsciiValue may contain a decimal separator, the
  '                        maximum number of digits to the left of the decimal
  '                        separator. The decimal separator is
  '                        Application.DecimalSeparator which returns "." or'
  '                        "," according to local conventions.
  ' * NumDecimalDigitsFmt  If AsciiValue may not contain a decimal separator, 0.
  '                        If AsciiValue may contain a decimal separator, the
  '                        maximum number of digits to the right of the decimal
  '                        separator.
  ' * AsciiValue           The value to be converted to comp-3 format. This
  '                        string must confirm to the format defined by the
  '                        first three parameters. For example: it can only
  '                        contain a sign if SignFmt indicates it can; it can
  '                        only contain a decimal separator if
  '                        NumDecimalDigitsFmt > 0 and the number of digits
  '                        cannot exceed the numbers indicated by
  '                        NumIntegerDigitsFmt and NumDecimalDigitsFmt.

  ' The return string will be sized to hold the largest Ascii value. Increase
  ' NumIntegerDigits and NumDecimalDigits above the true maximum size of
  ' AsciiValue if a larger return string is required. If the return value is to
  ' be signed but AsciiValue is not signed then set Sign to -1 or 1 as though
  ' it could be signed.

  ' The number of bytes in the return string will be:
  '    (NumIntegerDigits + NumDecimalDigits + 1) / 2 rounded up.
  ' The "+ 1" is for the sign.  Space is always allocated for a sign even if
  ' the value is unsigned.
  ' If the expression has to be rounded up, there will be space for one more
  ' integer digit than requested.

  Dim Digit As String
  Dim NumericValue As String
  Dim PosDest As Long
  Dim Pos As Long
  Dim PosDecimal As Long
  Dim Positive As Boolean
  Dim PosSrc As Long
  Dim ReturnValue As String

  ' Enlarge NumIntegerDigits if necessary
  If (NumIntegerDigitsFmt + NumDecimalDigitsFmt + 1) Mod 2 = 1 Then
    ' Make output string a whole number of bytes
    NumIntegerDigitsFmt = NumIntegerDigitsFmt + 1
  End If

  ' Decode AsciiValue

  ' If sign present, record value and remove from AsciiValue
  Select Case SignFmt
    Case -1
      Select Case Left(AsciiValue, 1)
        Case "-"
          Positive = False
          AsciiValue = Mid(AsciiValue, 2)  ' Remove sign
        Case "+"
          Positive = True
          AsciiValue = Mid(AsciiValue, 2)  ' Remove sign
        Case Else
          Positive = True
      End Select
    Case 0
      Positive = True
    Case 1
      Select Case Right(AsciiValue, 1)
        Case "-"
          Positive = False
          AsciiValue = Mid(AsciiValue, 1, Len(AsciiValue) - 1) ' Remove sign
        Case "+"
          Positive = True
          AsciiValue = Mid(AsciiValue, 1, Len(AsciiValue) - 1) ' Remove sign
        Case Else
          Positive = True
      End Select
    Case Else
      Debug.Assert False    ' Call error. SignFmt not -1, 0 or 1.
  End Select

  PosDecimal = InStr(1, AsciiValue, Application.DecimalSeparator)

  If PosDecimal > 0 Then
    If NumDecimalDigitsFmt = 0 Then
      ' AsciiValue contains decimal separator but specification says it
      ' should not
      Debug.Assert False
      AsciiToComp3 = "Error"
      Exit Function
    End If
    If PosDecimal > NumIntegerDigitsFmt Or _
       Len(AsciiValue) - PosDecimal > NumDecimalDigitsFmt Then
      ' AsciiValue contains more integer digits or more decimal digits than
      ' allowed by specification
      Debug.Assert False
      AsciiToComp3 = "Error"
      Exit Function
    End If
    ' Pad integer part of AsciiValue with leading zeros to NumIntegerDigits,
    ' discard decimal and
    ' pad decimal part with trailing digits to NumDecimalDigits
    NumericValue = Right(String(NumIntegerDigitsFmt, "0") & Mid(AsciiValue, 1, PosDecimal - 1), NumIntegerDigitsFmt) & _
                   Left(Mid(AsciiValue, PosDecimal + 1) & String(NumDecimalDigitsFmt, "0"), NumDecimalDigitsFmt)
  Else
    ' AsciiValue is all integer
    If Len(AsciiValue) > NumIntegerDigitsFmt Then
      ' AsciiValur contains more integer digits than allowed by specification
      Debug.Assert False
      AsciiToComp3 = "Error"
      Exit Function
    End If
    ' Pad AsciiValue with leading zeros to maximum length
    NumericValue = Right(String(NumIntegerDigitsFmt, "0") & AsciiValue, NumIntegerDigitsFmt)
    If NumDecimalDigitsFmt > 0 Then
      NumericValue = NumericValue & String(NumDecimalDigitsFmt, "0")
    End If
  End If

  ' Create Return value of required length but full of zeros.
  ReturnValue = String((NumIntegerDigitsFmt + NumDecimalDigitsFmt + 1) \ 2, Chr$(0))

  ' Pack Ascii string "ABCEFG" with each pair of ASCII digits becoming one byte
  ' of output string.  Ascii string has odd number of digits with last half byte
  ' reserved for sign.
  ' ABCDEFG  -> ab cd ef g
  PosSrc = 1
  PosDest = 1
  Do While True
    ' Handle digits that are stored in lefthand half of bytes
    Digit = Mid(NumericValue, PosSrc, 1)
    If Digit < "0" Or Digit > "9" Then
     ' Not a decimal digit
      Debug.Assert False
      AsciiToComp3 = "Error"
      Exit Function
    End If
    Mid(ReturnValue, PosDest, 1) = Chr(Val(Digit) * 16)

    PosSrc = PosSrc + 1
    If PosSrc > Len(NumericValue) Then
      ' All digits handled
      Exit Do
    End If

    ' Handle digits that are stored in righthand half of bytes
    Digit = Mid(NumericValue, PosSrc, 1)
    If Digit < "0" Or Digit > "9" Then
     ' Not a decimal digit
      Debug.Assert False
      AsciiToComp3 = "Error"
      Exit Function
    End If
    Mid(ReturnValue, PosDest, 1) = Chr$(Asc(Mid(ReturnValue, PosDest, 1)) Or Val(Digit))

    PosSrc = PosSrc + 1
    PosDest = PosDest + 1

  Loop

  If SignFmt = 0 Then
    ' Comp3 string is unsigned.  Half byte for sign already zero so no action required.
  Else
    If Positive Then
      Mid(ReturnValue, PosDest, 1) = Chr$(Asc(Mid(ReturnValue, PosDest, 1)) Or &HC)
    Else
      Mid(ReturnValue, PosDest, 1) = Chr$(Asc(Mid(ReturnValue, PosDest, 1)) Or &HD)
    End If
  End If

  AsciiToComp3 = ReturnValue

End Function