creating a range variable not tied to a spreadsheet

[electricpete]

Here's the situation.

I want to create a user-defined FUNCTION that manipulates several intermediate variables as complex variables. The excel complex functions (for example improduct, imsum, imexp) work on spreadsheet cells.

If I had a sub (instead of a function), I could just create my range variables, and then use a set statement to tie them to some remote unused corner of my spreadsheet, and then use them to store complex values (intermediate results).

BUT, I am using a function instead of a sub. Functions don't allow you to modify the spreadhseet by storing a value.

I could modify my function to call a subroutine, but then I'd have to pass all the other data long with it. Would certainly work, but it seems like a double-workaround
1 - I am having to store stuff in a spreadsheet cell, which I don't really want to do.
2 - I have having to create a sub and pass all my data to it, which I really want to do.

I could avoid these workaround and develop more straightforward code if I could simply create and manipulate a range (or single cell) variable within the function without tieing it to a spreadsheet. Is there a way to do this?

One solution I am considering is building my own userdefined data type for complex variables and then bulding my own set of primitive functions to manipulate these complex variables for complex multiplication, division etc. It would probably not be tremendously difficult. But I still would like to know if there is a way to use a range variable in a function without tieing to a spreadsheet.

 

[electricpete]

I tried the workaround of calling a sub from a function and it still didn't work. Apparently the restrictions which apply to functions (can't change the worksheet) also apply to subs called from functions.

 

[electricpete]

I got it figured out. I don't need a range. I can use string variables for all complex variables. Those functions like improduct take string arguments in and return string variables.

string1="1+2i"
string2="2+3i"
string3=improduct(string1,string2)
debug.Print(string3)

The above code returns -4+7i

 

[electricpete]

Now a related question. Is there a way to use the fourier function (from analysis toolpak-vba) in vba without tieing the data to a range in a spreadsheet.

I was able to use the function when tied to a spreadsheet as follows:
set range1=sheets("Sheet1").range("a1:a4")
set range2=sheets("Sheet1").range("b1:b4")
call fourier(range1,range2)
With 1 0 0 0 in column A the above returns 1 1 1 1 in column B (as expected).

I tried unsuccessfully to use fourier in vba without tieing it to a worksheet and got errors each time. See failed attempts below:
Sub fouriertest()
Dim range1(4) As String
Dim range2(4) As String
range1(1) = "1"
range1(2) = "0"
range1(3) = "0"
range1(4) = "0"
Call fourier(range1, range2)
Stop
' Gives error: Input range must be a continuous reference
End Sub

Sub fouriertest2()
Dim range1 As String
Dim range2 As String
range1 = ["1+0i" "0+0i"]
Call fourier(range1, range2)
Stop
' Gives type mismatch
End Sub

Sub fouriertest3()
Dim range1 As Range
Dim range2 As Range
range1(1) = 1
range1(2) = 0
Call fourier(range1, range2)
Stop
' Gives error - object variable not set
End Sub

Sub fouriertest4()
Dim range1(2) As Variant
Dim range2(2) As Variant
range1(1) = 1
range1(2) = 0
Call fourier(range1, range2)
Stop
' Gives error - input range must be a continuous reference
End Sub

 

[Bong]

I think you have to use a (temporary) worksheet. It looks like the VBA analysis toolpak does not have a Fourier function, only the Excel analysis tookpak. So you could add a worksheet, put up your input data, perform the worksheet function, capture your FK data, and delete the worksheet?

_________________
Bob Rashkin

 

[combo]

ATP's VBA Functions and Subs module does contain Fourier and FourierQ, but they are subs with input range and output range as procedure arguments.

combo

 

[electricpete]

Yes, I agree with the comments by combo. These are functions that are available from within vba when the analysis tookpack / vba is loaded and the file atbvbaen.xls is specified as a reference.

I was able to make it work from the vba immediate window as described at the beginning of my message 10 Feb 08 12:33, but only with ranges tied to a spreadsheet.

I guess there is no other way (?)

 

[combo]

You could follow Bob's suggestion. Add workbook, copy input data, perform transformation and copy back again. With 2D variant array you can quickly copy data between vba array and range.
The Fourier procedure uses ranges as it directly passes data to and from them. So it is not posible use other type variable.

combo