#Value error when setting a Cell value

[PhoenixJoe]

I'm a newbie to Visual Basic on Excel. I wanted to do a very simple task, and have wasted a couple hours trying. I'm hoping somebody can help. Here is a very simple function, but I get #Value error when I use it.

Function Test()
' The following doesn't work - I get a # Value error: WHY
[A2].Value = 34

' The following does work:
Test = [B2].Value

End Function

 

[IRstuff]

seems to work on my copy of Excel

TTFN

FAQ731-376

 

[PhoenixJoe]

Thanks a lot for trying it out. That's interesting. I thought it should work! From 'Help About', I'm using Excel 2003 (11.8142.8132) SP2. I wonder if there's a problem with this version (I doubt it), or I wonder if I don't have something configured correctly? Maybe an add-on or something?

Maybe I should provide some more details in case I'm missing something very fundamental. In Excel I selected Tools->Macro->Visual Basic Editor. I then picked 'Insert->Module' and put the code in the module.

Thanks again for the feedback.

 

[IRstuff]

It works in both 2000 and 2002. You might check to see if your help file show this:

Referring to Cells by Using Shortcut Notation


You can use either the A1 reference style or a named range within brackets as a shortcut for the Range property. You don't have to type the word "Range" or use quotation marks, as shown in the following examples.

Sub ClearRange()
Worksheets("Sheet1").[A1:B5].ClearContents
End Sub

Sub SetValue()
[MyRange].Value = 30
End Sub

TTFN

FAQ731-376

 

[PhoenixJoe]

I read through the 'help' a lot, and found stuff on the web, before posting this thread. I learned that there are a lot of different syntax options to set the value of the cell (short cut, 'workbook', 'worksheet', cell, range, A1 notation, etc). I tried them all (really just reaching for straws), and many other things. Since it works on your computer, my guess is that there is something in my environment that is preventing this. I tried two of my coworkers computers, and none of them worked either.

This is disappointing, but I need to move on to plan B so I can finish my task. I will continue to investigate this in the background, because I think this kind of VB programming in Excel will be useful to me, if I can get it to work.

 

[CFSEng]

PhoenixJoe,
Some things are not making sense. Your above code is a function, not a subroutine. How are you calling the function? If you are trying to set a cell value with a user defined function, then you would use

Function Test()
Test = [B2].Value
End Function

then in your spreadsheet, you would set the formula for cell A2 to =Test which in turn makes its value equal to the value of B2

If my memory serves me correct, you can't change the value of other cells with a user defined function

 

[PhoenixJoe]

CFSEng,

Thanks for the feedback. What you described works, as I mentioned in my original post. However, you mention "you can't change the value of other cells with a user defined function". That is exactly what I want to do. IRstuff said it worked on his computer.

I ran the function in the VB debugger, and it worked. However, when I use it in the spreadsheet it doesn't work. Maybe the problem is, as you say, "how I'm calling the function". I am just putting '=Test()' in a cell. Is there another way to call the function? As I said, I'm a newbie at doing VB in Excel, and I'm probably doing something dumb.

Thanks again for your help.

 

[PhoenixJoe]

OK, now I see! I have to put it in as a 'sub' and run it as a macro. DUH! Can I put a button on the spreadsheet that will run the macro when I click it?

Thanks again

 

[PhoenixJoe]

Nevermind. I added a button and called my Macro. I'm in business. I guess I just can't change cells from a user defined function.

Thanks for your help CFCEng and IRstuff.

 

[joerd]

For your info and entertainment, you might want to read

http://www.j-walk.com/ss/excel/odd/odd06.htm

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.