PDA

View Full Version : VBA Script to Average Amplitude Values in Each Bin Range of a Frequency Histogram



jnmonroe
03-03-2014, 05:14 AM
When running the VBA script below, the script throws a Run-time error and stops as soon as it hits a sound frequency (Freq.) bin which is empty. The script should output an average amplitude (Ave.Amp.) of zero if the bin does not contain any frequency values.

Can anyone suggest something which might allow the script to run without the run-time error and place a zero in the Ave.Amp. column if there are no frequency values within that particular bin range?

Thank you.

Sub Histogram()
Dim c As Integer, i As Integer, j As Integer
Dim avg As Double
Dim binlow As Integer, binhigh As Integer
Dim N As Integer

For c = 1 To 16
binlow = Cells(c + 2, 2)
binhigh = Cells(c + 3, 2)
avg = 0
N = 0
For i = 1 To 11
If Cells(i + 2, 6) >= binlow And Cells(i + 2, 6) < binhigh Then
N = N + 1
avg = avg + Cells(i + 2, 7)
End If
Next i
avg = avg / N

Cells(c + 3, 8) = avg
Next c


End Sub

jnmonroe
03-03-2014, 05:45 AM
Rather than outputting a zero when the bin range does not contain a frequency value, it's probably more appropriate for the VBA to output a blank.


When running the VBA script below, the script throws a Run-time error and stops as soon as it hits a sound frequency (Freq.) bin which is empty. The script should output an average amplitude (Ave.Amp.) of zero if the bin does not contain any frequency values.

Can anyone suggest something which might allow the script to run without the run-time error and place a zero in the Ave.Amp. column if there are no frequency values within that particular bin range?

Thank you.

Sub Histogram()
Dim c As Integer, i As Integer, j As Integer
Dim avg As Double
Dim binlow As Integer, binhigh As Integer
Dim N As Integer

For c = 1 To 16
binlow = Cells(c + 2, 2)
binhigh = Cells(c + 3, 2)
avg = 0
N = 0
For i = 1 To 11
If Cells(i + 2, 6) >= binlow And Cells(i + 2, 6) < binhigh Then
N = N + 1
avg = avg + Cells(i + 2, 7)
End If
Next i
avg = avg / N

Cells(c + 3, 8) = avg
Next c


End Sub

p45cal
03-03-2014, 09:04 AM
change:

avg = avg / Nto:

If N > 0 Then avg = avg / N?
It's balking at a division by 0.

later...

Rather than outputting a zero when the bin range does not contain a frequency value, it's probably more appropriate for the VBA to output a blank.OK.. change:
avg = avg / N
Cells(c + 3, 8) = avgto one line

If N > 0 Then Cells(c + 3, 8) = avg / N Else Cells(c + 3, 8) = Empty

jnmonroe
03-03-2014, 02:21 PM
change:

avg = avg / Nto:

If N > 0 Then avg = avg / N?
It's balking at a division by 0.

later...
OK.. change:
avg = avg / N
Cells(c + 3, 8) = avgto one line

If N > 0 Then Cells(c + 3, 8) = avg / N Else Cells(c + 3, 8) = Empty

p45cal
03-03-2014, 03:00 PM
I'm p45cal, not mark007