根据 VBA 中的变量跳过 x 行代码

Skip x lines of code depending on a variable in VBA

提问by Cassiopeia

I have a code which takes data from a PicoLog 1012 and records it into an excel spreadsheet. It is working well but currently it always records 12 channels of data. This will make it slow if a lot of data is required so I would like to allow users to enter a value in a cell to define the number of channels and then skip running unnecessary code based on this.

我有一个代码，它从 PicoLog 1012 中获取数据并将其记录到 Excel 电子表格中。它运行良好，但目前它总是记录 12 个数据通道。如果需要大量数据，这会使其变慢，因此我希望允许用户在单元格中输入一个值来定义通道数，然后基于此跳过运行不必要的代码。

The important parts are:

重要的部分是：

Dim values() As Integer 'number of datapoints in the array. Equal to channels * number of datapoints required.
Dim numChannels As Integer
numChannels = Worksheets("Sheet1").Range("W5").value 'this allows the user to set the number of channels
Dim samplenum As Long
    samplenum = Worksheets("Sheet1").Range("W3").value 'Reads number of samples desired per channel
    nValues = samplenum * numChannels 
'The code apparently requires one of these lines per channel.
channels(0) = 1
channels(1) = 2
channels(2) = 3
channels(3) = 4
channels(4) = 5
channels(5) = 6
channels(6) = 7
channels(7) = 8
channels(8) = 9
channels(9) = 10
channels(10) = 11
channels(11) = 12

ReDim values(12 * Worksheets("Sheet1").Range("W3").value) 'allow a variable data array
Dim sampleInterval As Long
Dim microsecs_for_block As Long

Dim testlength As Integer
testlength = Worksheets("Sheet1").Range("W4").value
microsecs_for_block = testlength * 1000000
status = pl1000SetInterval(handle, microsecs_for_block, nValues, channels(0), numChannels)

status = pl1000Run(handle, nValues, 0)
'If there is a more efficient way to do what follows then I would LOVE to hear it. Currently logging begins long after I activate the macro.   

ready = 0
Do While ready = 0
    status = pl1000Ready(handle, ready)
Loop
Cells(14, "P").value = "RECORDING COMPLETE" 'indicate readiness

' Get a block of W3 readings...
' we can call this routine repeatedly
' to get more blocks with the same settings
Dim triggerIndex As Long
Dim overflow As Integer
status = pl1000GetValues(handle, values(0), samplenum, overflow, triggerIndex)

' Copy the data into the spreadsheet
For i = 0 To samplenum - 1
1: Cells(i + 4, "A").value = adc_to_mv(values(numChannels * i + 0))
2: Cells(i + 4, "B").value = adc_to_mv(values(numChannels * i + 1))
3: Cells(i + 4, "C").value = adc_to_mv(values(numChannels * i + 2))
4: Cells(i + 4, "D").value = adc_to_mv(values(numChannels * i + 3))
5: Cells(i + 4, "E").value = adc_to_mv(values(numChannels * i + 4))
6: Cells(i + 4, "F").value = adc_to_mv(values(numChannels * i + 5))
7: Cells(i + 4, "G").value = adc_to_mv(values(numChannels * i + 6))
8: Cells(i + 4, "H").value = adc_to_mv(values(numChannels * i + 7))
9: Cells(i + 4, "I").value = adc_to_mv(values(numChannels * i + 8))
10: Cells(i + 4, "J").value = adc_to_mv(values(numChannels * i + 9))
11: Cells(i + 4, "K").value = adc_to_mv(values(numChannels * i + 10))
12: Cells(i + 4, "L").value = adc_to_mv(values(numChannels * i + 11))
Next i

My current idea is to write 12 different subs for this and call each one depending on the number of channels required but I am sure there must be an easier way?

我目前的想法是为此编写 12 个不同的 subs 并根据所需的频道数量调用每个 subs，但我确定一定有更简单的方法吗？

Is there some sort of "skip" command which causes lines to be ignored?

是否有某种“跳过”命令会导致行被忽略？

IF numChannels = 2
    Then skip 3,4,5,6,7,8,9,10,11,12
Else
    IF numChannels = 3
        Then skip 4,5,6,7,8,9,10,11,12
    Else
        IF'.... et cetera

采纳答案by Cassiopeia

You can see the final solution I came up with here

你可以在这里看到我提出的最终解决方案

It is not a perfect solution for logging data with picolog but IMHO it is better than the proprietary software if you don't need huge datasets or real-time visualisation.

它不是使用 picolog 记录数据的完美解决方案，但恕我直言，如果您不需要庞大的数据集或实时可视化，它比专有软件更好。

Pros:

优点：

• Instantly usable by anyone with excel experience; no need to use picolog's interface
• No need to convert data for processing into excel
• Automatic graphing using excel's interface
• Scope for easy further development (multisheet handling, application-specific processing)

• 任何有 excel 经验的人都可以立即使用；无需使用picolog的界面
• 无需将处理的数据转换成excel
• 使用excel界面自动绘图
• 易于进一步开发的范围（多页处理、特定于应用程序的处理）

Cons:

缺点：

• No realtime visualisation of data
• May struggle to handle logs with over 10,000 data points

• 没有数据的实时可视化
• 可能难以处理超过 10,000 个数据点的日志

回答by Dan

I believe the code you are looking for is GoTo. You can have those if statements and if the if statement is triggered, it will "GoTo" where ever your tag has been placed

我相信您正在寻找的代码是 GoTo。你可以有那些 if 语句，如果 if 语句被触发，它会“转到”你的标签被放置的地方

MSDN Example

MSDN 示例

Sub SkipLines()

Dim intSkipToLine as Integer

If intSkipToLine = 1 Then Goto Line1:
If intSkipToLine = 2 Then Goto Line2:
If intSkipToLine = 3 Then Goto Line3:
If intSkipToLine = 4 Then Goto Line4:

Line1:
' first line code

Line2:
' second line code

Line3:
' thrid line code

Line4:
' fourth line code

End Sub