sloppy code help

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

Matthew Dyer

test = ownershp(midd, site)
Range("a" & i).Value = team
Range("b" & i).Value = owner

so i built a UDF ownershp() that returns two different variables (team and owner) based on two other variables (parent site, child midd). currently, ihave to use the test = line to run the function. variables team and owner are public so that my code can use them in the main sub as well as the udf. how can i clean it up to not need to use the test = line? test does nothing else other than allow me to call the udf and it bugs me... there has to be a better way
 
test = ownershp(midd, site)
Range("a" & i).Value = team
Range("b" & i).Value = owner

so i built a UDF ownershp() that returns two different variables
(team and owner) based on two other variables (parent site, child
midd). currently, i have to use the test = line to run the function.
variables team and owner are public so that my code can use them in
the main sub as well as the udf. how can i clean it up to not need to
use the test = line? test does nothing else other than allow me to
call the udf and it bugs me... there has to be a better way
Click to expand...

A UDF is a custom cell function that returns a result to the cell using
it! Nor can you use a UDF to affect change in other cells, only the
cell using it in a formula.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
A UDF is a custom cell function that returns a result to the cell using
it! Nor can you use a UDF to affect change in other cells, only the
cell using it in a formula.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Click to expand...

you can use a user defined function in vba code as well and call it like you would call worksheet functions. but your statement does kind of clarify something for me - UDF's will only return one answer based on the code it compiles (returns a result to the cell calling the funciton). i think in my case instead of a UDF i should just call a seperate sub to return my two values.
 
you can use a user defined function in vba code as well and call it
like you would call worksheet functions. but your statement does kind
of clarify something for me - UDF's will only return one answer based
on the code it compiles (returns a result to the cell calling the
funciton). i think in my case instead of a UDF i should just call a
seperate sub to return my two values.
Click to expand...

If you use a sub then just call it directly, but pass the variables as
args...

Sub Get_Ownership(Team$, Owner$)
'process to args
Team = sTeam: Owner = sOwner
End Sub

...and in the caller...

Dim sTeam$, sOwner$ '($ = as String)

Get_Ownership(sTeam, sOwner)
Range("a" & i).Value = sTeam
Range("b" & i).Value = sOwner

OR

Since your target cells are contiguous...

Dim vaOwnerInf(1)

Get_Ownership(vaOwnerInf())
Range("a" & i).Resize(1, 2) = vaOwnerInf

Sub Get_Ownership(vArr())
'process to array
vArr(0) = sTeam: vArr(1) = sOwner
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I am still getting used to arrays, and this seems like a good start with a small 1x2 size array. Eventually Id like manipulate a MUCH larger array set(10 column x 250+ rows). Right now I am doing this via loops, but since I'm assigning values to cells in a spreadsheet instead of in memory via arrayim under the impression that there is a much more efficient method of doing this.
 
I am still getting used to arrays, and this seems like a good start
with a small 1x2 size array. Eventually Id like manipulate a MUCH
larger array set (10 column x 250+ rows). Right now I am doing this
via loops, but since I'm assigning values to cells in a spreadsheet
instead of in memory via array im under the impression that there is
a much more efficient method of doing this.
Click to expand...

Yes, direct read/write to ranges can be quite slow when dealing with
numerous cells. Processing in memory using arrays is orders of
magnitude faster. Thus, I default to using arrays unless absolutely
unavoidable!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top