VBA “Argument Not Optional” - unsure how to declare variables


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>


  • Pythons argparse default value doesn't work
  • Cypher - matching two different possible paths and return both
  • Julia: How to give multiple workers access to functions that are 'include(…)' into a modul
  • Scipy Leastsq Optional Output Variable (Mesg)
  • Center align outputs in ipython notebook
  • calculate gradient output for Theta update rule
  • custom UITableViewCell with image for highlighting
  • print() is showing quotation marks in results
  • Make VS2015 use angular-cli ng at build time in a .NET project
  • Android fill_parent issue
  • Spring Data JPA custom method causing PropertyReferenceException
  • Getting last autonumber in access
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • Is there a amazon webstore API for customers?
  • Get object from AWS S3 as a stream
  • Excel - Autoshape get it's name from cell (value)
  • Sending data from AppleScript to FileMaker records
  • Join two tables and save into third-sql
  • How to apply VCL Styles to DLL-based forms in Inno Setup?
  • vba code to select only visible cells in specific column except heading
  • How to model a transition system with SPIN
  • ORA-29908: missing primary invocation for ancillary operator
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • Do create extension work in single-user mode in postgres?
  • Do I've to free mysql result after storing it?
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Transpose CSV data with awk (pivot transformation)
  • Benchmarking RAM performance - UWP and C#
  • How to set the response of a form post action to a iframe source?
  • Angular 2 constructor injection vs direct access
  • Change div Background jquery
  • Qt: Run a script BEFORE make
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Sorting a 2D array using the second column C++
  • reshape alternating columns in less time and using less memory
  • Binding checkboxes to object values in AngularJs
  • Converting MP3 duration time
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?