UDF needs to update cells.

[keepitcool]

I've created an UDF to retrieve an array from an external app.

My users must be able to use the UDF's results as the source for
a DataValidation object with Dropdown list.

AFAIK Data Validation list source will only accept a "flat" delimited
string or a range object.
If the range is external a Name object is used as a wrapper for the range.


So : I'm forced to create an UDF that returns a range on a worksheet
within my addin called wsCache.

However if the lists needs to be refreshed:
I can run external application to retrieve a new array..
If the function is called from within the VBE all is fine and the cells in
wsCache can be rewritten.

But if the function is called from a cell e.g. vartype(application.caller)
= "Range" then my function exits when it tries to write to wsCache.

Any ideas..solutions..suggestions?

keepITcool
amsterdam
mailROT13

 

[keepitcool]

i need an answer...

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dave Peterson]

How are you trying to return the array to the worksheet?

UDF's can only update the cells that their in--they can't change other cells.

Maybe you could have your UDF return an array and you could use a multicelled
array formula for your UDF.

If you make the range large enough to hold the largest number of elements in
your array (ever!), you could fill in the unused cells with some kind of error.

Say I wanted my results returned in A1:A10,

I could use this udf:

Option Explicit
Function testme() As Variant

Dim iCtr As Long
'say 10 rows is enough
Dim myArr(1 To 10) As String

For iCtr = 1 To 7
myArr(iCtr) = "test" & iCtr
Next iCtr

testme = Application.Transpose(myArr)

End Function

Select A1:A10 and ctrl-shift-enter =testme() into those cells. But rows 8:10
are blank. So I could use this name as my data validation list.

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A1:$A10)-COUNTIF(Sheet1!$A$1:$A$10,""))

(Will it work when it's retrieving from another program? I didn't test.)

Well, I tried this and it worked ok:

Option Explicit
Function testme() As Variant

Dim myArr(1 To 10) As String
Dim myLine As String
Dim myFileName As String
Dim FileNum As Long
Dim iCtr As Long

myFileName = "C:\my documents\excel\test.txt"
FileNum = FreeFile

Close FileNum
Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
iCtr = iCtr + 1
Line Input #FileNum, myLine
myArr(iCtr) = myLine
Loop
Close FileNum

testme = Application.Transpose(myArr)

End Function

And it worked ok.

 

[keepitcool]

Thanks for the elaborate reply.
The array wasn't the problem. I know how to do that.

If you'd read my question properly:

I explained that I can't (simply) use an array, as I need to set a
DataValidation.

I absolutely need a range else the DV will not accept it...
DV will accept:
john,peter
a1:a100
MyNamedRange

if the named range doesnot evaluate to a (singlerow/col) range...
it will not work...arghhh


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dave Peterson]

I guess I was confused about this line:

But if the function is called from a cell e.g. vartype(application.caller)
= "Range" then my function exits when it tries to write to wsCache.

My function returned the array back to a 10 row by 1 column range on a worksheet
ok.

And then I used that dynamic range name in the data validation.

Did I miss something more?

 

[keepitcool]

I want to keep it simple for the user.
He should not have to work with arrays on hidden worksheets
(the list can be 10000 records long).. he should just set the
datavalidation to sometinh simple. That's the whole idea of my addin.

My addin should handle the (pre) processing e.g. convert the array to a
range...

I guess you dont know me... I'm a fairly gifted excel programmer.
I've tried the obvious..believe me.

I just wanted a 'loophole' to force the udf to trigger a sub.
or the DataValidation to accept an array.

I will probably do something via a classmodule's event.
to kickstart the range update when the array changes.

Thanks for your time.




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Dave Peterson]

You have a data validation list that can be 10,000 entries?