＃名称？ VBA函数的Excel中的错误 [英] #NAME? error in Excel for VBA Function

问题描述

我正在制作第一个VBA程序，并尝试运行以下功能。该函数检查第一行的特定命名范围，该行的值不大于其前导值，但小于1。

  public function findPurchase（）
 
 Dim CRT As Range 
设置CRT =范围（CostRateTable）
 
 Dim existsBetter As Boolean 
 existsBetter = True 
 
 Dim r As Integer 
r = 2 
 Dim c As Integer 
c = 4 
 
 While existsBetter 
 
 Dim Found As Boolean 
 FoundBetter = False 
 
 While Not FoundBetter和c <= CRT.Columns.Count 
如果CRT（r，c）> CRT（r，2）和CRT（r，c） 1然后
 FoundBetter = True 
 Else 
c = c + 1 
结束如果
 Wend 
 
 
 existsBetter = FoundBetter 
如果existsBetter然后
r = r + 1 
结束如果
 Wend 
 
 findPurchase = CRT（r，3）
'MsgBox（findPurchase）
结束功能
  

我知道这个功能是什么，因为我已经手动检查值表，从MsgBox中删除注释，并使用调试工具进入和退出每个功能步骤，通过该表。但是，当我使用 = findPurchase（）引用Excel中的函数时，我会得到一个 #NAME？错误。当我开始输入名称时，该功能甚至显示在功能自动完成框中。当我编写其他函数时，无论是否有参数，我可以引用它们，例如：

 函数addtwo（） 
 addtwo = 1 + 2 
结束功能
  

我做错了什么我的功能导致它不工作？

解决方案

您收到该错误，因为您有一个与功能。

更改该名称以说 find_Purchase ，一切都会很好:)请参见下图...

I am making my first VBA program and trying to run the following function. The function checks a specific named range for the first row which does not have a value greater than it's leading value, but less than 1.

Public Function findPurchase()

Dim CRT As Range
Set CRT = Range("CostRateTable")

Dim existsBetter As Boolean
existsBetter = True

Dim r As Integer
r = 2
Dim c As Integer
c = 4

While existsBetter

    Dim Found As Boolean
    FoundBetter = False

    While Not FoundBetter And c <= CRT.Columns.Count
        If CRT(r, c) > CRT(r, 2) And CRT(r, c) < 1 Then
            FoundBetter = True
        Else
            c = c + 1
        End If
    Wend


    existsBetter = FoundBetter
    If existsBetter Then
        r = r + 1
    End If
Wend

findPurchase = CRT(r, 3)
'MsgBox(findPurchase)
End Function

I know the function does what it is supposed to because I have both manually checked the table of values, removed the comment ' from the MsgBox, and used the debug tools to step in and out of each of the functions steps as it went through the table. However, when I reference the function in Excel with =findPurchase() I'm given a #NAME? error. The function even shows up in the function auto-complete box when I begin to type its name. When I write other functions, both with and without parameters, I can reference them just fine, for example:

Function addtwo()
    addtwo = 1 + 2
End Function

What am I doing wrong with my function which causes it not to work?

解决方案

You are getting that error because you have a module with the same name as the function.

Change that name to say find_Purchase and everything will be fine :) See the image below...

这篇关于＃名称？ VBA函数的Excel中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！