I am trying to create a user defined function to create a named range and assign a value to the cell. The below code is giving me a #Value error
我正在尝试创建用户定义的函数来创建命名范围并为单元格指定值。下面的代码给了我一个#Value错误
My code:
Public Function NameARange(CValue As String, NameR As String) as String
Dim ReferAdd As String
ReferAdd = "='" & ActiveSheet.Name & "'!" & ActiveCell.Address
ActiveWorkbook.Names.Add Name:=NameR, RefersTo:=ReferAdd
NameARange = CValue
End Function
Any help to fix this code will be much appreciated. Thanks
任何帮助修复此代码将非常感激。谢谢
1 个解决方案
#1
3
Are you sure it's not a #NAME? error that you're getting?
你确定它不是#NAME吗?你得到的错误?
Either way, there are a couple issues with your formula. The short explanation is it doesn't make logical sense.
无论哪种方式,您的公式都存在一些问题。简短的解释是它没有逻辑意义。
Let's say you stick that formula in cell A1 of Sheet1...
假设您将该公式粘贴在Sheet1的单元格A1中...
You're trying to create a named range with a Worksheet Function. Worksheet functions recalculate (re-execute) every time something changes on the worksheet. Excel would try to recreate a new named range by the existing name, over and over and over.
您正在尝试使用工作表函数创建命名范围。每次工作表上的某些更改时,工作表函数都会重新计算(重新执行)。 Excel将尝试通过现有名称重复创建新的命名范围,一遍又一遍。
Imagine if you had to sweep the floor anytime sometimes changed in your house... but sweeping the floor changes your house. You'd be stuck in an infinite loop.
想象一下,如果你不得不随时扫地,有时你的房子会发生变化......但扫地会改变你的房子。你会陷入无限循环。
You're also want the function to assign a formula to the cell that the function is sitting in. What if you could clone yourself, but the only place that clone could ever stand is exactly where you are standing. Wouldn't work out.
您还希望该函数为函数所在的单元格分配公式。如果您可以克隆自己,但克隆唯一可以站立的位置就是您所处的位置。不会工作。
And, finally, you want to finish by returning a value to the same cell that has the function (and the infinite copies of itself)... but not just any value: the value that you called the function with in the first place.
最后,您希望通过将值返回到具有该函数的相同单元格(以及其自身的无限副本)来完成...但不仅仅是任何值:您首先调用函数的值。
It's like a Catch-22 of a Quagmire of a Paradox.
它就像一个悖论泥潭的Catch-22。
There is no solution for what you're trying to do except, "don't". Excel won't let you anyhow, which is good because otherwise the universe just might implode.
除了“不要”之外,没有办法解决你想要做的事情。 Excel不会让你无论如何,这是好的,因为否则宇宙可能会崩溃。
A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:
由工作表单元格中的公式调用的用户定义函数无法更改Microsoft Excel的环境。这意味着这样的功能不能执行以下任何操作:
Insert, delete, or format cells on the spreadsheet.
在电子表格中插入,删除或格式化单元格。
Change another cell's value.
更改另一个单元格的值。
Move, rename, delete, or add sheets to a workbook.
移动,重命名,删除或向工作簿添加工作表。
Change any of the environment options, such as calculation mode or screen views.
更改任何环境选项,例如计算模式或屏幕视图。
Add names to a workbook.
将名称添加到工作簿。
Set properties or execute most methods.
设置属性或执行大多数方法。
The purpose of user-defined functions is to allow the user to create a custom function that is not included in the functions that ship with Microsoft Excel. The functions included in Microsoft Excel also cannot change the environment. Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.
用户定义函数的目的是允许用户创建自定义函数,该函数不包含在Microsoft Excel附带的函数中。 Microsoft Excel中包含的功能也无法更改环境。函数可以执行计算,将值或文本返回到输入它们的单元格。应通过使用Visual Basic子例程进行任何环境更改。
During calculation, Excel examines the precedents of the cell that contains a user-defined function. If not all precedents have been calculated so far during the calculation process, Excel eventually calls the user-defined function and passes a Null or Empty cell to the function. Excel then makes sure that enough calculation passes occur for all precedents to be calculated. During the final calculation pass, the user-defined function is passed the current values of the cells. This can cause the user-defined function to be called more frequently than expected, and with unexpected arguments. Therefore, the user-defined function may return unexpected values.
在计算过程中,Excel将检查包含用户定义函数的单元格的先例。如果在计算过程中到目前为止尚未计算所有先例,Excel最终会调用用户定义的函数并将Null或Empty单元格传递给函数。然后,Excel确保为计算所有先例进行足够的计算通过。在最终计算过程中,用户定义的函数将传递单元格的当前值。这可能导致用户定义的函数比预期更频繁地调用,并且具有意外的参数。因此,用户定义的函数可能会返回意外的值。
For correct calculation, all ranges that are used in the calculation should be passed to the function as arguments. If you do not pass the calculation ranges as arguments, instead of referring to the ranges within the VBA code of the function, Excel cannot account for them within the calculation engine. Therefore, Excel may not adequately calculate the workbook to make sure that all precedents are calculated before calculating the user-defined function.
为了正确计算,计算中使用的所有范围都应作为参数传递给函数。如果未将计算范围作为参数传递,则Excel不能在函数的VBA代码中引用范围,而是无法在计算引擎中考虑它们。因此,Excel可能无法充分计算工作簿,以确保在计算用户定义的函数之前计算所有先例。
(Source: Microsoft : Description of limitations of custom functions in Excel)
(来源:Microsoft:Excel中自定义函数的限制说明)
#1
3
Are you sure it's not a #NAME? error that you're getting?
你确定它不是#NAME吗?你得到的错误?
Either way, there are a couple issues with your formula. The short explanation is it doesn't make logical sense.
无论哪种方式,您的公式都存在一些问题。简短的解释是它没有逻辑意义。
Let's say you stick that formula in cell A1 of Sheet1...
假设您将该公式粘贴在Sheet1的单元格A1中...
You're trying to create a named range with a Worksheet Function. Worksheet functions recalculate (re-execute) every time something changes on the worksheet. Excel would try to recreate a new named range by the existing name, over and over and over.
您正在尝试使用工作表函数创建命名范围。每次工作表上的某些更改时,工作表函数都会重新计算(重新执行)。 Excel将尝试通过现有名称重复创建新的命名范围,一遍又一遍。
Imagine if you had to sweep the floor anytime sometimes changed in your house... but sweeping the floor changes your house. You'd be stuck in an infinite loop.
想象一下,如果你不得不随时扫地,有时你的房子会发生变化......但扫地会改变你的房子。你会陷入无限循环。
You're also want the function to assign a formula to the cell that the function is sitting in. What if you could clone yourself, but the only place that clone could ever stand is exactly where you are standing. Wouldn't work out.
您还希望该函数为函数所在的单元格分配公式。如果您可以克隆自己,但克隆唯一可以站立的位置就是您所处的位置。不会工作。
And, finally, you want to finish by returning a value to the same cell that has the function (and the infinite copies of itself)... but not just any value: the value that you called the function with in the first place.
最后,您希望通过将值返回到具有该函数的相同单元格(以及其自身的无限副本)来完成...但不仅仅是任何值:您首先调用函数的值。
It's like a Catch-22 of a Quagmire of a Paradox.
它就像一个悖论泥潭的Catch-22。
There is no solution for what you're trying to do except, "don't". Excel won't let you anyhow, which is good because otherwise the universe just might implode.
除了“不要”之外,没有办法解决你想要做的事情。 Excel不会让你无论如何,这是好的,因为否则宇宙可能会崩溃。
A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:
由工作表单元格中的公式调用的用户定义函数无法更改Microsoft Excel的环境。这意味着这样的功能不能执行以下任何操作:
Insert, delete, or format cells on the spreadsheet.
在电子表格中插入,删除或格式化单元格。
Change another cell's value.
更改另一个单元格的值。
Move, rename, delete, or add sheets to a workbook.
移动,重命名,删除或向工作簿添加工作表。
Change any of the environment options, such as calculation mode or screen views.
更改任何环境选项,例如计算模式或屏幕视图。
Add names to a workbook.
将名称添加到工作簿。
Set properties or execute most methods.
设置属性或执行大多数方法。
The purpose of user-defined functions is to allow the user to create a custom function that is not included in the functions that ship with Microsoft Excel. The functions included in Microsoft Excel also cannot change the environment. Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.
用户定义函数的目的是允许用户创建自定义函数,该函数不包含在Microsoft Excel附带的函数中。 Microsoft Excel中包含的功能也无法更改环境。函数可以执行计算,将值或文本返回到输入它们的单元格。应通过使用Visual Basic子例程进行任何环境更改。
During calculation, Excel examines the precedents of the cell that contains a user-defined function. If not all precedents have been calculated so far during the calculation process, Excel eventually calls the user-defined function and passes a Null or Empty cell to the function. Excel then makes sure that enough calculation passes occur for all precedents to be calculated. During the final calculation pass, the user-defined function is passed the current values of the cells. This can cause the user-defined function to be called more frequently than expected, and with unexpected arguments. Therefore, the user-defined function may return unexpected values.
在计算过程中,Excel将检查包含用户定义函数的单元格的先例。如果在计算过程中到目前为止尚未计算所有先例,Excel最终会调用用户定义的函数并将Null或Empty单元格传递给函数。然后,Excel确保为计算所有先例进行足够的计算通过。在最终计算过程中,用户定义的函数将传递单元格的当前值。这可能导致用户定义的函数比预期更频繁地调用,并且具有意外的参数。因此,用户定义的函数可能会返回意外的值。
For correct calculation, all ranges that are used in the calculation should be passed to the function as arguments. If you do not pass the calculation ranges as arguments, instead of referring to the ranges within the VBA code of the function, Excel cannot account for them within the calculation engine. Therefore, Excel may not adequately calculate the workbook to make sure that all precedents are calculated before calculating the user-defined function.
为了正确计算,计算中使用的所有范围都应作为参数传递给函数。如果未将计算范围作为参数传递,则Excel不能在函数的VBA代码中引用范围,而是无法在计算引擎中考虑它们。因此,Excel可能无法充分计算工作簿,以确保在计算用户定义的函数之前计算所有先例。
(Source: Microsoft : Description of limitations of custom functions in Excel)
(来源:Microsoft:Excel中自定义函数的限制说明)