Solved

Returning multiple values on vba with a function

Posted on 2011-09-14
3
305 Views
Last Modified: 2012-09-17
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
Comment
Question by:awesomejohn19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Expert Comment

by:slycoder
ID: 36538243
Break your function into two explicit parts and try something like:

Range("A1").Value = myfunction1()
Range("B1").Value = myfunction2()
0
 
LVL 4

Expert Comment

by:GeoffHarper
ID: 36538275
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
 
LVL 4

Accepted Solution

by:
AgeOfEmpires earned 500 total points
ID: 36538596
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question