• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Returning multiple values on vba with a function

I have a function, which is a web query. It gets the table from internet and pastes it on Cell E111.
The information I need is on Cells F111 and F114.

so after the webquery I tried something like this and didnt work
myfunction = b(Sheets("LiveSheet").Range("F111").Value, Sheets("LiveSheet").Range("F114").Value)

I need to put the values, i return from the function into two different cells on excel.

Also the function is used in a vba subroutine.
0
awesomejohn19
Asked:
awesomejohn19
1 Solution
 
slycoderCommented:
Break your function into two explicit parts and try something like:

Range("A1").Value = myfunction1()
Range("B1").Value = myfunction2()
0
 
GeoffHarperCommented:
Return an array from your function.

I *think* something like this might work in VBA...
Function DoIt(parm As Long) As Variant
    Doit = Array(parm,9,15)
End Function

Dim x as Variant

x = Doit(4)

Msgbox x(0)
Msgbox x(1)
Msgbox x(2)

Open in new window

0
 
AgeOfEmpiresCommented:
Another option, if for some reason you are dealing with two different datatypes is to use a user defined type.

Type A
   val1 as Integer
   val2 as String
End Type

Sub test()
   Dim y as A

   y = GetVal
   Debug.Print y.val1
   Debug.Print y.val2

End Sub

Function GetVal() as A
   GetVal.val1 = 1
   GetVal.val2 = "test"
End Function

Open in new window


This will call GetVal, return an integer and a string, and print both to the immediate window.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now