0

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.

user235319
  • 3
  • 1
  • 2
  • 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 Jul 03 '13 at 01:37

1 Answers1

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
Andy G
  • 19,232
  • 5
  • 47
  • 69
  • 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. – user235319 Jul 03 '13 at 01:18
  • @andyg careful with the *cannot* :) complex, but doable. http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change/8711582#8711582 – brettdj Jul 03 '13 at 01:21
  • @brettdj Gosh, yes, with win-api calls, I can turn my Excel into a piano ;) but, yes I concede the point graciously, he, he! – Andy G Jul 03 '13 at 01:29