Data Validation

[tiredofit]

I have a workbook where I have added some code so that a cell can only take certain values, via Data Validation.

The list exceeds 255 characters, ie the list of available values are:

"a,b,c,d,e, etc. "

When I save, close then reopen the workbook, I get this error message:

[code]

We found a problem with some content in 'Your workbook.xlsm.' Do you want to try to recover as much as we can? If you trust the source of this workbook, click Yes.

then it shows:

Removed feature: Data Validation from /xl/worksheets/sheet1.xml part

Has this got anything to do with the Data Validation being too long?

Thanks

 

[RoryA]

Create any worksheet event that gives you a Range parameter and you will see. For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

According to you, you're passing an object ByVal, so you can change it without affecting the "original" object?

No, that is not what I said.

When you pass an object, you actually pass a pointer to the object's address in memory. If you pass that pointer ByRef, then the called routine can make that pointer point to something else. If you pass it ByVal, it can't. Passing ByRef or ByVal does not at all affect what you can do with the object in question, only what you can do with the pointer.

That is why worksheet events pass the Range ByVal - there could be multiple routines monitoring that event and they can all effect changes to the range in question, but none of them can alter which range is being referred to. If you passed the range ByRef, the first listener might unintentionally change the range from say A1 to A2, and subsequent events would be given the wrong range.

Here's a simple example to show the difference:

Sub foo()
    Dim rg As Range
    Set rg = Range("A1")
    Debug.Print "rg starts assigned " & rg.Address
    Call rangeByVal(rg)
    Debug.Print "rg is now " & rg.Address
    Call rangeByRef(rg)
    Debug.Print "Rg is now " & rg.Address
End Sub
Sub rangeByVal(ByVal rgin As Range)
    Set rgin = Range("A3")
End Sub
Sub rangeByRef(ByRef rgin As Range)
    Set rgin = Range("A3")
End Sub

 

[abbeyWigan]

No, that is not what I said.

When you pass an object, you actually pass a pointer to the object's address in memory. If you pass that pointer ByRef, then the called routine can make that pointer point to something else. If you pass it ByVal, it can't. Passing ByRef or ByVal does not at all affect what you can do with the object in question, only what you can do with the pointer.

That is why worksheet events pass the Range ByVal - there could be multiple routines monitoring that event and they can all effect changes to the range in question, but none of them can alter which range is being referred to. If you passed the range ByRef, the first listener might unintentionally change the range from say A1 to A2, and subsequent events would be given the wrong range.

Here's a simple example to show the difference...

Apologies I misunderstood what you said. You are absolutely correct and thanks for the clarification given.

I also didn't quite explain myself very well in my own posts but immaterial. Your point is well made.

Regards.