How to pass a User defined type as a function parameter

[Kmanbozzy]

I use VBA all the time but have never tried to use Type. I haven't quite figured it out. Not sure how to pass the parameter into a function. Basically i have a function that i want to call to find a value in a range based on a condition. The condition is a type and based on the type i will find the value where the condition is met. maybe there is a better way to do it? I haven't been able to find anything similar to what I'm doing. Maybe i over looked a thread.

Type ForEachCType
    OffsetR As Integer
    OffsetC As Integer
    EqualTo As Boolean
    GreaterThan As Boolean
    GTorEqualto As Boolean
    LessThan As Boolean
    LTorEqualto As Boolean
End Type

Sub test()
a = ConditionalFind("Jon", Range("BJ:BW"), ForEachCType.OffsetR, "G")
End Sub

 'ForEachCType.OffsetR this is where i don't know how to call or assign this parameter a value
    
Function ConditionalFind(SearchValue, SearchRange As Range, ForEachConditionType As ForEachCType, ForEachConditionValue)
   'code stuffs yay
End Function

 

[Domenic]

To use your user-defined data type, first declare a variable to hold the data type...

Dim x As ForEachCType

Then assign properties for the object...

x.OffsetR = 10
x.OffsetC = 12
'etc
'
'

Then pass the object as follows...

a = ConditionalFind("Jon", Range("BJ:BW"), x, "G")

Then, in your called function, you can access the desired property like this...

Debug.Print ForEachConditionType.OffsetR

Hope this helps!

 

[Kmanbozzy]

awesome! so this is great for calling a sub, then the function. so if i wanted to use this a worksheet function in a cell like =conditionalfind(), id probably have to call a function to dim my variables then call the actual function? since i cant really dim from a cell