错误:用户定义函数中的“公式中使用的值的数据类型错误" [英] Error : 'A Value used in the formula is of the wrong data type' with User Defined Function

问题描述

我正在构建用户定义函数.

I am building a User Defined Function.

我得到一个错误

公式中使用的值的数据类型错误

A Value used in the formula is of the wrong data type

我正在尝试构建一个函数，该函数添加一个注释，也将注释添加到另一个位置，因为注释总是成对出现.

I am trying to build a function that adding one comment, will also add the comment to another location as the comments always come in pairs.

Public Function AddComments(vesselCell As Variant, shopCell As Variant, comment As String) As Variant

    Range(vesselCell).AddComment (comment)

    Worksheets("Shop").Range(shopCell).Value = comment

End Function

我已将其选出引起问题的第三行代码.

I have singled it out to the third line of code causing the problem.

当前设置为容器单元"，将注释添加到工作表中，然后商店"工作表具有一个供注释的栏目.

The setup currently is the Vessel Cell will be a comment added to the sheet, and then the Shop sheet has a section for comments as a column.

推荐答案

假定vesselCell和shopCell都是Range对象，Range(vesselCell)和Worksheets("Shop").Range(shopCell)都是问题的一部分.

Assuming vesselCell and shopCell are both Range objects, Range(vesselCell) and Worksheets("Shop").Range(shopCell) are part of the problem.

将它们设为Range，而不是Variant.

vesselCell.AddComment comment
shopCell.Value = comment

现在，此代码在UDF中不合法.将您的过程设为Sub过程(删除As Variant返回类型)，然后从其他VBA代码中调用它，或将其附加到形状或按钮的OnAction属性上.

Now, this code isn't legal in a UDF. Make your procedure a Sub procedure (remove the As Variant return type), and invoke it from other VBA code, or attach it to a shape or button's OnAction property.

用户定义的函数接受输入，计算结果，然后将其返回给调用单元:您的代码未返回任何内容，强烈表明Function在这里不合适

User-Defined Functions take inputs, compute a result, and then return that result to the calling cell: your code not returning anything is a strong indicator that a Function isn't appropriate here.

这篇关于错误:用户定义函数中的“公式中使用的值的数据类型错误"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！