0

I'm trying to get exp. Moving Average of Some quote. I build a custom function to retrieve it in my sheet. when I'm trying to execute in cell, it throws #Value Error. But when I execute it in immediate window it runs perfectly. even I've tried to make it volatile. I'm not sure why this happening. Below is my VBA code:

Option Explicit

 Function ExpMovingAverage9(Symbol As String, FromDate As String, ToDate As String) As Variant
    Application.Volatile
    Dim ws As Worksheet
    Dim tblInv As ListObject
    Dim SymbolTable As ListObject
    Dim FromDateTable As ListObject
    Dim ToDateTable As ListObject
    Dim Last As Long
    Dim LastValue As Variant
    Set ws = ThisWorkbook.Worksheets("AARTIIND")
    Set tblInv = ws.ListObjects("ChartData")
    Set SymbolTable = ws.ListObjects("Symbol")
    Set FromDateTable = ws.ListObjects("FromDate")
    Set ToDateTable = ws.ListObjects("ToDate")
    SymbolTable.DataBodyRange.Cells(1, 1) = Symbol
    FromDateTable.DataBodyRange.Cells(1, 1) = FromDate
    ToDateTable.DataBodyRange.Cells(1, 1) = ToDate
    tblInv.QueryTable.Refresh
    Application.CalculateUntilAsyncQueriesDone
    Last = tblInv.Range.Rows(tblInv.Range.Rows.Count).Row
    LastValue = Range("G" & (Last - 1)).Value
    ExpMovingAverage9 = LastValue
End Function
bad_coder
  • 11,289
  • 20
  • 44
  • 72
Vijay Pal
  • 1
  • 1
  • `SymbolTable.DataBodyRange.Cells(1, 1) = Symbol` and the next two lines -- you can't write to another cell like this in a UDF called from a cell. – BigBen Aug 30 '21 at 12:36
  • Any other way to write cells? I'm not much familiar with vba please suggest – Vijay Pal Aug 30 '21 at 12:48
  • Use a `Sub` (maybe assign to a button), not a UDF. – BigBen Aug 30 '21 at 12:48
  • I think you can modify other cells by using `Evaluate`, read this [answer](https://stackoverflow.com/a/55223874/15597936) although using a sub instead is better imo – Raymond Wu Aug 30 '21 at 12:53
  • I've multiple quotes to pass through this function and don't want to click every time on button to refresh the values...please suggest if any solution to this – Vijay Pal Aug 30 '21 at 12:54
  • Use a Sub to call the function repeatedly? – BigBen Aug 30 '21 at 12:56

0 Answers0