In VBA I'd rather prefer to use collections or dictionaries instead arrays. Especially when it comes to manipulate data in classes. But for spilling data into range that seems nice. Thank you!
Hi ! can we split the type of an array into the column/rows ? For a start, when we declare an array, the *hole* array is of the type you declared it to be. Can we declare a specific row/column to be of a specific type different one from the other ?
Hello Sir, I just watched this video. I learn a new thing from your video, that through VBA Array, we can create our own Dynamic Array Function in Excel. But, will you please give some real world dynamic examples. It'll make our concept much more clear on this. How in our day to day life we should use it? After trying a lot, I finally became successful to create a single UDF that can split names apart. This is not a Spilled Array Function. But, still with this one single function, one can split names (Irrespective of someone having Middle Name or not). I'm sharing my code below: ------------------------------- Function SplitNameViaUDF(InputRange As Range, Criteria As Long) As String If InputRange.Value = "" Then SplitNameViaUDF = vbNullString Exit Function Else Dim NameArray As Variant NameArray = Strings.Split(Expression:=InputRange.Value, Delimiter:=" ") If UBound(NameArray) - LBound(NameArray) = 2 Then If Criteria = 1 Then SplitNameViaUDF = NameArray(0) ElseIf Criteria = 2 Then SplitNameViaUDF = NameArray(1) ElseIf Criteria = 3 Then SplitNameViaUDF = NameArray(2) Else SplitNameViaUDF = vbNullString End If ElseIf UBound(NameArray) - LBound(NameArray) = 1 Then If Criteria = 1 Then SplitNameViaUDF = NameArray(0) ElseIf Criteria = 2 Then SplitNameViaUDF = vbNullString ElseIf Criteria = 3 Then SplitNameViaUDF = NameArray(1) Else SplitNameViaUDF = vbNullString End If Else If Criteria = 1 Then SplitNameViaUDF = NameArray Else SplitNameViaUDF = vbNullString End If End If End If End Function
