User-Defined Function Questions

[promalley2]

Good morning Mr. Excel MVPs and Power-Users,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have a couple of general questions about user-defined functions that I’m hoping an expert can answer. What I’m trying to do in general is created a host of modules in VBA that will store sub procedures and functions that allow me to use Excel in an interoperable manner with my CAD program through the CAD software’s VB API. I think I need a quick refresher in the “101”-level basic rules of passing values and objects between sub procedures and functions. I guess I consider the “101”-level basics and rules to be understanding the difference between ByVal and ByRef, which as I understand it: to pass something “ByVal” is to pass a “copy” of the object’s value (rather than the object itself…or is it a copy of the object itself?) whereas passing a something “ByRef” is to pass the memory “address” of the object (which is equates to passing the object itself). My first question is: do I understand the difference between ByVal and ByRef correctly? In the research I did prior to posting, nothing I found really described the difference in terms of “passing an object.” Can someone break it down for me relative to those terms?<o></o>
<o> </o>
I find it hard to believe that one couldn’t pass an object itself into a sub procedure or function, so assuming we’re able to do that, I have to subsequent questions:<o></o>
<o> </o>
1) Is it possible to create a UDF that returns an object rather than a primitive data type (such as a String, Long, Boolean, Double, etc.) and would anyone have a simple example of that exchange that I could see? <o></o>
<o> </o>
I was thinking of something along the lines of the following:<o></o>
Public Function CreateRange() As Range<o></o>
<o> </o>
Dim Cells As Range<o></o>
Set Cells = Range(“A1:A5”)<o></o>
CreateRange = Cells<o></o>
<o> </o>
End Function<o></o>
<o> </o>
2) If it is possible to create a UDF that returns an object, is it possible to return an object from an object model that is selected as a project reference? (Ie. If I have a CAD program VB API reference selected in my VBA project, can I create a function to return an object from the CAD program object model?)<o></o>
<o> </o>
I was wondering if something like the following is possible:<o></o>
Public Function CreateTheCADObject() As CADobjectLibrary.Object<o></o>
<o> </o>
Dim CreateCADobj As New CompactClassThatCreatesCADobject<o></o>
Dim CADobj As CADobjectLibrary.Object<o></o>
<o> </o>
Set CADobj = CreateCADobj.Create <o></o>
‘where .Create is a function in a compact class in the CAD 'software's VB API that creates an object<o></o>
<o> </o>
CreateTheCADObject = CADobj<o></o>
<o> </o>
End Function<o></o>
<o> </o>
I’m newer to the realm of VBA, and becoming a little more experienced, but I if anyone has some insight that will straighten me out and increase my understanding in the above mentioned area I would greatly appreciate your input.<o></o>
<o> </o>
Thanks!<o></o>

 

[promalley2]

This may be a little strange, but as I was waiting for insight, I believe I’ve figured out the answers to my questions. Below are three examples in ascending order of difficulty that I experimented around with to verify the passing of an object. Note that all three examples were written into a module (Module1). The first example is a function returning a primitive, which I knew you could execute. The second example is a function that returns an Excel range object that is defined within the function. The last example is a copy of an example of how to pass an asynchronous connection object from the function where it was created. Keep in mind that the CAD software’s VB API had to be selected as a reference, and I defined a global asynchronous connection variable placeholder for the returned asynchronous connection object.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Example 1: A function that returns a primitive<o></o>
<o> </o>

[FONT=Times New Roman]Public Function CreateInteger() As Long<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Dim Inte As Long<o:p></o:p>[/FONT]
[FONT=Times New Roman]Inte = 5<o:p></o:p>[/FONT]
[FONT=Times New Roman]CreateInteger = Inte<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]'WORKS!!<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]End Function<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Private Sub GetInteger()<o:p></o:p>[/FONT]
[FONT=Times New Roman]‘For testing<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Dim aa As String<o:p></o:p>[/FONT]
[FONT=Times New Roman]Dim a As Long<o:p></o:p>[/FONT]
[FONT=Times New Roman]a = Module1.CreateInteger<o:p></o:p>[/FONT]
[FONT=Times New Roman]aa = MsgBox(a & " is the value of the integer returned from the function.", vbOKOnly)<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]End Sub<o:p></o:p>[/FONT]

<o> </o>
<o></o>
Example 2: A function that returns a range object<o></o>
<o> </o>

[FONT=Times New Roman]Public Function CreateRange() As Range<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Dim R As Range<o:p></o:p>[/FONT]
[FONT=Times New Roman]Set R = Sheet1.Range("A1:A5")<o:p></o:p>[/FONT]
[FONT=Times New Roman]Set CreateRange = R<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]End Function<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Private Sub GetRange()<o:p></o:p>[/FONT]
[FONT=Times New Roman]‘For testing purposes, verify the range object was returned by applying various methods to the object<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Set a = Module1.CreateRange<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]If a Is Nothing Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]    MsgBox "Bummer! There is NO object returned."<o:p></o:p>[/FONT]
[FONT=Times New Roman]    Else<o:p></o:p>[/FONT]
[FONT=Times New Roman]    MsgBox "It's ALIVE!"<o:p></o:p>[/FONT]
[FONT=Times New Roman]    a.Select<o:p></o:p>[/FONT]
[FONT=Times New Roman]    Sheet1.Activate<o:p></o:p>[/FONT]
[FONT=Times New Roman]End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]End Sub<o:p></o:p>[/FONT]

<o> </o>
Example 3: A function that returns an object from a referenced object library<o></o>
<o> </o>

[FONT=Times New Roman]Public Function CreateAsync() As ObjectLibrary.AsyncConnection<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Dim AsyncConn As ObjectLibrary.AsyncConnection<o:p></o:p>[/FONT]
[FONT=Times New Roman]Dim AsyncConnection As New ObjectLibrary.CompactClassThatCreatesAsyncConnection<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Set AsyncConn = AsyncConnection.Connect("", "", "", “”)<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Set CreateAsync = AsyncConn<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]End Function<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Private Sub AsyncConnectionObj()<o:p></o:p>[/FONT]
[FONT=Times New Roman]‘For testing purposes, verify the asynchronous connection object was returned by applying various methods to the object<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Dim bool As Boolean<o:p></o:p>[/FONT]
[FONT=Times New Roman]Set async = Module1.CreateAsync<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]If async Is Nothing Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]    MsgBox "Bummer! There is NO object returned."<o:p></o:p>[/FONT]
[FONT=Times New Roman]    Else<o:p></o:p>[/FONT]
[FONT=Times New Roman]    bool = async.IsRunning<o:p></o:p>[/FONT]
[FONT=Times New Roman]    MsgBox "Houston, we have an object......and it's boolean value is " & bool & "!"""<o:p></o:p>[/FONT]
[FONT=Times New Roman]    async.Disconnect (1)<o:p></o:p>[/FONT]
[FONT=Times New Roman]    Set async = Nothing<o:p></o:p>[/FONT]
[FONT=Times New Roman]    <o:p></o:p>[/FONT]
[FONT=Times New Roman]End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]End Sub<o:p></o:p>[/FONT]

<o> </o>
I hope the above examples can help out someone like myself who is a novice become a little more experienced and gain a little more understanding in the realm of user-defined functions that are able to return primitives, Excel objects, and objects from a referenced object library.<o></o>
<o> </o>
Have a good one all!<o></o>