2

Thanks in advance for your help.

I am not sure where the problem in my UDF but the excel restarts automatically after executing the program and also shows MsxBox twice with the reference value.

    Public queryString As String
    Public Function SetIt(RefCell) As String
    On Error GoTo CatchBlock
        MsgBox RefCell.Value
        queryString = RefCell.Value
        RefCell.Parent.Evaluate "SetValue(" & RefCell.Address(False, False) & ")"
        GoTo Finally
    CatchBlock:
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error in Function"
    Finally:
        SetIt = ""
    End Function

// For now I am trying to set the cell reference value in some other cell.

    Sub SetValue(RefCell As Range)
       RefCell.Offset(1, 1).Value = queryString
    End Sub

I am still beginner and need to accomplish another big task based on this program code execution.

Please help !!

Excel 64 bit & Office 365 is my environment

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Hari
  • 441
  • 6
  • 15
  • You can't pass a string as an argument and expect it to magically become a range reference. Either pass a range ref or convert the string in SetValue. –  Oct 08 '18 at 09:06
  • @Jeeped, Sorry, I updated the code now but still I face the restart issue. – Hari Oct 08 '18 at 09:09
  • 2
    You are using [a trick](https://stackoverflow.com/a/23437280/11683) to force Excel to do something it is not designed to do (i.e. allow sheet functions to change sheets). I'm not surprised it crashes. You are not supposed to change sheets from functions called from sheets. – GSerg Oct 08 '18 at 09:26
  • @GSerg, Do you recommend not use the trick ? Any other recommendations ? – Hari Oct 08 '18 at 09:28
  • Don't use that trick - or do and have a fresh restart... – Solar Mike Oct 08 '18 at 10:47
  • If you explain what you are trying to accomplish, it'll be easier to recommend something that'll help you. – Sam Oct 08 '18 at 10:47
  • @Sam, The plan for me is to access REST service and upon calling it, the UDF function will receive JSON string. This JSON string would be parsed and data will be spread across rows and columns. Custom tailored excel sheet for our project – Hari Oct 08 '18 at 11:04
  • 1
    In that case I think it is wise to not use a function call from within a cell and instead go for a Sub that makes the call and the updates. Calling external services from a function call in a cell will give you a lot of headaches. – Sam Oct 08 '18 at 11:18
  • You might be interested to compare your code to this: https://stackoverflow.com/a/55223874/1903793 It works there. Can you explain what your function is expected to return? – Przemyslaw Remin Oct 28 '19 at 10:30

0 Answers0