Sheet doesn't recalculate after picking from list including own function-CodePudding

I have workbook with user VBA function (returning name of the cell):

Function cellName()
    cellName = ActiveCell.Offset(0, 0).Name.Name
    
End Function

I have a list dictList with 3 columns used as dictionary (cellNames; ENG equivalents; CZ equivalents)

I have a cell $P$1 including data validation that can contain EN/CZ value.

Each cell in the sheet that has specified name (=cellName) includes a function

=VLOOKUP(cellName();dictList;IF($P$1="CZ";2;3);FALSE)

Finally each named cell contains text in czech or english language based on its name and vlooked value in dictlist.

The problem occurs, when I switch in $P$1 from CZ to EN or vice versa.

The values with VLOOKUP formulas shows result #VALUE! until I press F2 and Enter on each cell.

I don't want to press F2 enter on each cell after switching the language.

Tank you for your help.

Karel

When I put the result of cellName() directly to vlookup, then it works as expected. When I put there back my function, then it returns the #VALUE! again.

CodePudding user response：

Instead of using ActiveCell, use Application.ThisCell:

Returns the cell in which the user-defined function is being called from as a Range object.

Public Function cellName() As String
    cellName = Application.ThisCell.Name.Name
End Function