What does =ASC() do?

Says Here But it doesn't really help.

I have been playing with this function but I have yet to find out if it is useful at all or not.

Definition from MSDN:

Asc Function returns an Integer representing the character code corresponding to the first letter in a string.

This example uses the Asc function to return a character code corresponding to the first letter in the string.

Dim MyNumber
MyNumber = Asc("A") ' Returns 65.
MyNumber = Asc("a") ' Returns 97.
MyNumber = Asc("Apple") ' Returns 65.

This is generally used to distinguish between upper & lower case letters, but you can look up ascii values for all kinds of symbols (and some that aren't, like carriage returns).

No no no, not in VBA. I use ASC all the time in VBA. But if you use it as a formula, it's different.

This is quoted from the page which I posted a link to:

For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters.

An example it shows:

Formula Result
=ASC("EXCEL") "EXCEL"

In this forum, we would all assume that you are talking about an Excel formula. Your link indicates that you are making reference to "Share Point Services". You might close out this thread and create a new thread specifically referencing "Share Point Services: ASC() Function"

Its not Share point services only as Excel VBA does have the ASC function and says the same thing in its help file as was linked to. ;)

http://office.microsoft.com/assistan...61033&Client=1

So much for the HelpHeap ... this situation must be version specific. I'm running Excel 2003 and there is NO Help info in Excel (not in VBA Editor) for "ASC()", but the cell formula "=ASC(A1)" doesn't barf and returns a text string from the text contents of A1 ("TEST" > "TEST"). Leave it to Megashaft to use the exact same name ("ASC()") for two totally different functions and totally ignore one of them in the HelpHeap.

Well, what practical use is the function?

I have no idea other then to compact some text without actually changing the string. Kind of a font width thing. Double byte to Single byte but are we talking about memory bytes or something else?

This made me think of some work I did awhile back (and have completely lost track of) regarding strings and Unicode, and I distinctly remember alternating zeros and ascii codes. I was thinking that this function (ASC()) might be used to convert a Unicode string of ASCII characters into a single-byte string (or an array of characters) ??? I have a note highlighted in my "Definitive Guide to Excel VBA" that says "NOTE: Characters are stored internally in Unicode format, that is, with two bytes per character." I built a quick String-To-Byte-Array (thanks to previous help by Declan Kenny) and my test strings come back as single byte ASCII codes. Am I going crazy or what?

Code:

---

Option Explicit
'MS Windows API Call
Private Declare Sub CopyMemory Lib "KERNEL32" _
    Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

'Convert a String to an Array of Bytes
Function StringToByteArray(ByRef aString As String) As Byte()
    Dim StrLen As Long
    Dim Buffer() As Byte
   
    'Fetch the length of the Buffer - assumes single byte
    StrLen = Len(aString)    ' * 2  for Unicode???
    'Don't bother with a null string
    If StrLen > 0 Then
        'Set the new size of the buffer
        ReDim Buffer(0 To StrLen - 1) As Byte
        'Fetch the String into the Byte Buffer
        CopyMemory Buffer(0), ByVal aString, StrLen
        'Set the Return parameter to the loaded buffer
        StringToByteArray = Buffer
    End If
End Function

Sub Test_It()
    Dim aStr As String
    Dim bStr() As Byte
    Dim i As Integer
   
    aStr = Cells(1, 1).Text
    'aStr = "This is a test.  This is only a test."  < Both of these do the same thing
   
    bStr = StringToByteArray(aStr)
    Debug.Print LBound(bStr), UBound(bStr)
   
    For i = 0 To UBound(bStr)    '<<< Shows single byte ASCII characters !!!
        Debug.Print bStr(i), Chr(bStr(i))
    Next i
   
End Sub

---

But what is this used for? That's what I don't see. Something so memory critical as to require double byte characters to be converted to single byte doesn't really seem compatible with the memory requirements of Excel itself. Maybe if it needs to be exported to some other device, but it seems tedious to do that in formulas for cells instead of in the export process.

I suspect it is more aesthetic than memory-related. If you were using Excel as a platform for an app that required outputting to some kind of message area, formed from a merging of a few cells for example, then you might want to make sure that your text was always going to fit in regardless of the font.

Or maybe you've constructed a multi-column list out of a single cell - you need to know how many spaces to put in between each column and hence you don't want it to get confused by having multiple width fonts. Like you might use Courier to accomplish a similar task.


Just a thought.

zaza

Haha wow, haven't been following this due to being busy at work. It seems I have dug up an alien corpse or something.

Whatever this is used for, it doesn't seem to be anything that any of us need.

Thanks for all the replies.