call sub(arg1 as Range): Error 424: object required

Hans Troost

New Member
Joined
Jan 6, 2015
Messages
24
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
dear all

My question is only technical: how to call a sub expecting a Range argument, using a range.
For functionality the "poor man's solution" shown below works, so I can continue, but don't like it.

I have to call mysub (arg1 as Range) for a column in a table.

the "poor man's solution" that perfectly works is:

mysub(ActiveSheet.Range("table name[column name]")) ' as in ActiveSheet.Range("Uitgiftelijst[VB.nu]"))

But I don't find it very elegant, because I copy/pasted the table from another sheet.
So I have a named range "table name"
- and defined it as ListObject
- and did a lot of things (formatting etc.) with it (except calling a this sub)
- e.g. mytable.ListColumns("column name").DataBodyRange.Font.name = "font name" etc. etc.


dim myTable as Listobject
set myTable = activesheet.listobjects("table name")

But calling the mysub(arg1 as Range) fails all the time with error 424: Object required
What I tried is
mysub(myTable.ListColums("column name").DataBodyRange
mysub(myTable.ListColums("column name").Range
mysub(myTable.ListColums("column name") resulting in Compile Error, Data type mismatch


Question:
how to call a mysub (arg1 as Range) with the elegant use of my already defined table references?

Example table and code : see the code for what i tired to achieve. Kept the working version and commented out the error triggering lines:

CategoryJanFebMarAprMayJunTotal Column
Alpha533327495039251
Beta427841207462317
Charlie392164222741214
Deltal593331614537266
Echo293129554469257
Foxtrot433035446354269
Golf224335536572290
Hotel714241796131325


VBA Code:
Option Explicit

Sub CondForm()

Dim myTbl As ListObject

Set myTbl = ActiveSheet.ListObjects("myTable")

With myTbl.ListColumns("Category").DataBodyRange
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$H2>260"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = vbRed
        .TintAndShade = 0.875
    End With
    .FormatConditions(1).StopIfTrue = False
End With

RemoveCF (ActiveSheet.Range("myTable[Category]")) ' Works! but prefer to not use Activesheet.<something> but functionality of my defined table: myTbl.ListColumns("Category")

'RemoveCF (myTbl.ListColumns("Category").DataBodyRange) ' Runtime error 424: Object Required. Why: this is a Range isn't it ?
'RemoveCF (myTbl.ListColumns("Category").Range) ' Runtime error 424: Object Required. Why: this too is a Range isn't it ?
'RemoveCF (myTbl.ListColumns("Category")) 'Compile error, type mismatch: understandable, is not a Range
'RemoveCF (myTbl.ListColumns("Category").Range.Select) ' Runtime error 424: Object Required
'what else?

End Sub

Sub RemoveCF(ByRef mySel As Range)

    Dim myCell As Range
    
    For Each myCell In mySel
        myCell.Interior.Color = myCell.DisplayFormat.Interior.Color
    Next myCell
    
    mySel.FormatConditions.Delete

End Sub

Any help appreciated

Kind regards, Hans Troost
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,728
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
RemoveCF myTbl.ListColumns("Category").DataBodyRange
 
Solution

Hans Troost

New Member
Joined
Jan 6, 2015
Messages
24
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
How about
VBA Code:
RemoveCF myTbl.ListColumns("Category").DataBodyRange
Thank Fluff, this works.

And of course, I should have tried and I don't know why: kind of a road block in my brain?

But besides that: what is the reason this works: is this that - with the parentheses - it is calling a function in stead of a sub what was the intention?

Anyway: I'm very happy with your answer. It leaded me out of my tunnel vision when looking for solutions.

Best regards, Hans
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,728
Office Version
  1. 365
Platform
  1. Windows
You would need the brackets if you "called" the sub like
VBA Code:
Call RemoveCF(myTbl.ListColumns("Category").DataBodyRange)
but not the way you did it.
 

Hans Troost

New Member
Joined
Jan 6, 2015
Messages
24
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks again Fluff, I understand it now and I will never forget: my memory is better than my “blocked” brain. Hans
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,728
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,893
Messages
5,638,869
Members
417,057
Latest member
Jimbo Jones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top