×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

#Value error when setting a Cell value

#Value error when setting a Cell value

#Value error when setting a Cell value

(OP)
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

RE: #Value error when setting a Cell value

(OP)
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.

RE: #Value error when setting a Cell value

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: Eng-Tips.com Forum Policies

RE: #Value error when setting a Cell value

(OP)
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.

RE: #Value error when setting a Cell value

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

RE: #Value error when setting a Cell value

(OP)
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.

RE: #Value error when setting a Cell value

(OP)
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

RE: #Value error when setting a Cell value

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources