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.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[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.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

I got it figured out. The complex variables are just string 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


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Now a related question. Is there a way to use the fourier function 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 String
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

Any suggestions on how to access fourier from vba without tieing the variables to a spreadsheet range?
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 String
range1(1) = 1
range1(2) = 0
Call fourier(range1, range2)
Stop
' Gives error - object variable not set
End Sub


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

I can't find any reference to a fourier function in VBA help, perhaps it is an add-in, but if it works when two ranges are specified as the parameters then it should work if you specify your arrays differently. Try:

Dim MyArray1(1 to 1, 1 to 4) as string
Dim MyArray2((1 to 1, 1 to 4) as string

MyArray1(1,1) = 1
MyArray1(1,2) = 0
MyArray1(1,3) = 0
MyArray1(1,4) = 0

call fourier(MyArray1, MyArray2)

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au

 

[electricpete]

Thanks. Fourier is available if you add the vba analysis tookpak and identify atpvbaen.xls as a reference. I tried your code:

Sub sub1()
Dim MyArray1(1 To 1, 1 To 4) As String
Dim MyArray2(1 To 1, 1 To 4) As String
MyArray1(1, 1) = 1
MyArray1(1, 2) = 0
MyArray1(1, 3) = 0
MyArray1(1, 4) = 0
Call fourier(MyArray1, MyArray2)
Stop
End Sub

The result is an error message "Fourier Analysis - input range must be a contiguous reference"

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IDS]

I finally found how to access the fourier command in Excel 2007, and recorded a macro, which other than using application.run rather than call, looked much like yours.

As far as I can tell the fourier command will only accept range parameters, which seems strange because most VBA functions are happy to accept an array.

I have a few posts about passing data to/from a spreadsheet range on my blog, which may be of interest, even if it isn't applicable to the fourier function.

http://newtonexcelbach.wordpress.com/2008/03/04/ranges-and-arrays/

http://newtonexcelbach.wordpress.com/2008/03/05/ranges-and-arrays-2/

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au