PDA

View Full Version : Function or not?



gayclarke
07-01-2007, 06:57 AM
Hi

Can anyone help me with some basics please?

I have a subroutine which I use in several workbooks to open a url in ie in order to analyse the page.

I decided to move it into one place and call it from the various workbooks to save duplicating code.

However, the data doesn't get passed back any more.

I'm not sure whether I need to make it a function or if there is a way of returning the arguments eg

call xxx(a,b) where b will change.

Thanks, Gay:banghead:

Norie
07-01-2007, 07:15 AM
Gay

Can we see the code?

gayclarke
07-01-2007, 07:51 AM
I've messed around with it that much I'm not sure where I'm up to.

Basically I want to do this sort of thing

In workbook1
Sub multiply(a, b, c)
a = b * c
End Sub

In workbook2,3,4 etc. I want to call "multiply" passing b&c
and have it return a (and maybe b&c).

That's just an example. What I'm actually doing is using the subroutine to extract data from a page in explorer open_in_ie(url, dta, frm) where dta and frm are blank to begin with and come back with the page data and number of frames if successfully opened.

When they are in the same workbook, dta goes into the sub blank and comes our with page data if found. With the sub in another workbook, nothing comes back.

I'm not familiar with how to call an external sub properly or even if it should be a sub or function.


Gay

Can we see the code?

Bob Phillips
07-01-2007, 08:57 AM
When you call the sub, use Application.Run, like so



myVal = Application.Run "book2.xls!test"


The thing is that this only returns a single value. You can't return byRef when using APplication.Run, so if you need multiple values, pass an array and return an array.

gayclarke
07-01-2007, 09:43 AM
Thanks for that but is that the onlt way to do it> How to I pass the arguments to it?

Norie
07-01-2007, 09:57 AM
gay

Can you post your actual code?

gayclarke
07-01-2007, 10:17 AM
Ok this is my test routine which I now have working as is. (The total code would be a bit impractical and unweildy to discuss.)

My question is, imagine the scenario where I want to zeroise b and c after completing the calculation and pass those parameters back also, is there any way it can be done?

Sub testit() (In workbook "Linkchecker.xls")
MyResults = Application.Run("FileList.xls!testit2", 2, 3)
MsgBox MyResults
End Sub

Function testit2(b, c)
testit2 = b * c
End Function

Thanks, Gay:banghead:

Bob Phillips
07-01-2007, 10:37 AM
Thanks for that but is that the onlt way to do it> How to I pass the arguments to it?

Think so

gayclarke
07-02-2007, 02:40 AM
OK I have it working after a fashion.

I have sanwidwiched the two variable together as text and split them again after it's passed back.

Seems crazy but if it's the only way....... :(

Thanks for your help.

Bob Phillips
07-02-2007, 03:01 AM
Why? Why not use an array?

gayclarke
07-02-2007, 03:27 AM
Why? Why not use an array? I'm not very experienced with this. In what way should I use an array and how would this help? Won't I still have the problem of it losing the data between one workbook and the next?

Bob Phillips
07-02-2007, 04:26 AM
What do you mean by losing the data?

gayclarke
07-02-2007, 04:47 AM
What do you mean by losing the data?
Sorry I'm probably being quite thick with this - very limited experience of VBA :blush
In my limited experience so far, when I have variables/arguments in one workbook which are required via a call from a second workbook, those variables are not passed across. In the past I have duplicated the subroutine in every workbook which seems silly.

What I am try to achieve is a routine in workbook A which extracts 2 variables containing the data from a web page and the number of frames. This routine is then called from workbooks B C D etc. passing those 2 vaiables back.

Am I missing the obvious?

Bob Phillips
07-02-2007, 01:13 PM
Here is an example of how to use an array


Book1



Public Sub TestMultipleReturns()
Dim ary
Dim i As Long

ary = Array(1, 2, 3, 4)
For i = 0 To 3
Debug.Print ary(i)
Next i

ary = Application.Run("Book2!ReturnMultipleValues", ary)
For i = 0 To 3
Debug.Print ary(i)
Next i

End Sub

Book2



Public Function ReturnMultipleValues(inArray)
Dim i As Long

For i = LBound(inArray) To UBound(inArray)
inArray(i) = inArray(i) * (i + 1)
Next i

ReturnMultipleValues = inArray

End Function

gayclarke
07-16-2007, 01:08 PM
Many thanks for that. Worked well.

gayclarke
07-16-2007, 01:08 PM
Many thanks for that. Worked a treat!