0

I'm brand new to VBA and using excel 2013. I've been racking my brain all day with this error! The lines marked with asteriks are getting
"Application-defined or object-defined error"

My function is supposed to do 2 things...
1) give the current cell a value
2) change the fill color of another cell

There are no issues with #1, but #2 keeps erroring out
This is the code that I'm currently using...
I'm calling it from within the cell in excel, giving it the formula: =ScoreIt()

Function ScoreIt()

Dim TotalVal As Integer, LRVal As Integer, LYVal As Integer, LGVal As Integer
TotalVal = 0
LRVal = 0
LYVal = 1
LGVal = 2

Dim CurrentRow As String, BedCell As String, Beds As Integer
CurrentRow = ActiveCell.Row
BedCell = Range("K" & CurrentRow).Address(False, False)
Beds = Range(BedCell).Value

If (Beds < 2) Or (Beds > 5) Then
    TotalVal = TotalVal + LRVal
    ** Range(BedCell).Interior.ColorIndex = 38 **
ElseIf (Beds = 2) Or (Beds = 5) Then
    TotalVal = TotalVal + LYVal
    ** Range(BedCell).Interior.ColorIndex = 36 **
ElseIf (Beds = 3) Or (Beds = 4) Then
    TotalVal = TotalVal + LGVal
    ** Range(BedCell).Interior.ColorIndex = 35 **
End If

ScoreIt = TotalVal

End Function



Thank you

Improve this question
2
  • Why not just make the BedCell a Range object and just call set BedCell = Range("K" & CurrentRow) then just use BedCell.Interior.ColorIndex = 38 Commented Dec 30, 2014 at 5:46
  • I've tried this already, it did not work either, BedCell returns the value in the cell rather than the cell's address/location. I then tried adding .Address(False, False) to the end of it, but this just ended with a type mismatch error. Commented Dec 30, 2014 at 6:10

1 Answer 1

Reset to default
1

Worksheet formulas, even UDF ones, cannot change the property of a range object or return a value/change a property in another range object. The function's return value is restricted to the cell from which it was called.

I would suggest re-designing your code and split into a function for the return value, and a private sub for the colour change.

Here is an excerpt from the Microsoft Support site:

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

-Insert, delete, or format cells on the spreadsheet.

-Change another cell's value.

-Move, rename, delete, or add sheets to a workbook.

-Change any of the environment options, such as calculation mode or screen views.

-Add names to a workbook.

-Set properties or execute most methods.

The purpose of user-defined functions is to allow the user to create a custom function that is not included in the functions that ship with Microsoft Excel. The functions included in Microsoft Excel also cannot change the environment. Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.

You can find the full article here: http://support.microsoft.com/kb/170787

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.