Code to determine place of apostrophe

[bknight]

I would like code to parse a string into numbers and it has an apostrophe in it.
would it be something like

Code: Select all

function FindApostrophe
Dim strPrice as String, strLeftPrice as String, strRightPrice as String, strApostrophe as String
intPrice as Integer, dblPrice as Double
strApostrophe  = Find "'" in strPrice
'this should be a number?
dblPrice = right(strPrice, length(strPrice - strApostrophe)
intPrice = Left(strPrice, length(strPrice - strPrice)
dblPrice = intPrice + dblPrice
end

[rory]

If it's just a number before and a number after, use Split instead.

[bknight]

Never used Spit before what is the correct syntax?

[HansV]

You have created a function but it doesn't take an argument and it doesn't return anything.
You declare a variable strPrice but you don't assign any value to it.

[bknight]

You have created a function but it doesn't take an argument and it doesn't return anything.
You declare a variable strPrice but you don't assign any value to it.

I know, it was to create a function to rid the "number" of the apostrophe, it needs work, but the apostrophe took priority in my mind then I can fill in that which you commented is in need.

[HansV]

Perhaps

Code: Select all

dblPrice = Val(Replace(strPrice,"'",""))

You'll have to assign a value to strPrice, and do something with dblPrice

[bknight]

Perhaps

Code: Select all

dblPrice = Val(Replace(strPrice,"'",""))

You'll have to assign a value to strPrice, and do something with dblPrice

It appears to these old eyes that you replace the " ' " with a " , ", wouldn't it be better with a decimal?

If a decimal is correct, then I can work with that.

[HansV]

You mentioned an apostophe. That is not the same as a decimal point.
So now I'm confused again, as usual.

[bknight]

Sorry let me put it another way, I have numbers in the form of xxx'xxx. The left most numbers are integers. The right most numbers are numerators of a fraction with denominators of 32.
113'27.5 would change to 113 + 27.5/32 or 113.859375, and this is the number from a string.

[HansV]

How could we have known that from your original post?

See if you can use this:

Code: Select all

Function Convert2Num(Price As Variant)
    Dim arr() As String
    If IsNull(Price) Then
        Convert2Num = Null
    Else
        arr = Split(Price, "'")
        If UBound(arr) = 0 Then
            Convert2Num = CDbl(arr(0))
        Else
            Convert2Num = arr(0) + arr(1) / 32
        End If
    End If
End Function

You can test the function in the Immediate window, e.g.

Code: Select all

? Convert2Num(113'27.5)

In a query, you can use

ConvertedPrice: Convert2Num([Price])

and in the Control Source of a text box on a form:

=Convert2Num([Price])

where Price is a text field containing values such as 113'27.5

[bknight]

The function would be best as the "text" entered needs to be in a number format i. e. ridding of the apostrophe and converting it to a usable number after the ridding the conversion is easy for me.

[ChrisGreaves]

The function would be best as the "text" entered needs to be in a number format i. e. ridding of the apostrophe and converting it to a usable number after the ridding the conversion is easy for me.

Hello. Hans mentioned the SPLIT function, which arrived after I had started coding in VBA.
I have attached a module of two functions, the first of which "dblApostrophe" should do what you want.
I have enclosed a copy of my predecessor to "Split" from my subroutine library.

Code: Select all

Function dblApostrophe(ByVal strInput As String) As Double
' 113'27.5 would change to 113 + 27.5/32 or 113.859375, and this is the number from a string.
    Dim strIntegerPart As String
    strIntegerPart = strSplitAt(strInput, "'")
    Dim strFractionalPart As String
    strFractionalPart = strInput
    Dim dblResult As Double
    dblResult = Val(strIntegerPart) + Val(strFractionalPart) / 32
    dblApostrophe = dblResult
'Sub TESTdblApostrophe()
'    Debug.Assert 113.859375 = dblApostrophe("113'27.5")
'End Sub
End Function

1) Copy/Paste the contents of the attached text file into a new module in your VBE project.
2) Drag the commented "TESTdblApostrophe" out of the function and de-comment it.
3) Run the "TESTdblApostrophe". It should run to completion and satisfy you that the function returns the correct value.

This code is far too wordy; I have used interim named variables to make it easier for you to see/understand what is going on.

Cheers, Chris

[bknight]

OK, the first code generates an error at strSplitAt unknown function.

