Announcement

Collapse
No announcement yet.

VBA Code To Write Values To Array Of Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          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.

          Comment

          Working...
          X