Using a FOR loop within an Excel VBA Function

Question

I created a simple function in MATLAB, and am trying to convert the function into Excel VBA function. My goal is to create an Excel formula =RT('range of dB levels', 'delta-time') and output the estimated reverberation time. The math is simple, see MATLAB code below:

function rr=RT(lvl_broad, dt)
n=12; %number of samples within slope calc
slope=zeros(length(lvl_broad), 1);
    for i=1:length(lvl_broad)
        if i<((n/2)+1) | i>length(lvl_broad)-(n/2)-1
            slope(i)=0;
        else
            slope(i)=(lvl_broad(i+(n/2))-lvl_broad(i-(n/2)))/n;
        end
    end
min_slope=min(slope); 
rr=abs(dt./min_slope)*60;
end

In excel, I modified/simplified this until I no longer got errors, however, the cell that I enter my 'RT' function in returns #VALUE and I do not know why. Does anything stand out in the code below? (note I changed the input range from lvl_broad to InterruptedNZ)

Function RT(InterruptedNZ, dt)

Dim Slope As Double
Slope = Slope(InterruptedNZ.Height, 1)

For i = 1 To InterruptedNZ.Height
        If i < ((6) + 1) Or i > (InterruptedNZ.Height - (6) - 1) Then
            Slope(i) = 0
        Else
            Slope(i) = (InterruptedNZ(i + (6)) - InterruptedNZ(i - (6))) / 12
        End If
        Next
End

min_slope = Application.WorksheetFunction.Min(Slope)

RT = Abs((dt / min_slope) * 60)


End Function

Have you thrown a debug point in so you can see where it's falling over? Also, can you provide a working example with values to pass to your function? — Skin
– Skin, Commented Jan 27, 2022 at 2:52
One thing stands out immediately is that you are using Slope as a variable and a Function Name. Definite No No. You're also declaring Slope in the Function but referencing it outside the function in the call to min_slope. — RetiredGeek
– RetiredGeek, Commented Jan 27, 2022 at 3:00
What is the purpose of this line: Slope = Slope(InterruptedNZ.Height, 1)? — bugdrown
– bugdrown, Commented Jan 27, 2022 at 3:03
You also seem to have a lonely End in the middle of your procedure that's left from your matlab code. This End will make it so that execution exists the function before assigning any value to the RT function. — DecimalTurn
– DecimalTurn, Commented Jan 27, 2022 at 3:08
If InterruptedNZ is an excel range object, InterruptedNZ.Height will give you the height of that range in points (~1/72 of an inch) which seems absurd . Please clarify a bit more. — Charlie
– Charlie, Commented Jan 27, 2022 at 3:22

DecimalTurn · Accepted Answer · 2022-01-27 04:36:53Z

Here are some tips to translate MATLAB code into VBA code:

length()

If you are trying to get the dimensions of a range, you'll need to use the .Rows.Count or .Columns.Count properties on the range you are working with.

PERFORMANCE NOTE: When you have a large enough range, it's a good idea to store the values of the range inside an array since you will reduce the number of times you access data from the sheets which can comme with lot of overhead. If so, you'll have to use ubound() and lbound().

zeros()

In VBA, there is no exact equivalent to the zeros() function in MATLAB. The way we would initialize an array of zeros would simply be by initializing an array of doubles (or another numerical type). And since the default value of a double is zero, we don't need to do anything else :

Dim Slope() As Double
ReDim Slope(1 To InterruptedNZ.Rows.Count)

Note that you cannot pass the dimensions in the Dim statement since it only accepts constants as arguments, so we need to create Slope as a dynamic array of doubles and then redimension it to the desired size.

Putting these two principles together, it seems like your code would look something like this:

Function RT(ByRef InterruptedNZ As Range, ByVal dt As Double)

    Dim Slope() As Double
    ReDim Slope(1 To InterruptedNZ.Rows.Count)
    
    Dim i As Long
    For i = 1 To InterruptedNZ.Rows.Count
        If i < ((6) + 1) Or i > (InterruptedNZ.Rows.Count - (6) - 1) Then
            Slope(i) = 0
        Else
            Slope(i) = (InterruptedNZ(i + (6)) - InterruptedNZ(i - (6))) / 12
        End If
    Next

    Dim min_slope As Double
    min_slope = Application.WorksheetFunction.Min(Slope)
    
    RT = Abs((dt / min_slope) * 60)


End Function

Addtionnal notes:

Refering to cells from a range like this InterruptedNZ(i) works but it is good practice to be more specific like this (assuming column range) :
```
   InterruptedNZ.Cells(i,1)
```
During my tests, I had a division by zero error since min_slope was zero. You might want to account for that in your code.

Collectives™ on Stack Overflow

Using a FOR loop within an Excel VBA Function

1 Answer 1

length()

zeros()

Addtionnal notes:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

length()

zeros()

Addtionnal notes:

Comments

Your Answer

Sign up or log in

Post as a guest

Related