×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

creating a range variable not tied to a spreadsheet

thread766-209313

creating a range variable not tied to a spreadsheet

creating a range variable not tied to a spreadsheet

(OP)
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.

Replies continue below

Recommended for you

RE: creating a range variable not tied to a spreadsheet

(OP)
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.

RE: creating a range variable not tied to a spreadsheet

(OP)
I got it figured out.  The complex variables are just string variables.  Those functions like improduct take string arguments in and return string variables.

CODE

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.

RE: creating a range variable not tied to a spreadsheet

(OP)
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.

RE: creating a range variable not tied to a spreadsheet

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
www.interactiveds.com.au

RE: creating a range variable not tied to a spreadsheet

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

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.

RE: creating a range variable not tied to a spreadsheet

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
www.interactiveds.com.au

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


News
Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close