VBA function doesn't select range

Question

I'm trying to code a function that will retrieve historic data on financial data from finance.yahoo.com

I want the function to write the data below the cell containing the function call, but I want to clean the range before writing new values. For that I need to select the range and delete, but the range("A6:F5000").select is doing nothing!

After I write the data I want to transform text to columns. I need to select the range of data, but this range selection is not working..

This is the code of my function:

Function getdata(ticker As String, dtinicio As Double, dtfim As Double, per As String)
  Dim r As Range
  Set r = Range(Application.Caller.Address)

  Workbooks(r.Parent.Parent.Name).Sheets(r.Parent.Name).Activate
  ActiveSheet.Range("A6:G50000").Select ' not working
  Selection.ClearContents

  qurl = "http://real-chart.finance.yahoo.com/table.csv?s=" & ticker & "&d=" & Month(dtfim) - 1 & "&e=" & Day(dtfim) & "&f=" & Year(dtfim) & "&g=" & per & "&a=" & Month(dtinicio) - 1 & "&b=" & Day(dtinicio) & "&c=" & Year(dtinicio) & "&ignore=.csv"

 With Workbooks(r.Parent.Parent.Name).Sheets(r.Parent.Name).QueryTables.Add(Connection:="URL;" & qurl, Destination:=Workbooks(r.Parent.Parent.Name).Sheets(r.Parent.Name).Range("A6"))
    .TablesOnlyFromHTML = False
    .FillAdjacentFormulas = False
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
    .SaveData = True
  End With

  For i = 1 To Workbooks(r.Parent.Parent.Name).Sheets(r.Parent.Name).QueryTables.Count
    Workbooks(r.Parent.Parent.Name).Sheets(r.Parent.Name).QueryTables(1).Delete
  Next i

  Call Macro2 '  not working even after I separate these commands in a sub..

  getdata = Now()
End Function

Sub Macro2()
'
' Macro2 Macro
'
  Dim r As Range
  Set r = Range(Application.Caller.Address)

  Range("A6").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.TextToColumns Destination:=Range("A6"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 5), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True

End Sub

This is very strange because I have an older version of this procedure, coded as a sub (not a function and it worked very well)

Sub getdata()

  Application.DisplayAlerts = False

  qurl = ThisWorkbook.Sheets("Downloader").Range("a13").Text
  On Error Resume Next
  With ThisWorkbook.Sheets("Downloader").QueryTables.Add(Connection:="URL;" & qurl, Destination:=ThisWorkbook.Sheets("Downloader").Range("a20"))
    .BackgroundQuery = False
    .TablesOnlyFromHTML = False
    .FillAdjacentFormulas = False
    .RefreshStyle = xlOverwriteCells
    .Refresh BackgroundQuery:=False
    .SaveData = True
  End With

  For i = 1 To ThisWorkbook.Sheets("Downloader").QueryTables.Count
    ThisWorkbook.Sheets("Downloader").QueryTables(1).Delete
  Next i

  Call OrganizaDados  
End Sub

Sub OrganizaDados()

  ThisWorkbook.Sheets("Downloader").Select
  Range("A20").Select

  Range(Selection, Selection.End(xlDown)).Select

  Selection.TextToColumns Destination:=Range("A20"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 5), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True

'    ActiveWorkbook.Worksheets("Downloader").Sort.SortFields.Clear
'    ActiveWorkbook.Worksheets("Downloader").Sort.SortFields.Add Key:=Range( _
'        "A20:A20004"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
'        xlSortNormal
'
'    With ActiveWorkbook.Worksheets("Downloader").Sort
'        .SetRange Range("A20:G20004")
'        .Header = xlYes
'        .MatchCase = False
'        .Orientation = xlTopToBottom
'        .SortMethod = xlPinYin
'        .Apply
'    End With
End Sub

Can someone help me understand what I'm doing wrong?

It is usually a good idea to [avoid using select in macro](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Vincent G, Jan 19 '17 at 13:50
`ActiveSheet`, without an object qualifier, is the same as `ActiveWorkbook.ActiveSheet`, and is probably not the same as `Workbooks(r.Parent.Parent.Name).Sheets(r.Parent.Name)` — Vincent G, Jan 19 '17 at 13:55
If you are trying to use the function as a UDF, its only interaction with the spreadsheet itself is the return value in the cell that contains the formula. In particular -- it won't do things like select a range. "I want the function to write the data below the cell containing the function call" is another thing which is not possible with a UDF. — John Coleman, Jan 19 '17 at 17:38
See this: https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel (although, there are various somewhat kludgy work-arounds that people have discovered, see: http://stackoverflow.com/q/23433096/4996248 ) — John Coleman, Jan 19 '17 at 17:46
Thank you for your answer. You are right, the main problem is that a function cannot change other cells. — Mário Fernandes, Jan 20 '17 at 13:47

VBA function doesn't select range

0 Answers0