Names Collection - Generating RefersToRange from RefersTo when RefersToRange not populated

I am working in the ThisWorkbook Names Collection in Excel VBA iterating through all the Names in a Workbook to compile a summary of all the various settings.

The ThisWorkbook Name(rng_Name) RefersTo is always populated but ThisWorkBook Names(rng_Name) RefersToRange is not always populated dependent on what is actually referred to by the Name.

I have found 3-D references and references to another Workbook typically appear to not have RefersToRange populated even though I would have thought they are simply ranges.

I can build a Range (from RefersTo) by using text string manipulation to pull apart the RefersTo by searching for ! and : etc but there must be a more efficient means of doing this.

I was initially thinking of using INDIRECT function or similar but that is not available in VBA.

Can anyone make any suggestions about an efficient way of generating a Range (when one is not populated) from the RefersTo? I couldn’t find any prior threads covering this.

Update
It appears that Excel doesn't recognize the RefersTo string generated by 3-D References.
Even when stripping out the = from the RefersTo string to generate a new str_1 and then when using Range(str_1) it will build a valid range for everything OTHER than 3D references.
So I am stuck with having to use text string manipulation at the moment?