3

i've been trying to fix this error for hours now and i can't seem to see what it is that i am doing wrong here.

My macro is as it follows:

Function testValueChange(cell As Range)
    On Error GoTo ErrorHandler

    Dim testValue As String

    testValue = Range(cell.Address)

    MsgBox testValue

    Range("Q2") = "new value"
Exit Function

    ErrorHandler:
        MsgBox "Error #" & Err & " : " & Error(Err)
End Function

I am able to retrieve the "testValue" correctly and it is displayed on the MsgBox. However, when i try to set a new value to the cell "Q2", i get the error "Error #1004: Application-defined or object-defined error."

There are several entries for that error over the web but none of them in this situation when i'm trying to simply set a String value to an empty cell!

The cell "Q2" is empty, i've chosen that cell specifically because it is not merged, nor referenced on another function or anything.

Thanks in advance for all the help!

Will
  • 83
  • 2
  • 6
  • do you call this function from Excel cell or VBA/IDE environment? – Kazimierz Jawor May 03 '13 at 07:40
  • 1
    see [this](http://stackoverflow.com/a/15659852/2143262) if you call your function from Excel cell... – Kazimierz Jawor May 03 '13 at 07:42
  • The function is called from VBA/IDE environment. I'm reading the link you gave me. Thanks. – Will May 03 '13 at 07:43
  • So basically from a Custom Function i can't change a cell value. I've seen several tutorials on the web telling me that i can. Don't know where those information came from... but isn't any way then? Maybe if i use a Sub() instead of a Function? – Will May 03 '13 at 07:45
  • you can if you call it from VBA/IDE within any subroutine or from Immediate Window. and can't change cell value when used as Excel cell function. – Kazimierz Jawor May 03 '13 at 07:46
  • And how do i invoke a Subroutine?? – Will May 03 '13 at 07:50
  • from excel sheet cell? you can't do it so simply. You could use `events` to start any subroutine – Kazimierz Jawor May 03 '13 at 07:51
  • 1
    define which worksheet Range("Q2") belongs to? eg: ws.Range("Q2") = "new value" – littlecodefarmer758 May 03 '13 at 09:17
  • possible duplicate of [Cannot VBA write data to cells in Excel 2007/2010 within a funtion](http://stackoverflow.com/questions/9476282/cannot-vba-write-data-to-cells-in-excel-2007-2010-within-a-funtion) – brettdj May 03 '13 at 09:59
  • 1
    Can you post the code that you are using to call the function? That may shed some light on the issue. Your code works fine for me. – Jon Crowell May 03 '13 at 13:23
  • 1
    @HeadofCatering: I have deleted the answer. Lets wait for OP's reply on how the function is being called. Thanks for correcting me. – shahkalpesh May 03 '13 at 16:09

3 Answers3

1

I'm on Linux OS right now so I can't test this in Excel, but try setting a range object first then assigning the value with .value property. i.e:

Dim RNG as Range
Set RNG = Range("Q2")
RNG.value = "new value"
UberNubIsTrue
  • 632
  • 6
  • 17
0

If you not interested in a return then shahkalpesh has the answer, but if your interested in monitoring the macro silently when run from somewhere else then return boolean like so.

Function testValueChange(cell As Range) as boolean 

    TestValueChange = TRUE 
    On Error GoTo ErrorHandler

    Dim testValue As String

    testValue = Range(cell.Address)

    MsgBox testValue

    Range("Q2") = "new value" 
    Exit Function

ErrorHandler: 
    testValueChange = FALSE
End Function

Usage in a vba sub.

If testValueChange then
    'process worked
Else
    'process didn't work
End if

Unless testvalue was ment to be testValueChange and returned as a string?

glh
  • 4,900
  • 3
  • 23
  • 40
0

You cannot assign values to cells, or change them in any manner, when calling from a Function (or from a call chain that has started by a Function). Only a Sub will work. The only exception is that function value can be assigned to the cell containing the function reference

Dan Simhony
  • 9
  • 1