Forum Discussion

JGB82's avatar
JGB82
Copper Contributor
Jan 11, 2023

UDF not working in Excel 365

I have made a new funcion via VBA and put it in its own module, so not in the workbook or something, I made a new module. It's a function for giving back the background color code of a selected cell...
excel
Formulas and Functions
Macros and VBA
office 365
HansVogelaar's avatar
HansVogelaar
MVP
Jan 11, 2023

JGB82 

Try making the folder containing the workbook a Trusted Location for Excel:

  • Select 'Vertrouwde locaties' in the Vertrouwenscentrum dialog.
  • If your workbook is stored in a network folder, tick the check box 'Vertrouwde locaties in mijn netwerk toestaan (niet aanbevolen)'.
  • Click 'Nieuwe locatie toevoegen...'
  • Click Bladeren, select the folder of the workbook, then click OK.
  • Quit and restart Excel to be on the safe side.
  • Reopen the workbook.
JGB82's avatar
JGB82
Copper Contributor
Jan 11, 2023
Ok, I'm gonna try that! Get back to you!
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 11, 2023
    also note certain folder are 'blocked' from being trusted/allowed like Downloads folder. So make sure you are not using that.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jan 11, 2023

      JGB82 BTW I would suggest a couple tweaks to the UDF

      Function NumColor(cell As Range) As Long
    NumColor = cell.Cells(1, 1).Interior.Color
End Function

      first will define the output format (long) and the second will have the function return the FIRST cell if a range is passed to the function