CODEWITHSUNDEEP
excelparameter-passingvba
Access_Query
Access_Query

Reputation: 99

Object required - Passing variable to Function

I'm taking first stab at Excel VBA having learned some VBA from an Access project. I am trying to find the first and second visible cells on a dataset that is filtered. My current method is to find the first visible cell via a sub function, and then use that first cell reference to find the next one. I get the Object required error when I compile on Vis1cell =

Here's the code:

Sub volvar()
'
' Volume Var Sort Macro
'
Dim rng As Range
Dim filterDept As Range

Dim volrng As String
Dim Vis1 As Range
Dim Vis1cell As String
Dim Vis2 As Range
Dim Vis2cell As String

Set rng = Range("$A$12:$BF$45000")
Set filterDept = Range("D8")

'volrng is static and just needs to be passed to the function.
volrng = "J12"
With ThisWorkbook.Sheets("Comparison")
    Set Vis1cell = VolVis(volrng)
    Set Vis1 = Range(Vis1cell)
    Set Vis2cell = VolVis(Vis1cell)
    Set Vis2 = Range(Vis2cell)

    Debug.Print "Vis1"; Vis1
    Debug.Print "Vis2"; Vis2
End With

    'if FilterCheck = 1, sort based on dept and then order
    If FilterCheck = 1 Then
        If Vis1 > Vis2 Then
            Debug.Print "FilterCheck 1 Asc"
            With ThisWorkbook.Sheets("Comparison")
                .AutoFilterMode = False
                rng.AutoFilter Field:=1, Criteria1:=filterDept.Value
            End With
            GoTo FilterAsc
        Else
            Debug.Print "FilterCheck 1 Desc"
            With ThisWorkbook.Sheets("Comparison")
                .AutoFilterMode = False
                rng.AutoFilter Field:=1, Criteria1:=filterDept.Value
            End With
            GoTo FilterDesc
        End If
    Else
        If Range("J13") > Range("J14") Then
            Debug.Print "FilterCheck 0 Asc"
            GoTo FilterAsc
        Else
            Debug.Print "FilterCheck 0 Desc"
            GoTo FilterDesc
        End If
    End If

FilterAsc:
    Debug.Print "Asc Goto"
    With ThisWorkbook.Sheets("Comparison")
        rng.AutoFilter Field:=10
        rng.CurrentRegion.Sort Key1:=.Range("J12"), Order1:=xlAscending, _
            Header:=xlYes, DataOption1:=xlSortNormal
    End With
    Exit Sub

FilterDesc:
    Debug.Print "Desc Goto"
    With ThisWorkbook.Sheets("Comparison")
        rng.AutoFilter Field:=10
        rng.CurrentRegion.Sort Key1:=.Range("J12"), Order1:=xlDescending, _
            Header:=xlYes, DataOption1:=xlSortNormal
    End With
    Exit Sub

End Sub

And here is the function I'm trying to pass to:

Function VolVis(rng As String) As String

    ActiveSheet.Range(rng).Select
    'Set rng = Range("J12")
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop

    Set VolVis = ActiveCell.Address

    Debug.Print "Cell Address"; VolVis

End Function

Upvotes: 2

Views: 1292

Answers (1)

Soulfire
Soulfire

Reputation: 4296

The issue is using the keyword Set. Set is used for setting an object reference, instead of simply assigning a value.

You correctly assign a string here:

volrng = "J12"

Vis1cell is declared as a string, but you are trying to reference an object.

The line:

Set Vis1cell = VolVis(volrng)

Should simply be:

Vis1cell = VolVis(volrng)

Now, a Range is an object that needs to be set. So the line:

Set Vis1 = Range(Vis1cell)

is correct.

You also reference the Range object often and you look to be trying to compare two values. To access the value of a range, you must use Range.Value.

For example, if you are trying to compare the two values in J13 and J14, this will not work:

Range("J13") > Range("J14")

You haven't specified what about the ranges you want to compare. Since you are using the > operator, I'll assume the values. To compare values, you must do something like:

Range("J13").Value > Range("J14").Value

I've made changes where appropriate in my code and added my initials in comment 'JR to help you see how it should look.

Sub volvar()
'
' Volume Var Sort Macro
'
Dim rng As Range
Dim filterDept As Range

Dim volrng As String
Dim Vis1 As Range
Dim Vis1cell As String
Dim Vis2 As Range
Dim Vis2cell As String

Set rng = Range("$A$12:$BF$45000")
Set filterDept = Range("D8")

'volrng is static and just needs to be passed to the function.
volrng = "J12"
With ThisWorkbook.Sheets("Comparison")
    Vis1cell = VolVis(volrng) 'JR
    Set Vis1 = Range(Vis1cell)
    Vis2cell = VolVis(Vis1cell) 'JR
    Set Vis2 = Range(Vis2cell)

    Debug.Print "Vis1"; Vis1.Address 'JR
    Debug.Print "Vis2"; Vis2.Address 'JR
End With

    'if FilterCheck = 1, sort based on dept and then order
    If FilterCheck = 1 Then
        If Vis1.Value > Vis2.Value Then 'JR
            Debug.Print "FilterCheck 1 Asc"
            With ThisWorkbook.Sheets("Comparison")
                .AutoFilterMode = False
                rng.AutoFilter Field:=1, Criteria1:=filterDept.Value
            End With
            GoTo FilterAsc
        Else
            Debug.Print "FilterCheck 1 Desc"
            With ThisWorkbook.Sheets("Comparison")
                .AutoFilterMode = False
                rng.AutoFilter Field:=1, Criteria1:=filterDept.Value
            End With
            GoTo FilterDesc
        End If
    Else
        If Range("J13").Value > Range("J14").Value Then 'JR
            Debug.Print "FilterCheck 0 Asc"
            GoTo FilterAsc
        Else
            Debug.Print "FilterCheck 0 Desc"
            GoTo FilterDesc
        End If
    End If

FilterAsc:
    Debug.Print "Asc Goto"
    With ThisWorkbook.Sheets("Comparison")
        rng.AutoFilter Field:=10
        rng.CurrentRegion.Sort Key1:=.Range("J12"), Order1:=xlAscending, _
            Header:=xlYes, DataOption1:=xlSortNormal
    End With
    Exit Sub

FilterDesc:
    Debug.Print "Desc Goto"
    With ThisWorkbook.Sheets("Comparison")
        rng.AutoFilter Field:=10
        rng.CurrentRegion.Sort Key1:=.Range("J12"), Order1:=xlDescending, _
            Header:=xlYes, DataOption1:=xlSortNormal
    End With
    Exit Sub

End Sub

And for your Function, you are returning a string, so the Set keyword is incorrect syntax. It should look like:

Function VolVis(rng As String) As String

    ActiveSheet.Range(rng).Select
    'Set rng = Range("J12")
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop

    VolVis = ActiveCell.Address

    Debug.Print "Cell Address"; VolVis

End Function

You can read more about the appropriate use of the Set keyword here. Or you can follow a YouTube video tutorial on Dim vs Set and their appropriate uses here.

Upvotes: 3

Related Questions