Literal data (numeric, mainly)

[ChrisGreaves]

What do these lexical atoms have in common?

0	1	2	3.4
5.6E+3	7.8E-9	45722.94	06-MAR-25
25-MAR-06	2025-03-06	7.9%	.012
(3.4)	1,234,567.89012	$32.3	7/5

Now that you have satisfied yourself that you know about numeric literals in spreadsheets, fire up your special spreadsheet processor and key them in, one in each cell, Some of them surprised me.
I was more surprised at the impact on my impression of numeric strings.

The task in hand is to parse formulas in Excel, things such as
=(3.4 + 7.89) ^ 2
Or, since we can nest numeric expressions with parentheses, something as simple as a fraction:-
(7/5)
I confess that in the example above, I was thinking of Excel’s ability to show a fractional value in fractional form, using the forward slash as a solidus.

I then managed to get myself confused between (i) what Excel shows to me by formatting a value I have keyed in and (b) how Excel interprets what I key in. That dollar symbol (character) for example. That can arise because we have told Excel (or Lotus or QuattroPro or ...) to format the cell with a dollar sign. But clearly here I am proposing to key IN that dollar sign as part of data-entry. In most cases we don't need a dollar sign - the label in the cell to the left does the job "Net Pay".

There’s more, but you may find this of interest.
Cheers, Chris

[HansV]

Excel formats the cell in which you input $32.3 as currency because $ is the default currency symbol on your computer.
On my computer, $32.3 is treated as text, so the number format is General and the value is displayed left-aligned. If I enter €32.3, however, it is formatted as currency.

[snb]

Before parsing it might be worthwhile to make an inventory of the application's defaults.

Code: Select all

Sub M_snb()
  for j=1 to 37
    msgbox application.international(j)
    c00=c00 & vblf & j & vbtab & application.international(j)
  next
  msgbox mid(c00,2)
End Sub

[ChrisGreaves]

Excel formats the cell in which you input $32.3 as currency because $ is the default currency symbol on your computer.
On my computer, $32.3 is treated as text, so the number format is General and the value is displayed left-aligned. If I enter €32.3, however, it is formatted as currency.

Thanks for this response, Hans.

I am working on Rob Van Gelder's code. Rob uses run-time variables strDecimalSeparator and strListSeparator and so on, so in that sense his code if flexible. My examples (I should have stated) were run on Excel2003 with North American settings.
Indeed $32.3 arrives as currency, but my thrust was towards something more general. This paper says in part:-
3.3.1. Dates The appearance of date and time values in spreadsheets depends on the presentation settings of cells. Internally, date and time values are stored as positive floating point numbers with the integer portion representing the number of days since a Jan 0 1900 epoch and the fractional portion representing the portion of the day passed. For this reason, the grammar only parses numeric dates and times and these are not distinguishable from other numbers.
:slaps forehead: Of course! Dates are stored as numbers. It is Rs45,723.43 as I type this.

That led me to wonder just how much literal data we might key in that is stored as a number.
That led me to ponder who set these rules, and that made me think that it must be the client or project manager who decides what is allowed, and hence what I specify in the BNF grammar.

This rabbit hole has many trails, including "Why is 12.34E+2" acceptable as a numeric literal to Excel2003 but "12.34E+2.5" is not. I can see no mathematical reason NOT to allow a fractional exponent, and a BNF parser for mathematics in general might allow "12.34E+2.5". But in the real world, some human client decides what to pay for.

The dollar sign is interesting; it doubles as a currency sign for some of us but, I think for all of us it remains valuable in specifying absolute cell addresses. This led me to consider parsing tokens separately, with "$32.3" being parsed as two strings "$" and "32.3" rather than just treating it as a formatted number.

More later when/if I pop out the other side of the warren!
Cheers, Chris

[ChrisGreaves]

Before parsing it might be worthwhile to make an inventory of the application's defaults.

Thank you snb. Do you know a way to get the title of each setting from Application.International?
Cheers, Chris

Untitled.jpg

[HansV]

Floating point numbers are stored as a real number >=1 and <10 plus a sign and an integer exponent. Excel follows this in scientific notation - it does not support fractional exponents in this notation, nor in number entry.
You can use fractional exponents in formulas:
=12.34^5.67
or
=POWER(12.34, 5.67)

[snb]

Here you go:

x1 = Application.International(xlCountryCode) ' 1
x1 = Application.International(xlCountrySetting) ' 2
x1 = Application.International(xlDecimalSeparator) ' 3
x1 = Application.International(xlThousandsSeparator) ' 4
x1 = Application.International(xlListSeparator) ' 5
x1 = Application.International(xlUpperCaseRowLetter) ' 6
x1 = Application.International(xlUpperCaseColumnLetter) ' 7
x1 = Application.International(xlLowerCaseRowLetter) ' 8
x1 = Application.International(xlLowerCaseColumnLetter) ' 9
x1 = Application.International(xlLeftBracket) ' 10
x1 = Application.International(xlRightBracket) ' 11
x1 = Application.International(xlLeftBrace) ' 12
x1 = Application.International(xlRightBrace) ' 13
x1 = Application.International(xlColumnSeparator) ' 14
x1 = Application.International(xlRowSeparator) ' 15
x1 = Application.International(xlAlternateArraySeparator) ' 16
x1 = Application.International(xlDateSeparator) ' 17
x1 = Application.International(xlTimeSeparator) ' 18
x1 = Application.International(xlYearCode) ' 19
x1 = Application.International(xlMonthCode) ' 20
x1 = Application.International(xlDayCode) ' 21
x1 = Application.International(xlHourCode) ' 22
x1 = Application.International(xlMinuteCode) ' 23
x1 = Application.International(xlSecondCode) ' 24
x1 = Application.International(xlCurrencyCode) ' 25
x1 = Application.International(xlGeneralFormatName) ' 26
x1 = Application.International(xlCurrencyDigits) ' 27
x1 = Application.International(xlCurrencyNegative) ' 28
x1 = Application.International(xlNoncurrencyDigits) ' 29
x1 = Application.International(xlMonthNameChars) ' 30
x1 = Application.International(xlWeekdayNameChars) ' 31
x1 = Application.International(xlDateOrder) ' 32
x1 = Application.International(xl24HourClock) ' 33
x1 = Application.International(xlNonEnglishFunctions) ' 34
x1 = Application.International(xlMetric) ' 35
x1 = Application.International(xlCurrencySpaceBefore) ' 36
x1 = Application.International(xlCurrencyBefore) ' 37

