VBA“参数不可选"-不确定如何声明变量 [英] VBA "Argument Not Optional" - unsure how to declare variables

问题描述

我正在尝试编写一个VBA宏，该宏通过计算直接位于其上方和下方的单元格的平均值来为特定单元格分配值.我通过选择开发人员"工具栏上的宏"按钮来运行它，然后我必须输入函数名称(它没有出现在列表中)"interpprob"并选择运行.然后，我弹出一个窗口，指出参数不是可选的".我不太清楚是什么问题.完整的宏在下面."tstep"是需要更改某些单元格值的一组行的数组.

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

谢谢，BL Roo

推荐答案

根据您的期望，将 Function 更改为 Sub ，然后删除参数.

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

您还可以在 Sub 之后插入以下声明:

You can also insert the following declarations just after the Sub:

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

Option Explicit

您还可以看到类型 Integer 被替换为 Long ，因为Integer太短(–32768 ... +32767)并且对于标准使用和保持不实用性不大 Integer 和 Long 都没有真正的好处(并且会降低性能).只需将每个整数变量声明为 Long .

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.

有关建议和修复的信息，请访问 YowE3K 和

Credits for suggestions and fixes go to YowE3K and robinCTS.

这篇关于VBA“参数不可选"-不确定如何声明变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！