[讨论]有没有尝试过在自定义函数中改变其他单元格的值？

sunsoncheng

值没改过,但画线倒是完全没问题的,这个我验证过,而且天天在用!

zxzx2733

QUOTE:

以下是引用sunsoncheng在2008-1-9 21:00:05的发言：

值没改过,但画线倒是完全没问题的,这个我验证过,而且天天在用!

有没有实例？瞧瞧

不会是和LZ一样的大师吧

stanleypan

Excel Function  一般不能更改单元格的值. (Change a cell value)

Only the calling cell or array and only by return value. 但还是可以实现更改单元格的值的.

Option Explicit
'*************************************************************************
'**╭⌒╮⌒╮          ︸ ︷  ﹀ ︿
'**╱◥██◣ Home      ﹀ ︷    ︸
'**|田|田 田|﹏﹏﹏﹏﹏﹏﹏﹏﹏﹏﹏﹏
'**╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬╬
'**Mail:Stanley.pan2000@Hotmail.com
'*************************************************************************
Function ChangeCellValue(ByVal SelectCells As Range)
'Only the calling cell or array and only by return value
'Call changeCell
'SelectCells.Value = "testing"
'Application.Volatile
  On Error Resume Next
  ChangeCellValue = SelectCells.Interior.ColorIndex
  SelectCells.Value2 = "Only for testing"
End Function

Sub changeCell()
  Range("A1") = "testing"
End Sub

9FeTfvvx.zip (6.69 KB, 下载次数: 26)

2008-1-9 21:39 上传

点击文件名下载附件
[讨论]有没有尝试过在自定义函数中改变其他单元格的值？

[此贴子已经被作者于2008-1-9 21:41:25编辑过]

andysky

QUOTE:

以下是引用stanleypan在2008-1-9 21:40:06的发言：

Excel Function  一般不能更改单元格的值. (Change a cell value)

Only the calling cell or array and only by return value. 但还是可以实现更改单元格的值的.

你这个实例并没有改变其它单元格的值啊？

仅仅是求参数的Interior.ColorIndex而已

stanleypan

你刷新一下,单元格A1的值不就变了吗.

stanleypan

我只是让大家看到,变化之前与变化之后的不同效果.

stanleypan

点一下fx 就可以看到单元格A1 值的变化.

andysky

QUOTE:

以下是引用stanleypan在2008-1-10 8:37:43的发言：
点一下fx 就可以看到单元格A1 值的变化.

确实可以更改。不过这种方式和常规函数的操作方式大大不同。无法在工作中运用。

如果输入函数后单击回车就能改变参数所指定的单元格区域的值倒是有用。

BrandSex

QUOTE:

以下是引用stanleypan在2008-1-10 8:37:43的发言：
点一下fx 就可以看到单元格A1 值的变化.

不错不错，我可以在使用A1之前调用Range("a1").Calculate

了解了一下UDF，果然有很多限制，看来很多东东还是要用UserForm来实现

Benefits of User Defined Excel Functions

• Create a complex or custom math function.
• Simplify formulas that would otherwise be extremely long "mega formulas".
• Diagnostics such as checking cell formats.
• Custom text manipulation.
• Advanced array formulas and matrix functions.

Limitations of UDF's

• Cannot "record" an Excel UDF like you can an Excel macro.
• More limited than regular VBA macros. UDF's cannot alter the structure or format of a worksheet or cell.
• If you call another function or macro from a UDF, the other macro is under the same limitations as the UDF.
• Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros".
• Excel user defined functions in VBA are usually much slower than functions compiled in C++ or FORTRAN.
• Often difficult to track errors.
• If you create an add-in containing your UDF's, you may forget that you have used a custom function, making the file less sharable.
• Adding user defined functions to your workbook will trigger the "macro" flag (a security issue: Tools > Macros > Security...).

andysky

..

[此贴子已经被作者于2008-1-10 10:03:38编辑过]