Change value of an element in an array VBA

Question

The following user defined VBA function does not give a specific error yet does not return a value. I have no idea why. Can anyone suggest how to amend this code so that it works? Thanks so much!

Public Function mytest(myrange As Range)

myrange(1).Value = myrange(1).Value * 44

mytest = myrange(1).Value

End Function

I would call this function in a cell as follows for example;

=mytest(A1)

Cell A1 would contain a value, say 10. I would hope the function returns value 440.

Even if this approach could work (and Andy explains why it doesn't), changes to the input range would trigger a recalculation, which would again increment the input, etc,etc until the cell value overflowed and you got an error. — Tim Williams
– Tim Williams, Commented Jul 3, 2013 at 1:37

Andy G · Accepted Answer · 2013-07-03 01:23:15Z

1

A user-defined function that you call/use in a worksheet cannot (added: without external help, such as complex win-api calls) change the application-environment. That is, you cannot use an UDF in a cell that would change any other cell, either its value or formatting.

Put it this way, there are no Excel functions that ever change another cell are there?

If you want to change the value of a cell then you need to create and run a sub-procedure.

Added: A function that you want to use in a worksheet should behave like any Excel function; it performs calculations and returns a value. For example:

Public Function MyTest(myRange As Range) As Double
    'return a value by assigning it to the function name:
    MyTest = myRange(1).Value * 44
End Function

edited Jul 3, 2013 at 1:23

answered Jul 3, 2013 at 1:13

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

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

3 Comments

user235319 Over a year ago

So what do you feel is the simplest way round this. I am happy for the values of myrange to stay fixed but I want to perform certain operations such as multiplication on the values contained within and output the result.

brettdj Over a year ago

@andyg careful with the cannot :) complex, but doable. stackoverflow.com/questions/8520732/…

Andy G Over a year ago

@brettdj Gosh, yes, with win-api calls, I can turn my Excel into a piano ;) but, yes I concede the point graciously, he, he!

Collectives™ on Stack Overflow

Change value of an element in an array VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related