Question:

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 Roo

Based on your expectations, change Function into 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 Sub:

<pre class="lang-vb prettyprint-override">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">Option Explicit

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 Integer and Long has no real benefit (and has a performance penalty). Just declare every integer variable as Long.

<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>