Function or not?

[gayclarke]

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

[Norie]

Gay

Can we see the code?

[gayclarke]

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]

When you call the sub, use Application.Run, like so

[vba]

myVal = Application.Run "book2.xls!test"
[/vba]

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]

Thanks for that but is that the onlt way to do it> How to I pass the arguments to it?

[Norie]

gay

Can you post your actual code?

[gayclarke]

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

[Bob Phillips]

Thanks for that but is that the onlt way to do it> How to I pass the arguments to it?

Think so

[gayclarke]

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]

Why? Why not use an array?

[gayclarke]

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]

What do you mean by losing the data?

[gayclarke]

What do you mean by losing the data?

Sorry I'm probably being quite thick with this - very limited experience of VBA
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]

Here is an example of how to use an array


Book1

[vba]

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[/vBa]

Book2

[vba]

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
[/vba]

[gayclarke]

Many thanks for that. Worked well.

[gayclarke]

Many thanks for that. Worked a treat!