Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,718

    Re: Custom Function To Return An Array As Result

    VB:
    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:


    VB:
    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