user defined function help

[largeselection]

I'm having difficulty using a UDF to pass a value in a different column based on the input. So far I have something along the lines of...

Public Function ValueMe(Over As String)
Select Case Over
Case Is = "1st"
ValueMe = Range("ActiveCell.Offset(0, 9)").Value
End Select
End Function

But that doesn't work. I'm trying to say that if in my UDF in the cell I type =ValueMe(reference the cell next to it which has the value "1st" in it), it will return the value of the cell in the same row, 9 columns over.

Any ideas?

 

[MrKowz]

Your case statement is slightly off:

Public Function ValueMe(Over As String)
Select Case Over
    Case "1st"
        ValueMe = Range("ActiveCell.Offset(0, 9)").Value
End Select
End Function

 

[p45cal]

Try:

Public Function ValueMe(Over As String)
Application.Volatile
Select Case Over
  Case Is = "1st"
    ValueMe = Application.Caller.Offset(0, 9).Value
End Select
End Function

If you miss out Application.Volatile you'll have to make sure the sheet is recalculated manually with the likes of say Ctrl+Alt+F9

edit after posting:Both Case "1st" and Case Is "1st" seem to work but MrKowz's version is the more conventional.

 

[largeselection]

Hmm, I was hoping this might help me avoid a circular error, but I'm still getting the circular error (even though now the UDF works as expected- THanks for the help with that)

I was hoping excel would treat that as a value rather than a formula which is circular... back to the drawing board I suppose.

Thanks again!

 

[mikerickson]

This looks very similar to Excel's OFFSET function.

Also, what is the formula in the cell that is generating a Circular Reference Error? (and which cell is it in?)

 

[largeselection]

The circular reference is actually a pretty long trail through different cells. Initially I didn't have this issue because I was just using the result of some formulas and pasting them as values into the new cells where I needed them manually.

However, I need the ability to be able to retrieve a certain formula's result based on input in the column next to it. That is what is causing the circular reference because now instead of pasting the actual number as a value I am trying to use a formula and it does not like that.

So I was hoping that by using a UDF to say that the result of the UDF is the value of the cell 9 columns to the right that it would fix the circular error, but apparently it doesn't work like that. Maybe I can figure something out with paste special values or something

 

[largeselection]

Is it possible to have excel treat another cell as the value of the formula rather than a formula?

So if in cell A1 I have the formula = 2 + A2, and A2 is currently = 2. Is it possible to do something so that a function would treat A1 as "4" and not a formula that results in 4? So a function that would treat A1 as its current value

 

[mikerickson]

Is it possible to have excel treat another cell as the value of the formula rather than a formula?

So if in cell A1 I have the formula = 2 + A2, and A2 is currently = 2. Is it possible to do something so that a function would treat A1 as "4" and not a formula that results in 4? So a function that would treat A1 as its current value

All functions act that way.

 

[largeselection]

? Well i'm not getting anywhere with paste special values in any way in a UDF.

I almost had something that looked like it worked by using offset(0,9).text instead of .value, but that didn't update as I updated some of the other cells in the circlular reference.

Now I'm looking into maybe adding an iterative portion? So maybe putting something that says to just iterate the circular reference one time.

What do you think about that to accomplish what I'm trying to do?

 

[mikerickson]

UDF's, when called from the worksheet, return a value, they cannot change the environment by (for example) coloring cells or by copy/pasting into cells.

The problem is that you DO have a circular reference. Those cause problems, even if you contrive a work-around to get Excel to accept them. Excel's Circular Reference notice is a warning that the spreadsheet needs to be re-designed, not an obstacle that needs to be overcome.