CODEWITHSUNDEEP
excelvba
Oversoul
Oversoul

Reputation: 51

VBA Excel - Pass Worksheet and Range as Parameters

Can anyone help me convert a Sub into a function so I could use it to a different form controls? I'm new to VBA excel and I've been trying to convert this for hours with no luck. Appreciate it if someone could help.

Private Sub ComboBox1_Change()
    Dim v, e
    With Sheets("DATABASE").Range("minRange").SpecialCells(2)
        v = .Value
    End With

    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
        If Not .exists(e) Then .Add e, Nothing
        Next
        If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
    End With
End Sub

Thanks.

Upvotes: 0

Views: 805

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

A range variable should have the worksheet already qualified so no need to pass that separately. Just write your sub to accept a range argument:

Sub MySub(ByVal rng As Excel.Range)

    MsgBox rng.Address

End Sub

Upvotes: 1

Related Questions