VBA fails when setting range in Excel

Question

Using an example from MSDN I'm trying to set the value of a cell in Excel 2007. Here are the steps I took:

In the Excel options choose Enable all Macro's and Trust access to VBA object model.
Create a new Excel WorkBook.
Add a new VBA Module.

Insert the following function:

Function MyTest(rg As Range)
    rg.Value = 1
    MyTest = 1234
End Function

Add the formula =MyTest(B1) to the cell at A1.

When the first line of MyTest is executed, the debugger simple fails. Without any error it stops debugging and displays #VALUE! in A1. B1 remains empty.

I've tried setting .Formula instead of .Value. I've tried using ActiveSheet and Worksheets["Sheet1"] to access Worksheets. What can possibly be the cause of this error?

Why do you try to set the value on rg? What happens when you comment out that line? — Tomalak
– Tomalak, Commented Jul 17, 2011 at 13:59
When I comment out rg.Formula = 1, A1 changes to 1234 as expected. I try to set rg because I want to change cells programmatically. — Matt
– Matt, Commented Jul 17, 2011 at 14:03
I would not be surprised if the range was read-only. You cannot set more than one cell value with a built-in cell function either. Just set a break point on the offending line to see what the error is in the debugger. — Tomalak
– Tomalak, Commented Jul 17, 2011 at 14:08
I have set a breakpoint but cannot deduce what the problem is. I suspect you're right in it being read-only, I just cannot see why. — Matt
– Matt, Commented Jul 17, 2011 at 14:15
I don't think you need to do item 1 on your list. In particular "Trust access to VBA object model" can be dangerous. — David Heffernan
– David Heffernan, Commented Jul 17, 2011 at 14:16

David Heffernan · Accepted Answer · 2011-07-17 14:31:55Z

7

VBA functions that are called from cell formulae are not allowed to modify cell contents. This restriction is in place so that Excel can keep track of dependencies.

If you need to modify cell contents then you'll have to invoke your VBA through some other mechanism, e.g. a user form.

answered Jul 17, 2011 at 14:31

David Heffernan

616k46 gold badges1.1k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

VBA fails when setting range in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related