vba validate - headers in input sheet

[Mallesh23]

Hi Team,

I want to check whether all headers are present in Input sheet.
I am using below code, is there any other way. Thanks in advance!


My attempted code

Sub Header_Validation()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets(1)
    
    Dim headers As String
    headers = "Sales,Total,Region,SalesPerson"

    If Header_Check(sh, headers) = False Then
    
        MsgBox "Header not found" & Chr(10) & headers, 16, "Header Missing in " & sh.Name
     
    End If


End Sub


Function Header_Check(ByVal sh As Worksheet, headers As String, Optional hrow As Long = 1) As Boolean

    Dim Header_Array() As String
    Dim i As Integer
    Dim cnt As Integer
        
    Header_Array = Split(headers, ",")
    headers = ""
    cnt = 0
    
    For i = 0 To UBound(Header_Array)
            
            cnt = 0
            
            On Error Resume Next
               cnt = Application.WorksheetFunction.Match(Header_Array(i), sh.Rows(hrow), 0)
            On Error GoTo 0
            
            If cnt = 0 Then
                If headers = "" Then
                    headers = Header_Array(i)
                Else
                    headers = Header_Array(i) & "," & Header_Array(i)
                End If
            End If
    Next i


Header_Check = CBool(Len(headers) = 0)
 
End Function

 

[dmt32]

Hi,
looks like you are trying to adapt last code you posted

try this change to your codes

Sub Header_Validation()
    Dim headers As String
    Dim sh As Worksheet
   
    Set sh = ThisWorkbook.Worksheets(1)
   
    headers = "Sales,Total,Region,SalesPerson"

    If Not Header_Check(sh, headers) Then
   
        MsgBox "Header not found" & Chr(10) & headers, 16, "Header Missing in " & sh.Name
    
    End If


End Sub



Function Header_Check(ByVal sh As Object, ByRef headers As String, Optional ByVal hrow As Long = 1) As Boolean
    Dim headerarray() As String
    Dim i As Integer
    Dim m as Variant
   
    headerarray = Split(headers, ",")
    headers = ""
    For i = 0 To UBound(headerarray)
        m = Application.Match(headerarray(i), sh.Rows(hrow), 0)
        If IsError(m) Then headers = headers & Chr(10) & headerarray(i)
    Next i
       
    Header_Check = CBool(Len(headers) = 0)
       
End Function

Dave

 

[Mallesh23]

Hi Dmt32,

Thanks once again, liked your coding .

Function Header_Check(ByVal sh As Object, ByRef headers As String, Optional ByVal hrow As Long = 1) As Boolean


One question :=> ByRef headers As String ... do we need to provide Byref as arg or byval.


Thanks
mg

 

[dmt32]

You pass arguments to a procedure (sub or function) by reference or by value. By default, Excel VBA passes arguments by reference. In this case, it is not necessary to declare a parameter ByRef I just choose to do so to make it clear this is what I intended.

passing arguments by reference (ByRef) the code is referencing the original value so any changes made to the variable in the called procedure are passed back to the calling procedure.

passing arguments by value (ByVal) is just passing a copy of the data i.e. the underlying element is non modifiable and any changes made will not be reflected in the calling procedure.

How you pass arguments depends if there is a need to protect the variable from being changed (ByVal) or if you need to return any modifications to it back to the calling procedure (ByRef)

There is plenty of reading material online if require further information.

Dave

 

[Mallesh23]

Hi Dave,

Thanks once again for help. ?


Thanks
mg

 

[dmt32]

Hi Dave,

Thanks once again for help. ?


Thanks
mg

welcome

Dave