输入参数的Excel 2010 VBA用户书面功能修改 [英] Excel 2010 VBA User Written Function Modification of Input parameter

问题描述

我对Excel 2010 VBA有疑问.已经做了很多寻找解决方案的尝试，但是答案并没有解决我的问题.希望stackoverflow社区能够为我提供一些启示.

I have a question about Excel 2010 VBA. Done plenty of searching for a solution but the answers don’t address my issue. Hoping that the stackoverflow community will be able to shed some light for me.

我有一个用户编写的函数，该函数从工作表的单元格中调用.可以通过两个参数来调用它-两个单元格都在同一张纸上.

I have a user written function that is called from cells in a worksheet. It is called with two parameters – both cells on the same sheet.

该函数的目的是复查这两个参数，并根据它们的值返回一个字符串作为函数的结果.

The aim of the function is to review the two parameters and to return a string as the result of the function depending upon their values.

到目前为止，这些功能可以按预期工作.

This far the functions works as expected.

但是，如果第一个参数的值具有某个值，我希望能够更改第二个参数的值.这就是我卡住的地方.我找不到任何改变其价值的方法.

However, if the value of the first parameter has a certain value, I want to be able to change the value of the second parameter. This is where I get stuck. I cannot find any way to alter its value.

我尝试仅将参数设置为所需值，但这会导致错误.我以为可以建立指向活动工作表以及单元格的指针，但是我不知道它的引用是什么(因为工作表上会有很多).

I have tried just setting the parameter to the desired value but that causes an error. I thought I might be able to set up a pointer to the active sheet and hence the cell but I don’t know what its reference is (as there will be many on the sheet).

说实话，我不确定我能做我所需要的.如有任何想法或意见，我们将不胜感激.

To be honest, I am not sure that I can do what I need. Any thoughts or comments on a way forward will be gratefully received.

致谢

格雷厄姆·琼斯

推荐答案

由于用户定义函数(UDF)无法更改工作簿/工作表等的状态，因此可以使用工作表更改事件.

As User defined functions(UDF) cannot change the state of the workbook/worksheet etc.You can use the worksheet change event.

将此代码复制到您要输入函数的工作表中

Copy this code to the worksheet where you are entering the function

    Private Sub Worksheet_Change(ByVal Target As Range) 
Application.EnableEvents=False
     if Target.Address = "$A$1" and target.count = 1 Then ' change in first parameter - first parameter
       if Target.Value ="something" then    Range("B1").Value ="change"
         '$A$1 is the location of fist paramenter
         'range("b1") is second parameter on assumption
      end if
   Application.EnableEvents=True
    End Sub 

这篇关于输入参数的Excel 2010 VBA用户书面功能修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！