Databases
StackOverflow2013
Postsdbo
POSkip x lines of code depending on a variable in VBA
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSkip x lines of code depending on a variable in VBA
    primarykey
    data
    text
    <p>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.</p> <p>The important parts are:</p> <pre><code>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 </code></pre> <p>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?</p> <p>Is there some sort of "skip" command which causes lines to be ignored?</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCassiopeia
    1. USCassiopeia
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThe code is a bit difficult to follow and it's not reproducible so I may have the wrong end of the stick but my suggestion would be to make your loop stop iterating at the number of channels so changing the line `For i = 0 To samplenum - 1` to `For i = 0 To Min(samplenum - 1, numChannels)` which will return the lowest of the samplenum or numChannels values.
      singulars
      1. POSkip x lines of code depending on a variable in VBA
      1. USSteph Locke
    2. COThis gives a compile error saying "Sub or function not defined" and highlights "min". Could I apply this same code to the channel number designation too?
      singulars
      1. POSkip x lines of code depending on a variable in VBA
      1. USCassiopeia
    3. COApologies, should be `WorksheetFunction.min() ` and yes you can.
      singulars
      1. POSkip x lines of code depending on a variable in VBA
      1. USSteph Locke
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload