promalley2
New Member
- Joined
- Nov 16, 2009
- Messages
- 24
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>
<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>