Compile Error when trying to return PChar or OleVariant for UDF

Question

The title doesn't quite capture the essence of the issue.

I have a UDF function that returns a PChar.

function AccountDescription(sAccountId: PChar) : PChar; stdcall;

This was working fine but I realized I wanted to return #N/A if the accountId was not found.

I discovered CVErr(xlErrNA) and changed the Signature to return OleVariant. But now I am receiving [Error] Incompatible types: 'OleVariant' and 'PAnsiChar'.

I could not find any information on how to resolve this so I figure my understanding of the problem must not be correct.

I tried just passing a string which compiled but produced a runtime error of "Invalid variant type".

The full code is:

function AccountDescription(sAccountId: PChar): OleVariant; stdcall;
var
  strResult: string;
  strPChar : PChar;
begin

    try
        strResult:= repo.GetAccount(sAccountId).Description;
        strPChar := strAlloc(length(strResult)+1) ;
        StrPCopy(strPChar, strResult) ;
        Result := strPChar;
    except
      Result := CVErr(xlErrNA);
    end;

end;

Note: Is excel responsible for destroying the string or is that my cleanup? Should I be creating a copy or should I just be returning a pointer to an existing string. After typing it I feel like I should be returning a pointer.

Update: Removed some irrelevant code in the example.

Now using:

function AccountDescription(sAccountId: PChar): OleVariant; stdcall;
var
  strResult: string;
begin

    try
        Result := PChar(repo.GetAccount(sAccountId).Description);            
    except
      Result := CVErr(xlErrNA);
    end;

end;

Answer 1

You do not need the PChar cast, you can assign a String directly to an OleVariant (it will be converted by the RTL into a BSTR that the receiver will then free when done using it):

Result := repo.GetAccount(sAccountId).Description;     

As for reporting an error, do you have a viable CVErr() function in your Delphi code? In VB, CVErr() returns a Variant of type Error (varError in Delphi) containing an error code (xlErrNA is 2042). Delphi has a VarAsError() function for that same purpose:

Result := VarAsError(2042);