So I deleted all of the lines and pasted in the text document, deleted the comments.
How then to run the function in the immediate window with some string that needs to be a number? I don't remember doing that, although Hans suggested that I type in Convert2Num("114'27.5") in the immediate window and that converted the string to a useable number in my Access thread. This procedure will go into an Access number field event.

[ChrisGreaves]

OK, the first code generates an error at strSplitAt unknown function. So I deleted all of the lines and pasted in the text document, deleted the comments. How then to run the function in the immediate window with some string that needs to be a number? I don't remember doing that, although Hans suggested that I type in Convert2Num("114'27.5") in the immediate window and that converted the string to a useable number in my Access thread. This procedure will go into an Access number field event.

Let’s take this one step at a time; it works. I have been using this code in all my applications for 25 years.
The program code I have supplied is generic VBA. It will work in any MSOffice application that supports VBA: Word, Excel, Access, Powerpoint, Outlook, Project etc.
We will show this by running it in WordVBA, after which you can transfer the code to whatever application you want.
(1) Open up MSWord. Then open up and maximize the Visual Basic Editor. You should see something like the image below:-

BKnight_01.png

I started with a new document which is called (by MSWord) Document2.
I have circled the comment/decomment toolbar buttons
In the Project Explorer window you can see that the Project is called “Document2”, and that I have inserted a new module, which is called “Module1”
I always have an Option Explicit statement – you will find posts about it here in Eileen’s Lounge.
(2) Now open up that attached text file. I copy/paste the contents by using:-
    (a) Ctrl+A to select all the text in the file. Guaranteed every time!
    (b) Ctrl+C to copy the selected text to the clipboard
    (c) Alt+Tab to return to the Visual Basic Editor
    (d) Ctrl+V to paste the clipboard contents into the module1.

BKnight_02.png

This is what I see now in the VBE. Except for that large arrow, which I painted on the screen snapshot to indicate that there is more code below; I can’t show all the code on my laptop screen.
(3) Save your work at this time with Alt+F then S
(4) You can drag the three (green) commented lines from the end of the function “dblApostrophe” until they are immediately below that functions END statement, then de-comment them using the de-comment tool in the toolbar

BKnight_03.png

In this screen shot I have dragged the TEST subroutine outside its function and I am about to click on the yellow-circled toolbar button.
(5) Once that is done, your three line TEST subroutine will still be selected.
(6) So tap the F5 function key to run that TEST subroutine.
(7) It should run quietly without any objection. That means that the TEST subroutine called the function “dblApostrophe”, which executed and returned the correct result.
(8) Try running that TEST subroutine again, but first change the

Code: Select all

Debug.Assert 113.859375 = dblApostrophe("113'27.5")

To read

Code: Select all

Debug.Assert 513.859375 = dblApostrophe("113'27.5")

(9) Now when you run the TEST subroutine, you should find that the subroutine halts with that Debug.Assert line highlit in yellow. That indicates that there is a problem. (Of Course! We supplied the wrong answer this time around!!!)

If you get this far, then collect some more sample data.
Duplicate that Debug.Assert line using your collected data.

Now this is important: Do NOT run the function with the new data to see what results pop out and then paste those results into your Debug statement.
Collect the data, calculate by yourself what you expect to get as a result, and use THOSE values to the left of the equals sign.
You want to know that the function returns what you expect, not what it returns.

Cheers, Chris

[SpeakEasy]

>which arrived after I had started coding in VBA

Heh That dates you a bit; Split came in with VBA 6.0 (based on VB6) back in 1999. Mind you I'm one to talk, I started back in the days of BASICA and GW-BASIC

Mind you we don't need Split at all for this if we take a slightly different approach. and know that Access has an Evaluate function. Assuming that we can always expect good input, this can pretty much be done in a single line:

Code: Select all

Function Convert2Num(Optional Price As String) as Double
    Convert2Num = Eval(Replace(Price, "'", "+") & "/32") ' Use Evaluate instead of Eval for Excel
End Function

[bknight]

