Question about setting a variable

[Mr930]

I had some code that worked fine where I set a variable using its range name like this:

[Constraint1] = szTemp

The function that contained this code was called and triggered by a worksheet change event.

I now have it set up that the function is called by a cell formula, and now when that line of code is executed, everything stops cold. When I step through the debugger the active line just goes away as if I had reached the end of my code.

The only change I can think of is the way the function is called and I am not sure if that is the culprit or not. Is there some rule for setting variables by using their range name?

thanks
Fred Emmerich

 

[Norie]

Fred

You can't use a worksheet function to set the value of a cell, you can only return a value to the cell it's in.

 

[Jonmo1]

I now have it set up that the function is called by a cell formula

A function (any function, built in or VBA UDF) when called from within a cell formula CANNOT change the value or format of any other cell.
It can only return a value to the cell that the formula is entered in.

Sorry.

 

[Mr930]

The return value was just a placeholder. In the function itself I change multiple ranges. I have a couple of lines that erase ranges by name and even though the lines execute fine, the range is not cleared (just discovered this). Originally the ranges I was messing with were on the same sheet as the code, now they are not and I am wondering if that is the problem. I thought named ranges were workbook level variables, are they worksheet level?

thanks



quOTE=Norie;2714585]Fred

You can't use a worksheet function to set the value of a cell, you can only return a value to the cell it's in.[/QUOTE]

 

[Mr930]

Okay, it finally sunk in. I leanred something new today.

thanks

 

[Jonmo1]

this line

[Constraint1] = szTemp

Is NOT setting a variable.

It is placing the value of the variable szTemp into the Range Named Constraint1.

Therefor, it cannot be done when the function is called from a cell formula.
Because cell formulas CANNOT change the value of OTHER cells,
They can only return a value to the cell the formula is entered in.

 

[Jonmo1]

Okay, it finally sunk in. I leanred something new today.

thanks

Hey, you know what...
I just learned something new too...

I didn't know you could refer to a named range like that
[Constraint1]

I thought you could only do it like
Range("Constraint1")



You never stop learning...