For the Range Value you would get the same number format as Excel simply by using
.Sheets(i).Range(RangeName).Text
instead of
.Sheets(i).Range(RangeName).Value
You could then do away with a bunch of the fiddly code trying to replicate that.

Your code to return the RangeValue has an error handler but it makes a fatal assumption that the named range is a single cell. So not only does the bookmark name in Word need to exist in the Excel workbook (that the user selected), but the capitalisation of the bookmark/name must match and it must be only a single cell. A clean way to deal with the capitalisation issue is shown here Excel Named Ranges - Does a Named Range Exist



To also handle the possibility of the Excel named range existing but being more than one cell, lets assume that you want the value in the first cell
.Sheets(i).Range(RangeName).Cells(1).Text

Post a sample document and workbook pair if you want the code fixed so that it works with your sample. It is much easier for us to work out the code flaws when we see what your ACTUAL input is.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia