Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: VBA Code To Write Values To Array Of Cells

  1. #1
    Join Date
    7th June 2010
    Posts
    8

    VBA Code To Write Values To Array Of Cells

    Hi,

    I want to create a custom function that returns several arguments but I do not want to execute it as an array function (by selecting a range and pressing ctr+shift+enter). What I want is to execute it as a normal worksheet function ( =NameOfFunction(argument1,argument2, …) ) and that the adjacent cells are filled with the contents of the array that has been generated by the function. Is this possible?

    The expression range(somerange) = myfunction returns an error if not executed from inside VBA.

    All suggestions are much appreciated!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,786

    Re: Custom Function To Return An Array As Result

    Code:
    Sub ArrayAsHRange()
        Range("A1:D1") = Array("Head1", "Head2", "Head3", "Head4")
    End Sub
    
    Sub ArrayAsVRange()
        Range("A1:A4") = WorksheetFunction.Transpose(Array("Head1", "Head2", "Head3", "Head4"))
    End Sub
    As far as I know, a Worksheet Function cannot return an array as a result to a range without entering as an array.

  3. #3
    Join Date
    7th June 2010
    Posts
    8

    Re: Custom Function To Return An Array As Result

    Thank you very much for replying such quickly. However, that is not at all what I am trying to accomplish.

    My code is:


    Code:
    Function bdh(Ticker As String, StartDate As Date, EndDate As Date)
    
    Dim DataSheet As Worksheet
    Dim QT As QueryTable
    
    
    Set DataSheet = Sheets("DB")
    Set QT = DataSheet.QueryTables("Yahoo_Stocks")
    
    
    qurl = "http://ichart.yahoo.com/table.csv?s=" & Ticker
    qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
                "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
                Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & "&q=q&z=" & _
                Symbol & "&x=.csv"
    
    
    With QT
        .Connection = "URL;" & qurl
        .BackgroundQuery = True
        .TablesOnlyFromHTML = False
        .refresh BackgroundQuery:=False
        .SaveData = True
    End With
                
    
    
    
    bdh = DelimitTheArray
    
    End Function
    
    Private Function DelimitTheArray()
    Dim i As Integer
    Dim DataSheet As Worksheet
    
    Set DataSheet = Sheets("DB")
    Dim ar() As Variant
    ReDim ar(1 To DataSheet.UsedRange.Rows.Count - 1, 0 To 6)
    
    For i = 1 To DataSheet.Range("A1").End(xlDown).Row - 1
    
        ar(i, 0) = Split(DataSheet.Cells(i, 1), ",")(0)
        ar(i, 1) = Split(DataSheet.Cells(i, 1), ",")(1)
        ar(i, 2) = Split(DataSheet.Cells(i, 1), ",")(2)
        ar(i, 3) = Split(DataSheet.Cells(i, 1), ",")(3)
        ar(i, 4) = Split(DataSheet.Cells(i, 1), ",")(4)
        ar(i, 5) = Split(DataSheet.Cells(i, 1), ",")(5)
        ar(i, 6) = Split(DataSheet.Cells(i, 1), ",")(6)
    
    Next i
    
    DelimitTheArray = ar
    
    End Function

    And what it does is to download stock quotes from yahoo servers.

    The information that is downloaded looks something like:

    Date,Open,High,Low,Close,Volume,Adj Close
    2010-06-04,2.35,2.54,2.32,2.33,3366100,2.33


    The data then gets delimited and assigned to an array. When I type =bdh(ticker,StartDate,EndDate) in excel the result that is returned is “Date” – the first element of the downloaded array.

    What I am trying to accomplish is to make the bdh function return all the elements of the array without having to execute it as an array formula - by selecting a worksheet range and pressing ctr+shift+enter.

    Thanks a lot for your answers.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    8th March 2010
    Posts
    1,651

    Re: VBA Code To Write Values To Array Of Cells

    You can achieve what you want by not using a function (that's not designed for reading/writing, but for calculating), but using a macro. Arguments can also pased through in macros.
    And you can split the data using Texttocolumns.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th June 2010
    Posts
    8

    Re: VBA Code To Write Values To Array Of Cells

    Ok thanks for the feedback. There must be a way to do it with a function though. Bloomberg has developed an addin that works exactly the way that I described and which I am trying to replicate.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Sum/Total Values In Array Macro Code
    By craigwg in forum EXCEL HELP
    Replies: 10
    Last Post: May 2nd, 2008, 16:46
  2. Values From Array To Cells
    By Karver in forum EXCEL HELP
    Replies: 5
    Last Post: November 30th, 2006, 15:01
  3. For Each Loop. Write Values to Other Cells
    By roughneck82 in forum EXCEL HELP
    Replies: 2
    Last Post: August 11th, 2006, 19:08
  4. Write/change Values in a CLOSED Worksheet (by code)
    By Michael Avidan in forum EXCEL HELP
    Replies: 4
    Last Post: April 4th, 2006, 06:36
  5. Write array with grouping
    By ghh3rd in forum EXCEL HELP
    Replies: 5
    Last Post: March 3rd, 2006, 02:51

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno