Updating formula in Excel via VBA

[GeneralShamu]

I am using a Bloomberg API import function that does not update automatically.

I am running a macro that depends on this cell updating. If you manually go into it by hitting 'F2' and then exit by hitting 'Enter.'

I tried recording a macro where I select the cell and go through these key strokes but I am unsure what htis is doing...

Range("B4").Select
ActiveCell.FormulaR1C1 = "=Formula1(xxx,xxx,xxx)"
Range("B5").Select

 

[Marcelo Branco]

Hi,

How do you call the function?

M.

 

[GeneralShamu]

Hi,

How do you call the function?

M.

It's a Bloomberg function (bdh)...there is no function call per say.

 

[Marcelo Branco]

It's a Bloomberg function (bdh)...there is no function call per say.

Sorry, i have no experience with this kind of function. I was thinking it was a function called by vba code, like
myVar = myFunction(argument1, argument2...)

Maybe someone else can help you.

M.

 

[GeneralShamu]

Sorry, i have no experience with this kind of function. I was thinking it was a function called by vba code, like
myVar = myFunction(argument1, argument2...)

Maybe someone else can help you.

M.

No need to apologize; I appreciate your assistance!

 

[Marcelo Branco]

No need to apologize; I appreciate your assistance!

Tks for your kind words.

So, let me say what was my idea. Create a volatile-function that would indirectly calls the main-function, ie, something like

Function CallsIndirect(argument1 As Long, argument2 As Long) As Variant
    Application.Volatile
    CallsIndirect = MainFunction(argument1, argument2)
End Function

But, i think it would not work for you...

M.

 

[gregtx81]

I think this might work...

If the Bloomberg API has more than a single "formula1" then comment out the "if" and "end if" statements (but leave the line in between).

This basically simulates going through and hitting 'F2 + Enter' on cells with formulas that contain "formula1".

Sub Macro1()
   
    
    Dim cellswithformulas As Range, cell As Range
    Dim text_to_search As String
   
   
    text_to_search = "formula1("
    Set cellswithformulas = ActiveSheet.usedrange.SpecialCells(xlCellTypeFormulas)
    
    For Each cell In cellswithformulas
        
        If InStr(1, cell.Formula, text_to_search, vbTextCompare) <> 0 Then
            cell.Formula = cell.Formula
        End If

    Next cell
        
End Sub