PS. I'd prefer the MsgBox

[SpeakEasy]

>Do you know a way to get the title of each setting

There's a list here of the enumeration in question

https://learn.microsoft.com/en-us/offic ... ernational

Sadly VBA doesn't give us a way to list the enum's member names, which is what you are asking for

But we can get around that by using the TypeLib Information library. Just set a reference to it, and have a play with this code:

Code: Select all

Option Explicit

Public Sub TestEnumReading()
     GetEnumMemberName "XlApplicationInternational"  ' Lists members of XlApplicationInternational
End Sub

' requires reference to TypeLib Information library
Private Function GetEnumMemberName(strEnumName As String, Optional varVal As Variant) As Variant

    Dim TLIApp As TLIApplication
    Dim myConstant As ConstantInfo
    Dim EnumMember As MemberInfo
    Dim TLI As TypeLibInfo

    Set TLIApp = New TLIApplication

    ' Get our type library info from the relevant file
    ' This is the line that makes this example specific to Excel library
    Set TLI = TLIApp.TypeLibInfoFromFile("C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE")
 
    ' Loop through members of libraries constants
    For Each myConstant In TLI.Constants
        If myConstant.TypeKind = TKIND_ENUM And myConstant.Name = strEnumName Then
            For Each EnumMember In myConstant.Members
                 If Not IsMissing(varVal) Then
                    Select Case TypeName(varVal)
                        Case "String"
                            If EnumMember.Name = varVal Then GetEnumMemberName = EnumMember.Value
                        Case "Integer"
                            If EnumMember.Value = varVal Then GetEnumMemberName = EnumMember.Name
                    End Select
                Else
                ' List all members of this enum in debug window if you like
                ' it is left as an exercise to the reader to determine a nice way of returning this info to the calling procedure...
                 Debug.Print EnumMember.Name, EnumMember.Value
                End If
            Next
        End If
    Next
End Function

This can be invoked in a number of ways:

It can be invoked in a number of ways:

GetEnumMemberName("XlApplicationInternational", 20)
returns name of member of XlApplicationInternationalenumeration with value of 20

GetEnumMemberName("XlApplicationInternational", "xlCountrySetting")
returns value of member of XlApplicationInternationalenumeration with name of xlCountrySetting

GetEnumMemberName("XlApplicationInternationalenumeration")
displays complete list of members of the named enumeration with their values in the IDE's immediate window

You can use it to list any Excel enumeration (well, actually any enumeration in any COM/ActiveX library)

What you can't do is use it to read user defined enumerations in the application that you are running it from

(you'll also note that there are 46 members, not 37)

[ChrisGreaves]

... You can use fractional exponents in formulas:

Why am I surprised? https://eileenslounge.com/viewtopic.php?f=27&t=42018.
Hmm!
So Excel does not support fractional exponents as numeric literals but does support fractional exponents in formulas, that is, as a run-time executable that depends on Excel's internal coding.
Kinda blows to smithereens the mean examination question "Does Excel support fractional exponents?", doesn't it? !! ???
Thanks again.
Chris

[ChrisGreaves]

Here you go:
PS. I'd prefer the MsgBox

Thanks snb
The MsgBox is a pain to me. I have to keep clicking to see the entire result.
Debug is my friend because i can trace interim results, and important to me - I can set up a large batch of tests and run them un-attended.
Cheers, Chris

[ChrisGreaves]

But we can get around that by using the TypeLib Information library. Just set a reference to it, and have a play with this code:
...
What you can't do is use it to read user defined enumerations in the application that you are running it from

Speakeasy, thanks for this tip.
How did you know what my next question would be?

Code: Select all

Public Enum ParsingState
    ParsingError
    Expression1
    Expression2
    LeadingName1
    LeadingName2
    LeadingName3
    LeadingNameE
    WhiteSpace
    Text1
    Text2
    Number1
    Number2
    Number3
    Number4
    NumberE
    Bool
    ErrorX
    MinusSign               'for ambiguity between unary minus and sign
    PrefixOperator
    ArithmeticOperator
etc. etc.

Thanks again; Chris

[snb]

@speak

Resulting in:

Code: Select all

Sub M_snb()
  ReDim sp(50, 2)
  
  With New TLIApplication
    For Each it In .TypeLibInfoFromFile("F:\.....\excel.exe").Constants(93).Members
      sp(n, 0) = it.Name
      sp(n, 1) = it.Value
      sp(n, 2) = Application.International(it.Value)
      n = n + 1
    Next
  End With

  Cells(1, 6).Resize(UBound(sp), 3) = sp
  Columns(8).HorizontalAlignment = xlRight
  Cells(1, 6).CurrentRegion.Sort Cells(1, 7)
End Sub

[PJ_in_FL]

And people thought the magical incantations in Harry Potter's Hogwarts' classes were mysterious....