Excel VBA 中不必要的值串联和类型错误

Question

我的VBA代码从特定的软件输出(卡尔森调查软件(中获取一个.txt文件并进行一些计算，然后将其转换为.CSV 文件。 我在计算组件方面特别遇到问题，其中我的一列文本文件(使用逗号分隔符引入 excel(没有执行我告诉它的计算，并且似乎正在连接自己(删除小数点后的所有内容(。 我的假设是，因为我将这些值放入设置为字符串的数组(必须设置为字符串，否则我会收到类型错误(，这会导致小数点后的串联。 我不知道为什么计算似乎没有运行，因为程序似乎执行得很好。

以及用于快速参考的VBA脚本(有问题的特定部分是"进行数据转换"部分：

Private Sub Workbook_Open()
Sheets("Sheet1").Cells.ClearContents
'---------------------------------------------------------------------------------------
'Choose and open the .TXT file for conversion
Dim answer As Integer
answer = MsgBox("Do you want to process a .TXT file for use in InfoSWMM?", vbYesNo + vbQuestion, "Select .TXT File")
If answer = vbNo Then
Exit Sub
End If
Dim Ret
Ret = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If Ret <> False Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Ret, Destination:=Range("$A$1") _
)
.Name = "Sample"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End If
'---------------------------------------------------------------------------------------
'Do data conversion, SECTION NEEDS UPDATING LACKING FEATURES, BUGS
Dim row As Integer
Dim col As Integer
Dim i As Integer
Dim tester(3) As String 'Bug[1] related, type error (see below).  String type fixes type error, but causes undesired concatenation
Dim col_test As Integer
Dim rim As Integer
For row = 1 To ActiveSheet.UsedRange.Rows.Count
If IsEmpty(ActiveSheet.Cells(row, 1).Value) = True Then
Exit For
End If
'Change these values in case feature code library is changed in Carlson, also need to add extra fields
If ActiveSheet.Cells(row, 5).Value = "SD" Or ActiveSheet.Cells(row, 5).Value = "WQ" Then
col_test = 20
rim = ActiveSheet.Cells(row, 4).Value
For i = 0 To 3
tester(i) = ActiveSheet.Cells(row, col_test).Value 'Bug[1] here, type error if not a String.
col_test = col_test + 4
Next i
ActiveSheet.Cells(row, 37).Value = rim - Application.Max(tester) 'Bug[2] here, not performing calculation.
End If
Next row
'---------------------------------------------------------------------------------------
'Save converted file as .CSV
MsgBox "Choose the desired save location for the .CSV file."
Dim InitialName As String
Dim PathName As Variant
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
InitialName = "sfm_output"
PathName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="CSV (Comma delimited) (*.csv), *.csv")
ws.Copy
ActiveWorkbook.SaveAs Filename:=PathName, _
FileFormat:=xlCSV, CreateBackup:=False
MsgBox "Process completed successfully." & vbNewLine & "File saved to:" & vbNewLine & PathName
'---------------------------------------------------------------------------------------
'Close all Workbooks
Application.DisplayAlerts = False
Application.Quit
End Sub

任何帮助将不胜感激。 谢谢。

Answer 1

你试过CSTRING或CINT函数吗？

例如： tester(i( = CString(ActiveSheet.Cells(row， col_test(.值(