I am trying to write a VBA macro that assigns values to specific cells by calculating the average of the cells directly above and below it. I am running it by selecting the Macros button on the Developer toolbar, then I have to type in the name of my function (it doesn't appear on the list) "interpprob" and select run. I then get a popup that states "Argument is not optional." I'm not quite sure what the problem is. The full macro is below. "tstep" is meant to be an array of the set of rows that need some cell values changed.
Function interpprob(f As Integer, d As Integer, spec As String, tstep As Long, above As Long, below As Long, i As Integer, j As Integer) f = 41 d = 441 spec = ETHA tstep(0) = f tstep(1) = f + d tstep(2) = f + 2 * d tstep(3) = f + 5 * d For i = 0 To 4 For j = 52 To 57 above = Cells(tstep(i) - 1, j).Value below = Cells(tstep(i) + 1, j).Value Sheets(spec).Cells(tstep(i), j).Value = (above + below) / 2 Next j Next i End Function
Thanks, BL RooAnswer1:
Based on your expectations, change
Sub and remove the parameters.
Sub interpprob() f = 41 d = 441 spec = "ETHA" tstep(0) = f tstep(1) = f + d tstep(2) = f + 2 * d tstep(3) = f + 5 * d For i = 0 To 3 'Changed from 4 as you do not assign a value to tstep(4) For j = 52 To 57 above = Cells(tstep(i) - 1, j).Value below = Cells(tstep(i) + 1, j).Value Sheets(spec).Cells(tstep(i), j).Value = (above + below) / 2 Next j Next i End Sub
You can also insert the following declarations just after the
Dim f As Long Dim d As Long Dim spec As String Dim tstep(0 To 3) As Long Dim above As Long Dim below As Long Dim i As Long Dim j As Long
It is a practice which pays off when a program grows. It keeps you safe from several kinds of mistakes.
To make this practice mandatory, insert the following directive as first line of the file (just before everything else):<pre class="lang-vb prettyprint-override">
You can also see that type
Integer was replaced by
Long because Integer is too short (–32768 ... +32767) and unpractical for standard use and keeping around both
Long has no real benefit (and has a performance penalty). Just declare every integer variable as
<strong>Credits for suggestions and fixes go to <a href="https://stackoverflow.com/users/6535336/yowe3k" rel="nofollow">YowE3K</a> and <a href="https://stackoverflow.com/users/1961728/robincts" rel="nofollow">robinCTS</a>.</strong>