Excel 2010 VBA User Written Function Modification of Input parameter

Question

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.

With regards

Graham Jones

score 1 · Accepted Answer · 2013-03-07 14:50:37Z

1

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

edited Mar 7, 2013 at 14:50

answered Mar 7, 2013 at 14:43

user2063626

Sign up to request clarification or add additional context in comments.

1 Comment

Graham Over a year ago

Many thanks for this suggestion. I have used this to successfully check my values and alter others as necessary.

John Bustos · Accepted Answer · 2013-03-07 14:42:24Z

As a general rule, you don't want your Excel functions to change their inputs. I have done it before, but it's considered very bad practice, though.

The more acceptable way would be to make a function that calculates the second parameter - This would take 2 inputs:

The first parameter
The default value for the second parameter

Then you use that finction to calculate the second parameter in your worksheet (In other words, rather than have the cell which contains your second parameter just be a set value, it is now calculated via this function) and then your original function can work as always with the second parameter always being the expected value.

I hope this makes sense - If not, just let me know and I'll supply an example.

Collectives™ on Stack Overflow

Excel 2010 VBA User Written Function Modification of Input parameter

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related