convert the Triangle average method of VBA code to LabVIEW

[ronke]

Dose anyone know how to convert the Triangle average method of VBA code to LabVIEW? thanks!

Sub triaverage()

startline = 3
endline = 1253 '1253
datacolumn = 3
pointmax = 1251
n = 17
wlresolution = 0.004

myrange = (n - 1) / 2
weightfactor = 1 / ((n - 1) / 2)

pointindex = 0

For i = startline To endline
weightsum = 0
weightedpowersum = 0
pointindex = pointindex + 1 'data point under process

For j = -myrange To 0 'left side including the very point itself
movingpoint = pointindex + j 'moving point in the range

If movingpoint > 0 And movingpoint < pointmax Then
movingweight = 1 + j * weightfactor 'moving weight for each point in the range
weightsum = weightsum + movingweight 'sum up all involved weight
weightedpower = movingweight * Worksheets("sheet1").Cells(i + j, datacolumn)
weightedpowersum = weightedpowersum + weightedpower
'Debug.Print j, movingpoint, movingweight, weightsum, i, weightedpower, weightedpowersum, averagepower
End If
Next j

For j = 1 To myrange 'right side excluding the very point itself
movingpoint = pointindex + j 'moving point in the range

If movingpoint > 0 And movingpoint < pointmax Then

movingweight = 1 - j * weightfactor 'moving weight for each point in the range
weightsum = weightsum + movingweight 'sum up all involved weight
weightedpower = movingweight * Worksheets("sheet1").Cells(i + j, datacolumn)
weightedpowersum = weightedpowersum + weightedpower

'Debug.Print j, movingpoint, movingweight, weightsum, i, weightedpower, weightedpowersum, averagepower
End If
Next j
averagepower = weightedpowersum / weightsum

Worksheets("sheet1").Cells(i, datacolumn + 2) = averagepower
'Debug.Print i, weightsum

Next i

[GerdW]

Hi ronke,

where are you having problems with?

Basically its a running average over a 1D array of values ("datacolumn")…

Show your VI (or the attempt to create one) and we will help you with your homework!

Cleaning up the VBA code may improve it's readability:

For i = startline To endline
  weightsum = 0
  weightedpowersum = 0
  pointindex = pointindex + 1
  For j = -myrange To 0
    movingpoint = pointindex + j
    If movingpoint > 0 And movingpoint < pointmax Then
      movingweight = 1 + j * weightfactor
      weightsum = weightsum + movingweight
      weightedpower = movingweight * Worksheets("sheet1").Cells(i + j, datacolumn)
      weightedpowersum = weightedpowersum + weightedpower
    End If
  Next j
  For j = 1 To myrange
    movingpoint = pointindex + j
    If movingpoint > 0 And movingpoint < pointmax Then
      movingweight = 1 - j * weightfactor
      weightsum = weightsum + movingweight
      weightedpower = movingweight * Worksheets("sheet1").Cells(i + j, datacolumn)
      weightedpowersum = weightedpowersum + weightedpower
    End If
  Next j
  averagepower = weightedpowersum / weightsum
  Worksheets("sheet1").Cells(i, datacolumn + 2) = averagepower
Next i

 A main loop with two FOR loops inside. In each inner FOR loop you will need a case structure…

(The LabVIEW version will look better when you use autoindexing and polymorphism.)

[ronke]

In fact I don't have this vi, just try to create a vi to replace the VBA to integrating this calculation method into LabVIEW, but I'm not familar with VBA, so neet some help to convert this average method to LabVIEW, so I need some help, thanks.

[GerdW]

Hi ronke,

most of that code are just variable names, the only VBA-for-Excel specific part are those two Cell calls:

Worksheets("sheet1").Cells(i + j, datacolumn)

Here a value from your Excel table is read: replace this by an IndexArray function to your 1D array data (or autoindexing).

[ronke]

I try to using the LabVIEW formula node to write this VI, but show some error, Cold you help me provide a VI example for me, many thanks. 

[ronke]

---

GerdW 已写：

Hi ronke,

most of that code are just variable names, the only VBA-for-Excel specific part are those two Cell calls:

Worksheets("sheet1").Cells(i + j, datacolumn)

Here a value from your Excel table is read: replace this by an IndexArray function to your 1D array data (or autoindexing).

---

---

GerdW 已写：

Hi ronke,

most of that code are just variable names, the only VBA-for-Excel specific part are those two Cell calls:

Worksheets("sheet1").Cells(i + j, datacolumn)

Here a value from your Excel table is read: replace this by an IndexArray function to your 1D array data (or autoindexing).

---

I try to using the LabVIEW formula node to write this VI, but show some error, Cold you help me provide a VI example for me, many thanks. 

[GerdW]

Hi ronke,

I try to using the LabVIEW formula node to write this VI, but show some error,

Why do you need a formula node?

And which errors do you get?

Cold you help me provide a VI example for me, many thanks. 

No. Why should I do your task of doing the programming?

I repeat: Attach your VI (or the attempt to create one) and we will help you to solve your homework!

[ronke]

Attached is the VI example to replace the excel calculation method

[GerdW]

Hi ronke,

and where's the part for that running average?

[ronke]

I using the TSA moving average in this VI now, see the yellow label, just plat to using Triangle average method to replace this VI.