none
Bug in VBA AscW() for Excel 2011 RRS feed

  • Question

  • Hello All -

    I've found a bug in the VBA AscW() function in 2011 Excel, and maybe later.  As written AscW() requires an Unicode character input in the integer range (<=32,767).  As we know, Unicode input can be up to 65,535.  Try this in the VBE's immediate window:

    ?AscW(ChrW(65279))

    It should return 65279.  Instead it returns -257.  AscW() has "wrapped around."  My fix has been to follow it with a line of code somewhat like this:

    A = AscW(C)

    If A<0 then A = 65536 + A ' A is a negative number

    Try ?65536+AscW(ChrW(65279))

    AscW() needs to work in longs.

    ...Mick536

    Thursday, January 28, 2016 2:56 PM

All replies

  • AscW() needs to work in longs.

    It works, run the macro below.

    Andreas.

    Sub Test()
      Dim L As Long
      L = AscW(ChrW(65279))
      If ChrW(L) <> ChrW(65279) Then
        MsgBox "Hmm, you have a strange version..."
      Else
        MsgBox "Where should be the issue?"
      End If
    End Sub

    • Proposed as answer by Ciprian Lupu Sunday, January 31, 2016 7:40 AM
    Thursday, January 28, 2016 3:33 PM
  • Hi Andreas -

    The issue is L.  Add a line of code:  Debug.print L

    L: -257

    Which I think means ChrW() only works over integers, too. -257<>65279, and there are no negative codepoints.

    I just found out this has been noted before, in the same way.  See https://support.microsoft.com/en-us/kb/272138

    ...Mick356

    Thursday, January 28, 2016 3:52 PM
  • Hi, Mick536

    This is the forum to discuss questions and feedback for Excel for Developers, I'll move your question to the Technet forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents,
    and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.
     
    Thanks for your understanding.

    Friday, January 29, 2016 5:23 AM
  • The issue is L.  Add a line of code:  Debug.print L

    L: -257

    No, that's not the issue.:-)

    I know you'll see the negative number and when you use the negative number elsewhere it might be cause an issue.

    The reason why this is not (really) a bug is the fact the VB has not an Unsigned Integer (0 to 65.535) it has only a Signed Integer (-32.768 to 32.767), means the values of the 2 bytes of that Integer are the same in both cases.

    Other languages (C, Delphi, etc.) have Unsigned Integers and such a ASCW function is called in the background.
    See also the code below.

    Andreas.

    Sub Test()
      Debug.Print MakeUnsignedWord(-257)
      Debug.Print MakeSignedWord(65279)
    End Sub
    
    Function MakeUnsignedWord(ByVal FromInteger As Integer) As Long
      If FromInteger < 0 Then
        MakeUnsignedWord = FromInteger And &HFFFF&
      Else
        MakeUnsignedWord = FromInteger
      End If
    End Function
    
    Function MakeSignedWord(ByVal FromWord As Long) As Integer
      If FromWord > &HFFFF& Then Err.Raise 6, "MakeSignedWord", "Overflow"
      If FromWord < 0 Then Err.Raise 6, "MakeSignedWord", "Underflow"
      If FromWord > &H7FFF Then
        MakeSignedWord = FromWord - &H10000
      Else
        MakeSignedWord = FromWord
      End If
    End Function
    

    Friday, January 29, 2016 8:21 AM
  • Hi David -

    Thanks, you are the best judge.  But I thought developers were those folks.  ;-0

    ...best, Mick

    Friday, January 29, 2016 3:03 PM
  • Hi Andreas -

    Yes, -257 broke my code. But adding 65536 to negative code points fixed it.

    I do understand the difference between signed and un-signed integers, and if VBA had unsigned integers, unicode would fit perfectly.  But rather than giving VBA a new type of variable, longs exist and would seem to suffice.  I can't see a reason why AscW() and ChrW() demand integers, but someone else may have.

    Thanks for the code snippet above. 

    ...best, Mick

    Friday, January 29, 2016 3:33 PM