Issue passing two parameters from sub to function to get back result

[BradleyS]

I'm trying to pass two parameters (sr and colPos) from a sub to a function and get back the result (Names).
The function checks for a value in column H and if found gets the name in column B of the adjacent row
The Names variable will have a list of ; separate names

However, I keep getting a compile error "Expected array" in the sub with regards to "Names"
What am I doing wrong? How do I accomplish this?

Option Explicit
Sub Macro1()

    Dim sr As String, colPos As Integer, Names As String, sn As String

        sr = "H7:H"
        colPos = 6
        
        sn = Names(sr, colPos)


End Sub

Function Names(ByVal sr As String, ByVal colPos As Integer) As String
        
        Dim output
        Dim entry As Variant
        Dim Lr As Long
    
        Lr = ThisWorkbook.ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
            On Error Resume Next    'if only 1 row
    
        For Each entry In ThisWorkbook.ActiveSheet.Range(sr & Lr)
            If Not IsEmpty(entry.Value) Then
                output = output & entry.Offset(, -colPos).Value & ";"
            End If
        Next
        
        Names = Left(output, Len(output) - 1)

End Function

 

[HighAndWilder]

I'm trying to pass two parameters (sr and colPos) from a sub to a function and get back the result (Names).
The function checks for a value in column H and if found gets the name in column B of the adjacent row
The Names variable will have a list of ; separate names

However, I keep getting a compile error "Expected array" in the sub with regards to "Names"
What am I doing wrong? How do I accomplish this?

Option Explicit
Sub Macro1()

    Dim sr As String, colPos As Integer, Names As String, sn As String

        sr = "H7:H"
        colPos = 6
       
        sn = Names(sr, colPos)


End Sub

Function Names(ByVal sr As String, ByVal colPos As Integer) As String
       
        Dim output
        Dim entry As Variant
        Dim Lr As Long
   
        Lr = ThisWorkbook.ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
            On Error Resume Next    'if only 1 row
   
        For Each entry In ThisWorkbook.ActiveSheet.Range(sr & Lr)
            If Not IsEmpty(entry.Value) Then
                output = output & entry.Offset(, -colPos).Value & ";"
            End If
        Next
       
        Names = Left(output, Len(output) - 1)

End Function

Change the function name from 'Names' which is reserved.

 

[BradleyS]

Change the function name from 'Names' which is reserved.

I have changed it to GetNames and it still shows the same error

 

[HighAndWilder]

I have changed it to GetNames and it still shows the same error

In all three places?

Option Explicit
Sub Macro1()

    Dim sr As String, colPos As Integer, Names As String, sn As String

        sr = "H7:H"
        colPos = 6
        
        sn = GetNames(sr, colPos)

End Sub

Function GetNames(ByVal sr As String, ByVal colPos As Integer) As String
        
        Dim output
        Dim entry As Variant
        Dim Lr As Long
    
        Lr = ThisWorkbook.ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
            On Error Resume Next    'if only 1 row
    
        For Each entry In ThisWorkbook.ActiveSheet.Range(sr & Lr)
            If Not IsEmpty(entry.Value) Then
                output = output & entry.Offset(, -colPos).Value & ";"
            End If
        Next
        
        GetNames = Left(output, Len(output) - 1)

End Function

 

[BradleyS]

In all three places?

Option Explicit
Sub Macro1()

    Dim sr As String, colPos As Integer, Names As String, sn As String

        sr = "H7:H"
        colPos = 6
       
        sn = GetNames(sr, colPos)

End Sub

Function GetNames(ByVal sr As String, ByVal colPos As Integer) As String
       
        Dim output
        Dim entry As Variant
        Dim Lr As Long
   
        Lr = ThisWorkbook.ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
            On Error Resume Next    'if only 1 row
   
        For Each entry In ThisWorkbook.ActiveSheet.Range(sr & Lr)
            If Not IsEmpty(entry.Value) Then
                output = output & entry.Offset(, -colPos).Value & ";"
            End If
        Next
       
        GetNames = Left(output, Len(output) - 1)

End Function

Ok this works and I can see that the only thing you have changed is remove(ignores) the dim Names as string reference in the sub.
It works so I'm happy thank you

 

[RoryA]

Change the function name from 'Names' which is reserved

It isn't a reserved word. The only issue was the Dim Names as String which conflicted with the function name.