I have an older version of excel and all that you described did not work, but after deleting the comments from test.
The Sub TESTdblApostrophe() did not join the code.
Running F-5 I get an error at the end if right before the sub test sub, incantating an end function is required.
after inserting an End Function after the end if the code compiles with
1)Function dblApostrophe(ByVal strInput As String) As Double
2)Sub TESTdblApostrophe()
3)Public Function strSplitAt(strSource As String, strDelimiter As String) As String
4) Sub TESTstrSplitAt()
Slected Sub TESTdblApostrophe() and entered F5 again, tracing the code, nothing much happened, selecting f5 again and highlighted TESTdblApostrophe() with no print outs, but that may have been your debug print assert, changing that to print ran the whole thing again and I got true in the immediate window. No message boxes popped up and that may be because the number "entered" was "113'27.5".
Thus your code works, but how i enter in another "number" instead of "113'27.5"? I mean outside of your code but the number gets executed by the code?

[ChrisGreaves]

I have an older version of excel and all that you described did not work, ...

I have just followed my own nine steps carefully, one by one, in Excel2003.
At which of the steps did you find a problem?
I can help you by first setting up a way of working that WORKS.
We can modify it later on.
I believe that if you follow those nine steps, you will find it behaves exactly as i predicted.

I have no doubt at all that there are more concise methods.
In my younger days (i.e. circa 1998) I might have jumped out to a PC-based version of STSC's APL. But that isn't the way I would present a solution to a VBA problem in an MSVBA forum.
At least, not for beginners.

Please go back and follow my nine steps; if there is a step that you do not understand, or that you think is incorrect, please let me know.
Cheers, Chris

[bknight]

The code did not compile. after adding an end Function it compiled. the code list from top to bottom
1)Function dblApostrophe(ByVal strInput As String) As Double
2)Sub TESTdblApostrophe()
3)Public Function strSplitAt(strSource As String, strDelimiter As String) As String
4) Sub TESTstrSplitAt()
If that should be corrected, please post a correction. The reference to an older version was "In this screen shot I have dragged the TEST subroutine outside its function and I am about to click on the yellow-circled toolbar button."
I don't find any button, toolbar or anything like you imaged. I do however have a Run Dialog on mine.

[ChrisGreaves]

Heh That dates you a bit; ...

Hi SpeakEasy

... as does my nostalgia for holding 80-column punched cards up to the ceiling lights so I could "read" the holes and see the answer!⁽¹⁾

Mind you we don't need Split at all for this if we take a slightly different approach. and know that Access has an Evaluate function.

Quite so. However I was raised to build libraries (of utility functions) the argument being that library development with seriously transportable code was ten to a hundred more times efficient than specialized code. Given a choice between VBA code that will work in any MS application and VBA code that works only in Access, I will always go for the generalized code.

I took a break there to write to my editor/publisher. We are concocting a book about my high school teachers, and that bit

Untitled.png

is straight out of my high school days, my maths teacher Brian Feld, 1962-63. I saw it again in 1971 in a book "Implementing Software for Non-Numeric Applications" by W.M.Waite where he wrote his FORTRAN functions with their own self-testing mechanism. No doubt about that! It speeds up maintenance by about 90%.

In my defence: in this post I preconceded that "This code is far too wordy; I have used interim named variables to make it easier for you to see/understand what is going on."

⁽¹⁾ Nowadays I wonder how well those chads would fare in my tower vermicomposters
Cheers, Chris

[ChrisGreaves]

The code did not compile. after adding an end Function it compiled. the code list from top to bottom
1)Function dblApostrophe(ByVal strInput As String) As Double
2)Sub TESTdblApostrophe()
3)Public Function strSplitAt(strSource As String, strDelimiter As String) As String
4) Sub TESTstrSplitAt()

OK. Where (which step) in my list of steps did I suggest that you should add or remove an 'End Function"?

I am trying to find out where you are straying from the specific steps I laid out.

Untitled2.png

My code does NOT look like the four lines you specified above.
The SUB "TESTdblApostrophe" is commented out; it has a single quote as the first non-blank character; it is coloured green; it is a comment and as such it can not generate a syntax error.

Please go back and follow my steps as laid out.

The text

Code: Select all

'Sub TESTdblApostrophe()
'    Debug.Assert 113.859375 = dblApostrophe("113'27.5")
'End Sub 

is three lines of COMMENTS and in my step(2) I asked that you drag it, all three lines, as is, outside the main procedure and then decomment it. Perhaps I was not clear enough in that second step. Is that the case?
I shall wait for any other member of Eileen's Lounge to confirm that the steps I suggested will produce the results that I spoke of.
cheers, Chris