here, the validation_check function returns an error of invalid property assignment (code 450). when I don't use the set keyword, it returns the error from validate_check = finalCollection, and when I do use the set keyword, it highlights cellArray = validation_check(cellArray, C_VAL_LIST_KW) ie from where I call the function. Here is the code
The sub where i call the function
Sub validate_list()
Call declare_vars
validationRange.Select
Dim rng As Range
Dim colStartCell As Range
Dim colEndCell As Range
Dim cNum As Integer
Dim cellArray
Dim n As Integer
Dim msgboxString As String
n = 0
For Each cell In Application.Selection
If 0 < InStr(UCase(cell), UCase(C_VAL_LIST_KW)) Then
cNum = cell.Column
Set colStartCell = Cells(firstDataRangeRow, cNum)
'MsThgBox colStartCell.Address
Set colEndCell = Cells(dataLastRowNum, cNum)
'MsgBox colEndCell.Address
Set rng = Range(colStartCell, colEndCell)
cellArray = split_array(cell)
cellArray = validation_check(cellArray, C_VAL_LIST_KW)
...
...
End Sub
The Function of split array returns an array, so the problem doesn't lie there
validate check function:
Option Explicit
Public Function validation_check(arr, metaTag) As Collection
Dim newCollection As New Collection
Dim finalCollection As New Collection
Dim arraySize As Integer
Dim i As Integer
Dim val_index As Integer
arraySize = UBound(arr) - LBound(arr) + 1
For i = 0 To arraySize - 1
If arr(i) = metaTag Then
val_index = i
Exit For
End If
Next
For i = val_index To arraySize - 1
newCollection.Add (arr(i))
Next
For i = 1 To newCollection.Count
If InStr(1, newCollection(i), "#") <> 1 Then
finalCollection.Add (newCollection(i))
End If
Next
Set validation_check = finalCollection
End Function
Split array function
Public Function split_array(cell)
Dim arr1, arraySize
arr1 = Split(cell.Value, ",")
arraySize = UBound(arr1) - LBound(arr1) + 1
For i = 0 To arraySize - 1
arr1(i) = Trim(arr1(i))
Next i
split_array = arr1
End Function
