Dim Cell. no "as"

[lezawang]

Hi
I would appreciate if you can help me understand the code below
1) What does Dim Cell mean? why "as" was omitted
2) What does "&gt" do here?
3) Is these easier implementation of similar code? Thank you.

Sub Highlight_Duplicates(Values As Range)
Dim Cell
 
For Each Cell In Values
    If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
        Cell.Interior.ColorIndex = 6
    End If
 
Next Cell
End Sub

https://www.automateexcel.com/vba/highlight-duplicates-in-range/

 

[Marcelo Branco]

Dim Cell (type omitted) is equivalent to Dim Cell as Variant

& gt ; (without spaces) is the HTML equivalent to > , that is greater than

M.

 

[lezawang]

Sorry another question. How to call that sub?
I create a button in my excel sheet and tried to "Assign" to macro but the macro was not in the list!
Another thing, I just noticed that this macro has an argument!
Thank you.

 

[shg]

Alt+F8 to show the Macros dialog.

You know you can do this with conditional formatting?

 

[RoryA]

You can't pass a Range object using a macro assigned to a button. You'd need to change the argument to say a String and then pass the address of the range.

 

[shg]

Doh ...

 

[lezawang]

Thanks all for your help. I inserted button. I changed the argument to String but then when I right clicked on the button to assign macro, I do not see any macro. Then I removed the argument from the macro and now I can see the macro.
My question, how can I pass an argument using button. if not possible, then how can I call that macro. Thanks alot.

 

[Rick Rothstein]

Thanks all for your help. I inserted button. I changed the argument to String but then when I right clicked on the button to assign macro, I do not see any macro. Then I removed the argument from the macro and now I can see the macro.
My question, how can I pass an argument using button. if not possible, then how can I call that macro. Thanks alot.

You cannot pass an argument into a macro... the lack of an argument is what makes it a macro, otherwise you have a normal subroutine which can only be called from other VB code.

Originally, how exactly were you planning to pass an argument into the code from a button push???

 

[Marcelo Branco]

You can select the range and assign this macro to the button

Sub Highlight_Duplicates()
    Dim Cell
 
    For Each Cell In Selection
        If WorksheetFunction.CountIf(Selection, Cell.Value) > 1 Then
            Cell.Interior.ColorIndex = 6
        End If
     
    Next Cell
End Sub

M.

 

[Joe4]

You cannot pass an argument into a macro... the lack of an argument is what makes it a macro, otherwise you have a normal subroutine which can only be called from other VB code.

Of course, you need to be careful of nomenclature here - people often use "Macro" and "VBA" interchangeably. In VBA, you can have "Procedures" and "Functions".
You can pass arguments to both Procedures and Functions, but maybe not in all instances.

I am not sure how one would pass an argument to a Procedure that is called by a button, if that is possible at all.
Where would the value come from?

I think the following suggests that you cannot call any procedure that has an input argument with a button:

I changed the argument to String but then when I right clicked on the button to assign macro, I do not see any macro. Then I removed the argument from the macro and now I can see the macro.