单步执行会产生不同的结果，然后再运行 [英] Stepping through yields different result then just running
问题描述
我有一段代码用于拆分单元格中的文本.数据是由不使用任何有用定界符的调查程序输出的，因此不幸的是，将文本转换为列对我没有任何帮助.
I have a piece of code that is used to split up the text in a cell. The data is outputted by a survey program that doesn't make use of any useful delimiter, so unfortunately converting text to columns isn't of any help to me.
我写了这段代码，但事实证明，在两种情况下结果是不同的.
I wrote this piece of code, but it turns out that the outcomes are different in two cases.
-
我逐步运行代码，直到添加第一列，然后完成它
I run the code step by step until the first column is added, and then let it finish
我从execute宏的菜单中运行代码
I run the code from the execute macro's menu
在第一种情况下，输出与我设计的一样.在标题为Crop: XXX的列之后(其中包含需要拆分的原始数据)，每个单独的条目都有从1到X编号的列.每行的数据开始在X列中拆分，然后移动到有条目.像这样:
In the first case the output is as I designed it to be. After a column with the header Crop: XXX (Which contains the raw data that needs to be split) there are columns for each separate entry numbered 1 to X. Data from every row starts to be split in column X and then moves as far as there are entries. Like this:
| Crop XXX | 1 | 2 | 3 | 4 |
|-------------|----|----|----|----|
| X1,X2,X3 | X1 | X2 | X3 | |
| X1,X2,X3,X4 | X1 | X2 | X3 | X4 |
在第二种情况下，所有列均编号为1，并且每个新行都在前一行的数据之前输入其数据.像这样:
In the second case all columns are numbered 1, and every new row enters its data before the data of the previous row. Like such:
| Crop XXX | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
|-------------|----|----|----|----|----|----|----|
| X1,X2,X3 | | | | | X1 | X2 | X3 |
| X1,X2,X3,X4 | X1 | X2 | X3 | X4 | | | |
我用来输入和编号这些列的代码是这样的:
The code I use to input and number these columns is this:
If Not UBound(inarray) = 0 Then
For i = UBound(inarray) To LBound(inarray) Step -1
If ws.Cells(1, col + i).Value = i Then
If i = UBound(inarray) Then
Exit For
End If
col_to_add = col + i + 1
Exit For
Else
addcol = addcol + 1
End If
col_to_add = col + i
Next i
If Not i = UBound(inarray) Then
col1 = ConvertToLetter(col_to_add)
col2 = ConvertToLetter(col_to_add + addcol - 1)
ws.Columns(col1 & ":" & col2).Insert shift:=xlToRight
End If
If Not addcol = 0 Then
For j = 1 To addcol
If col_to_add = col + j Then
If j = 1 Then
ws.Cells(1, col_to_add) = 1
Else
ws.Cells(1, col_to_add + j - 1) = Cells(1, col_to_add + j - 2).Value + 1
End If
Else
ws.Cells(1, col_to_add + j - 1) = Cells(1, col_to_add + j - 2).Value + 1
End If
Next j
End If
For k = UBound(inarray) To LBound(inarray) Step -1
ws.Cells(row, col + k) = inarray(k)
Next k
End If
在此示例中，Inarray()是一个一维数组，其中第一行包含以下值:
In this example Inarray() is a 1d array containing the below values for the first row:
| Inarray() | Value |
|-----------|-------|
| 1 | X1 |
| 2 | X2 |
| 3 | X3 |
ConvertToLetter是以下功能:
Function ConvertToLetter(iCol As Integer) As String
Dim vArr
vArr = Split(Cells(1, iCol).Address(True, False), "$")
ConvertToLetter = vArr(0)
End Function
有谁能指出为什么情况1和情况2之间会出现这种差异?通常，这些情况是在对象没有完全分类的情况下发生的，但是我认为这次我解决了这个问题.
Could anyone point out why this difference occurs between scenario 1 and 2? Usually these things happen when objects aren't fully classified, but I thought I tackled that problem this time.
推荐答案
区别在于，因为Cells和Range并不完全合格.因此，当您逐步进行操作时，您还将选择正确的工作表，而您却自动选择了正确的工作表.
The difference is because the Cells and the Range are not fully qualified. Thus, when you go step-by-step you are also selecting the correct worksheet and automatically you are not.
只要您有这样的事情:
ws.Cells(1, col_to_add + j - 1) = Cells(1, col_to_add + j - 2).Value + 1
请确保始终像在此处那样将Worksheet写在Cells()之前-ws.Cells.或在范围之前-ws.Range().否则，将使用ActiveSheet或代码所在的工作表(如果不在模块中).
make sure that you always write the Worksheet before the Cells() like here - ws.Cells. Or before the range - ws.Range(). Otherwise it takes the ActiveSheet or the sheet where the code is (if not in a module).
这篇关于单步执行会产生不同的结果，然后再运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
