Excel VBA function to write to several cells

[duragezic]

I've been trying to do the simplest thing for a while and I think I've finally found out why it doesn't work. Consider this:

Public Function Test(a, b)
Range("A1") = 20
Test = 4
End Function

Then in some other cell, I enter formula =Test(1, 2). This results in #VALUE!. Using the debugger I find it always hits an error when executing the line where it tries to set the value for Range (you don't have to specify Range("A1").Value since Value is its default property).

What puzzled me for so long is that I could type
Range("A1") = 20
in the Immediate Window and cell A1 would be filled with 20

I can also do the same function but as a Sub instead of a Function, then use the Play Macro button and Cell A1 becomes 20. So the problem is that Functions can't seem to write to cells outside of their own.

However, I need it to be a function since I need to pass in arguments, and I need to return several values, so I thought I could directly write these three return values to the cell where they are suppose to go. Any ideas how to do this?

 

[KLin]

It's because the macro recording is within the domain of the worksheet. You need to define where you want to set the value within a public function. Try using Activesheet.Range to see if that works.

 

[xtknight]

I believe other cells are set read-only when you execute a formula inside one cell, simply for security. Having one cell modify another without notice would cause messy access violations and possibly circular references or infinite loops.

You may be able to get around this by hooking into the event loop of the Excel worksheet and listening to the Change event.

http://support.microsoft.com/kb/213566
http://support.microsoft.com/kb/291294
http://msdn.microsoft.com/en-u...eet_events(VS.89).aspx

 

[duragezic]

Yeah I see now. It really isn't a good idea but it would've worked well for what I needed.

Anyway I got around it by passing in a number indicating what cell called the function, and assigned the correct result to the function based on it, and called the function from each cell needing to be filled in.

Thanks.