Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel function that returns a reference to an object

Posted on 2003-11-03
12
Medium Priority
?
179 Views
Last Modified: 2010-05-18
I want to pass an object to a function and have the function return a reference to the object.  I'm new at vb, can this be done?

Type dt
   name As String
   value As Integer
End Type

Sub test()
Dim v As dt, temp As dt
Set v = New dt
v.name = "pear"
set temp = t(v) 'make temp and v point to the same object
temp.name = "orange" ' this should change v.name too
Range("a1").value = v.name 'make sure it worked

End Sub

Function t(ByRef a As dt) As dt
set t = a ' assign the location in memory of a to t
End Function
0
Comment
Question by:relsloan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9674006
That code really works?
I think you cannot use new to instantiante a type (it is not an object, just a structure of data)
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9674012
Maybe if you explain a bit in simply words what you need.
What kind of object must returns and what kind of object you will pass...
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9674019
To this point:


Type dt
   name As String
   value As Integer
End Type

Sub test()
Dim v As dt, temp As dt
v.name = "pear"
...
....
...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:relsloan
ID: 9674247
My code does not work as I'm new to vb.  I want to pass any object (reading the help, I don't think I can pass a string and return a pointer to a string - can I?).  I thought that declaring a new type was the same as declaring a new type of object.  You can disregard the dt type and make a new object that you can pass to the function and have the function return a pointer to that object.  Just make sure the object contains a string field, and use the pointer that returns from the function to access the object to change the string.  Then print the new string using the old object that you passed to the function.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 9675297
You can pass a variable by reference in which case it is in effect a pointer.

Sub test()
Dim strVal1 as string

strVal1 = "test1"
test2 strVal1

MsgBox strVal1

End Sub

Function test2(ByRef strVal as String) as Boolean
strVal = "test2"
test2 = True
End Funciton

Teh result of the previous code will be a message box with strVal1 = to "test2"
0
 

Author Comment

by:relsloan
ID: 9676131
I want to be able to have two variables in sub that refer to the same object (call them a and b).  a should be created in the sub and b should be set to point to object a in the function.  When I return from the function(in the sub), I want to be able to assign something to b, which should, in affect assign the same thing to a since they are both the same object.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9677958
if i understood:


dim a as object, c as object

set a = MakeSame(c)

function MakeSame(x as object) as object
dim b as object
'example?
dim xl as excel.application

set b = new excel.application
set MakeSame = b
end function

Why do you need this anyway?
0
 

Author Comment

by:relsloan
ID: 9679732
I want to pass an array to a function and have the function return an element.  Then I want to be able to do stuff to that element (so that the array is also updated with those changes), but not know which memeber of the array I am changing.

I figured out that you can only use set to set an object so I made a new class module- dt:
Dim val As String

Property Get value() As String
   value = val
End Property

Property Let value(v As String)
   val = v
End Property

Sub test()
Dim d As dt, temp As dt
Set d = New dt
d.value = "apple"
Set temp = t(d)
temp.value = "pear"
Range("a1").value = temp.value
End Sub

Function t(ByRef a As dt) As dt
Set t = a
End Function
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9680090
if you will go to work with arrays of elements, i suggest that instead of an array use a collection class.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10191353
PAQed, with points refunded (50)

Computer101
E-E Admin
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question