Error that comes just when I try to use the Worksheet_Change event's Target range to start my procedure

[bernatbosch]

In a standard module, I have written a procedure in order to validate some data values located in a particular worksheet of my workbook.


To do so, the procedure catches some values from my database by using an array and then works with them to get the desired result.


During the process of calculation, several functions are used for solving recurrent calculation procedures within the whole process.


Two of this functions, which are working together into some extent, are the ones I'm showing below:


Private Function VariableID(Rng As Range) As String
'the application of this function pressumes the range passed (Rng) is proven to be in a table object
If ColumnHeader(Rng) Like "V*" And ColumnHeader(Rng) Like "*:*" Then 'its a dynamic variable column
VariableID = Left(ColumnHeader(Rng), InStr(1, ColumnHeader(Rng), ":", vbTextCompare) - 1)
Else 'its not a dynamic variable column
VariableID = ""
End If
End Function


Private Function ColumnHeader(Rng As Range) As String
'the application of this function pressumes the range passed (Rng) is proven to be in a table object
ColumnHeader = CStr(Rng.ListObject.HeaderRowRange(1, Rng.Column - Rng.ListObject.Range.Column + 1))
End Function


The whole procedure uses, for several times, a parameter called InputCell to express the range of the active cell at the beginning (and during) the execution of the macro, parameter which is declared at the beginning of the procedure as follows:


Public Sub MyMacro()
Dim InputCell as Range
Set ImputCell = ActiveCell
etc.
End Sub


The question is:


If I run the whole procedure by pressing F5 and running the macro from the visual basic editor, everything works fine. If I link a button to the macro and click on the button to run the procedure, it does well.


Nevertheless, I need the procedure to run automatically every time I change my worksheet data, so to locate a call of the macro at the Worksheet_Change event module, like this:


Private Sub Worksheet_Change(ByVal Target As Range)
Call MyMacro(Target)
End Sub


As a result of calling the macro through the Worksheet_Change event module and using the parameter Target of the Worksheet_Change event module to retain and pass the coordinates of my active cell range to the procedure located in my standard code module, I had to change a little bit the way MyMacro was identifying this parameter and therefore I adapted the beginning of MyMacro as follows:


Public Sub MyMacro(InputCell as Range)
etc.
End Sub


And so the question is:


Everytime I try to run the macro from the Worksheet_Change event module and using the parameter Target of the Worksheet_Change event module I get an error in the following line of my second function (when the procedure tries to get the parameter named as ColumnHeader):


ColumnHeader = CStr(Rng.ListObject.HeaderRowRange(1, Rng.Column - Rng.ListObject.Range.Column + 1))


The error basically argues that 'object is not set' and I've checked and it looks like in this point the procedure does not recognize anymore the object Rng of the Private Function ColumnHeader


This Rng parameter, now not anymore recognized, is actually a parameter which jumps and passes through one function to another, and ultimately its origin actually is the parameter Target coming from the Worksheet_Change event.


The chain of the passes for this parameter is something like follows:
1.
Private Sub Worksheet_Change(ByVal Target As Range)
Call MyMacro(Target)
End Sub
2.
Public Sub MyMacro(InputCell as Range)
etc.
VariableID(InputCell)
etc.
End Sub
3.
Private Function VariableID(Rng As Range) As String
If ColumnHeader(Rng) Like "V*" And ColumnHeader(Rng) Like "*:*" Then 'its a dynamic variable column
VariableID = Left(ColumnHeader(Rng), InStr(1, ColumnHeader(Rng), ":", vbTextCompare) - 1)
Else 'its not a dynamic variable column
VariableID = ""
End If
End Function
4.
Private Function ColumnHeader(Rng As Range) As String
ColumnHeader = CStr(Rng.ListObject.HeaderRowRange(1, Rng.Column - Rng.ListObject.Range.Column + 1))
End Function


And this is the chain that gives an error at step number 4


The curious thing is, we I declare the parameter InputCell inside the procedure and not pass it from the Worksheet_Change event (Target) the whole procedure works fine and no error is reported.


Can anyone explain me why of this behavior?


Thank you in advance and sorry for my long explanation.

 

[Norie]

Is Rng definitely part of a table/listobject?

 

[bernatbosch]

Yes, absolutely. Actually the procedure starts with checking if the cell is in the DataBodyRange of a ListObject, otherwise skips the whole procedure.
Thanks

 

[Norie]

I can't see anything in the posted code where that check is done.

Can you post the code and also the code that calls ColumnHeader?

 

[bernatbosch]

Ok, I'll do it, but later because now I'm working on it and I have been deconstructing it a little bit...

Anyway, it seems to me that when you are using the parameter Target in the worksheet change module (Private Sub Worksheet_Change(ByVal Target As Range)) the syntax "ByVal" maybe has something to do with the fact that later the procedure cannot use this parameter as a range reference.

Could it be that ByVal just treats Target as a value but not as a worksheet range reference?

 

[bernatbosch]

Actually, If I try to run the following code located in the Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Address
End Sub

it returns an error saying something like 'invalid property' so maybe that means that you cannot use an event Target parameter as if it were a usual range ref (?)

 

[bernatbosch]

Oh, sorry. My mistake. I forgot adding the debug.print syntax in here...


Private Sub Worksheet_Change(ByVal Target As Range)
debug.print Target.Address
End Sub


works fine
never mind, sorry

 

[Norie]

Target is passed as a range object, not value, whether passed ByVal or ByRef.