No announcement yet.

VBA Code To Write Values To Array Of Cells

  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code To Write Values To Array Of Cells


    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

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

      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 = "" & 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

      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.


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


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