One Function: Different Formulas for Different Inputs

[Jennifer]

I need to convert typing speeds in various units to other units. The input and output units can be any of:

• WPM = words per minute
• CPS = characters per second
• CPM = characters per minute
• SPW = seconds per word

etc.

Is there any way that I can define a set of expressions in strings with names that define the conversion to be done and then just execute the corresponding string?

For example, the expressions to convert between WPM & CPS are:

CPS = WPM * 5 / 60 = WPM  / 12
WPM = CPS * 12

Can I define 2 constants:

Const WPM2XPS as String = "CPS = WPM  / 12"
Const CPS2WPM as String = "WPM = CPS * 12'

and then if I am asked to do one of these, I just "execute" the appropriate string?

The only alternative that I know of is two Case statements -- one to check the input format and convert the data to some intermediate units and a second one to convert that to the output units.

[mikerickson]

This sounds like it would be best done with functions

Function WPM2CPS(WPM as Double) As Double
    WPM2CMS = WPM * 5 / 60
End Function

Function WPM2CPM(WPM as Double) As Double
    WMP2CPM = WPM * 5
End Function

Function CPS2WPM(CPS As Double) As Double
    CPS2WPM = CPS * 60 / 5
End Function

' etc,

[Jennifer]

OK, but how do I know which function to call? It seems to me that whether I have a set of strings or functions containing the formulas, I still have to know which to "execute". No?

Here's the parent function:

'========================================================================================
'                      Typing Speed Conversion Utility

' Utility to convert between various ways of expressing typing speeds.

'   Syntax: =TypingSpeed(InData, InFmt, OutFmt)

' Parameters:
'   InData  The data to be converted (floating point)
'   InFmt   The format that data is currently in
'   OutFmt  The format the data is to be converted into

'   Both InFmt and OutFmt can be any of: WPM, WPS, CPS, CPM, etc.
'========================================================================================
Public Function TypingSpeed(InData As Double, InFmt As String, OutFmt As String) As Double

Const CPW As Double = 5   'Characters per word

'??? Now what?

TypingSpeed = ???

End Function

I still have to check both InFmt and OutFmt and then decide which function to call...

[mikerickson]

OK, but how do I know which function to call? It seems to me that whether I have a set of strings or functions containing the formulas, I still have to know which to "execute". No?

What do you have, what do you want? You should be aware of that at each step in the code.

Or are you asking how the user of this code will specify which units they are passing to your TypingSpeed function?
It looks like the InFmt and OutFmt should convey that information.

Public Function TypingSpeed(InData As Double, InFmt As String, OutFmt As String) As Double
    Rem in/out formats "wpm", "cpm", "cps", "spw"

    Const CPW As Double = 5

    Select Case LCase(InFmt)
        Case "wpm"
            Select Case LCase(OutFmt)
                Case "wpm"
                    TypingSpeed = inData
                Case "cpm"
                    TypingSpeed = WPM2CPM(inData)
                Case "cps"
                    TypingSpeed = WPM2CPS(inData)
                Case "spw"
                    TypingSpeed = WPM2SPW(inData)
            End Select
        Case "cpm"
            Select Case LCase(OutFmt)
                Case "wpm"
                    TypingSpeed = CPM2WPM(inData)
                Case "cpm"
                    TypingSpeed = inData
                Case "cps"
                    TypingSpeed = CPM2CPS(inData)
                Case "spw"
                    TypingSpeed = CPM2SPW(inData)
            End Select
        Case "cps"
            Select Case LCase(OutFmt)
                ' ...
            End Select
        Case "spa"
            Select Case LCase(OutFmt)
                ' ...
            End Select
    End Select
End Function

[Jennifer]

Yes, I know I can do nested Case statements. I guess I thought that was obvious. The problem with that is that if there are N formats, it will require N! statements. The outer Case command will have N statements and each of those will have its own Case command with N Case statements. At least my other solution only requires 2N statements.

I was hoping for some kind of "Execute" command that would tell VBA to execute a string. Most interpreted languages have something like that.

If VBA had an Execute statement, then I could write:

Public Function TypingSpeed(InData As Double, InFmt As String, OutFmt As String) As Double

Const CPW As Double = 5   'Characters per word
Const ValidFormats As String = "WPM WPS CPM CPS SPW SPC MPW MPC"

' The conversion formulas
Const WPM2WPM As String = "WPM"
Const WPM2WPS As String = "WPM/60"
Const WPM2CPS As String = "WPM/12"
Const WPM2CPM As String = "5*WPM"

If 0 = InStr(1, ValidFormats, UCase(InFmt)) Or _
   0 = OutStr(1, ValidFormats, UCase(InFmt)) Then
  TypingSpeed = CVErr(cverrvalue)
  Exit Function
End If

Execute "TypingSpeed = " & InFmt & "2" & "OutFmt"

End Function

I still need N! conversion strings, but they are all in one place and each to edit.

So, is there anything like the Execute statement?

[Paul_Hossler]

Actually I think it's only (N)(N-1) not N!

With N = 4, that's only 12 permutations, not 24, and with N = that's 56 -- Still a lot

What I started to do was

Option Explicit
Public Function TypingSpeed(InData As Double, InFmt As String, OutFmt As String) As Double
     
    Const CPW As Double = 5 'Characters per word
    Const ValidFormats As String = "WPM WPS CPM CPS SPW SPC MPW MPC"
     
    If InStr(1, ValidFormats, UCase(InFmt)) = 0 Or _
       OutStr(1, ValidFormats, UCase(OutFmt)) = 0 Then
        TypingSpeed = CVErr(xlErrValue)
        Exit Function
    End If
     
    If UCase(InFmt) = UCase(OutFmt) Then
        TypingSpeed = InData
        Exit Function
    End If
     
    Select Case UCase(InFmt & "2" & OutFmt)

        Case "WPM2WPS"
            TypingSpeed = .....

        Case "WPM2CPM"
            TypingSpeed = .....
     
     etc.
     
End Function

[Jennifer]

Actually I think it's only (N)(N-1) not N!

With N = 4, that's only 12 permutations, not 24, and with N = that's 56 -- Still a lot

Ah, good point. I was about to correct you back and say that it is actually N^2 because I want to handle the NOP case where both formats are the same, but then I saw that you handled that case separately. But I still get to correct you to say that it's actually N(N-1)+1!

What I started to do was

Hey, I like that solution. I'll reformat it a bit, something like this:

Select Case UCase(InFmt & "2" & OutFmt)
    Case "WPM2WPS" TypingSpeed = ..... 
    Case "WPM2CPM" TypingSpeed = ..... 
    Case "WPM2CPS" TypingSpeed = ..... 
      . . .

I never know what to do with special cases like InFmt=OutFmt. Your solution simplifies the whole routine, but then that option is missing form the list. And it is probably the least likely combination, but it is tested every time.

Anyway, thanks for a very clever solution. Cheers.

[mikerickson]

Rather than the N! (or N^2-n) approach you could use a 2N approach.

First convert the given figure to a standard format (WPM for this example)

Public Function TypingSpeed(InData As Double, InFmt As String, OutFmt As String) As Double
    Select Case LCase(InFmt)
        Case "cpm"
            InVal = CPM2WPM(InVal)
        Case "wpm"
        Case "cps"
            InVal = CPS2WPM(InVal)
        Case "sps"
            InVal = SPW2WPM(InVal)
    End Select

    ' InVal is now in wpm format

    Select Case LCase(OutFmt)
        Case "cpm"
            TypingSpeed = WPM2CPM(InVal)
        Case "wpm"
            TypingSpeed = InVal
        Case "cps"
            TypingSpeed  = WPM2CPS(InVal)
        Case "sps"
            TypingSpeed  = WPM2SPS(InVal)
    End Select
End Function

Rem in/out formats "wpm", "cpm", "cps", "spw"

[Paul_Hossler]

1. Don't forget the :

Select Case UCase(InFmt & "2" & OutFmt) 
   Case "WPM2WPS":  TypingSpeed = ..... 
   Case "WPM2CPM":  TypingSpeed = ..... 
   Case "WPM2CPS":  TypingSpeed = .....

2. I started with nested Selects but didn't like it; it seems too hard to debug

3. I also tried converting In and Out to 'standard' units and calculating from there but felt it was too obscure.

Finally decided to Keep It Simple Self (KISS) and just spell out the various permutations in a Select with straight forward calculations

Personal preference

[Jennifer]

Rather than the N! (or N^2-n) approach you could use a 2N approach.

First convert the given figure to a standard format (WPM for this example)

Yep, I mentioned that possibility in my original post. Thanks, -jm

[Jennifer]

1. Don't forget the :

Select Case UCase(InFmt & "2" & OutFmt) 
   Case "WPM2WPS":  TypingSpeed = ..... 
   Case "WPM2CPM":  TypingSpeed = ..... 
   Case "WPM2CPS":  TypingSpeed = .....

I can never remember when they are needed and when not. I just code it up. If the statement stays red, I adjust until it's happy.

2. I started with nested Selects but didn't like it; it seems too hard to debug

Totally. And it's a mess to maintain. There's no say to get everything on one screen.

3. I also tried converting In and Out to 'standard' units and calculating from there but felt it was too obscure.

I think you mean converting In to some intermediate form, then converting that to Out. I don't find it, too obscure, but, it's not nearly as good as your solution.

Finally decided to Keep It Simple Self (KISS) and just spell out the various permutations in a Select with straight forward calculations Personal preference

Completely agree, but when I learned KISS, the second "s" had a different, more pointed, meaning.

[snb]

You can easily produce all unique combinations of 8 elements using:

Sub M_snb()
   sn = [if(row(1:8)<transpose(row(1:8)),choose(row(1:8),"WPM","WPS","CPM","CPS","SPW","SPC","MPW","MPC") & choose(transpose(row(1:8)),"WPM","WPS","CPM","CPS","SPW","SPC","MPW","MPC"),"")]
   Cells(20, 1).Resize(8, 8) = sn
End Sub

NB. The writing into the sheet is only meant as illustration of the content of Array variable sn.

BTW. Yes VBA can do things in strings, but using variables is much more comfortable.

MsgBox Evaluate("12*300 & ""  Euro""")

[Paul_Hossler]

Completely agree, but when I learned KISS, the second "s" had a different, more pointed, meaning.

Yes, but I've become more "PC" as I get older

[SamT]

Rearranging Paul's sub

Public Function TypingSpeed(InData As Double, InFmt As String, OutFmt As String) As Double 
     
    Const CPW As Double = 5 'Characters per word
    Const ValidFormats As String = "WPM WPS CPM CPS SPW SPC MPW MPC" 
     
    If InStr(1, ValidFormats, UCase(InFmt)) = 0 Or _ 
    OutStr(1, ValidFormats, UCase(OutFmt)) = 0 Then

Like this

Public Function TypingSpeed(InFmt As String, OutFmt As String, _
InData As Double, Optional Duration As Double) As Double 
     
    Const CPW As Double = 5 'Characters per word
    Const ValidFormats As String = "WPM WPS CPM CPS SPW SPC MPW MPC" 


''''''''''''''''''''''''''''''''''''''''''''''''''''
If not IsEmpty(Duration) Then
Select Case UCase(InFmt)
Case "WPM":     TypingSpeed = InData / Duration           'InData in Words / Duration in Minutes
Case "WPS":     TypingSpeed = InData / (Duration / 60)   'InData in Words / Duration in Seconds
Case "CPM":     TypingSpeed =      'InData in Characters   Duration in Minutes
Case "CPS":     TypingSpeed =      'InData in Characters   Duration in Seconds
End Select
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''

    If InStr(1, ValidFormats, UCase(InFmt)) = 0 Or _ 
    OutStr(1, ValidFormats, UCase(OutFmt)) = 0 Then

The Scenario: You have a value for InData over a random Time period and want to calculate it to one of the four standard formats

X = TypingSpeed("WPM", "CPS", TypingSpeed("WpM", "Any", 942, 19))

The Inner Call, (With a Duration Value) Converts 942 words over 19 minutes to a standard WPM value.When a Duration Value is present, the Outfmt is ignored. The Outer Call converts this WPM value to a CPS value.

[Jennifer]

I've done quite a bit of testing and I think I have the function working. I decided against writing 64 (8x8) explicit conversion formulas. Instead, I chose to convert everything to WPM as an intermediate value and then convert that to the out units. (PS: I changed "Fmt" to "Units" everywhere.) That's only 2N (16) expressions.

Then I decided to add 2 special cases. That reduces the number of executed tests by 16-2.

In case anyone cares, here's the final code:

'========================================================================================
'                      Typing Speed Conversion Utility


' Utility to convert between various ways of expressing typing speeds.


'   Syntax: =TypingSpeed(InData, InUnits, OutUnits)


' Parameters:
'   InData    The data to be converted (floating point)
'   InUnits   The units that data is currently in
'   OutUnits  The units the data is to be converted into


'   Both InUnits and OutUnits can be any of the units listed below.


' Data Units:
'   WPM   Words per Minute
'   WPS   Words per Second
'   CPS   Characters per Second
'   CPM   Characters per Minute
'   SPW   Seconds per Word
'   SPC   Seconds per Character
'   MPW   Minutes per Word
'   MPC   Minutes per Character


' There are 8 units. That's N^2 (64) conversions. Rather than 64 explicit expressions,
' I chose to convert everything to common units (WPM) and then convert that to the
' output units. That's just 2N (16) statements.
' I then made a special case for when InUnits=OutUnits. It's probably a low frequency case,
' but it was easy to test for and it avoids a double conversion in 7 of 8 cases.
' I then made a special case for when InUnits=Reverse(OutUnits). For these 8 cases,
' the result is just the reciprocal (1/InData). This avoids several arithmetic operations.


'   Change Log
' 02/19/17  Created
' 02/26/17  Tested (See WPM to CPS etc.xlsx)
'========================================================================================
Public Function TypingSpeed(InData As Double, InUnits As String, OutUnits As String) As Double


Const ValidFormats As String = "WPM WPS CPM CPS SPW SPC MPW MPC"
Const CPW As Double = 5   'Characters per Word
Const SPM As Double = 60  'Seconds per Minute
Dim WPM As Double         'The intermediate value, convert everything to WPM


InUnits = UCase(InUnits): OutUnits = UCase(OutUnits)


If 0 = InStr(1, ValidFormats, InUnits) Or _
   0 = InStr(1, ValidFormats, OutUnits) Then
  TypingSpeed = CVErr(xlErrValue)
  Exit Function
End If


'If the formats are the same, no conversion is needed
If InUnits = OutUnits Then: TypingSpeed = InData: Exit Function


'If the formats are the reversed, it's the reciprocal
If InUnits = StrReverse(OutUnits) Then: TypingSpeed = 1 / InData: Exit Function


Select Case UCase(InUnits)          'First convert everything to WPM
  Case "WPM": WPM = InData              'WPM to WPM
  Case "MPW": WPM = 1 / InData          'MPW to WPM


  Case "WPS": WPM = InData * SPM        'WPS to WPM
  Case "SPW": WPM = SPM / InData        'SPW to WPM


  Case "CPS": WPM = InData * SPM / CPW  'CPS to WPM
  Case "SPC": WPM = SPM / InData / CPW  'SPC to WPM


  Case "CPM": WPM = InData / CPW        'CPM to WPM
  Case "MPC": WPM = 1 / InData / CPW    'WPM to CPM
End Select


Select Case UCase(OutUnits)         'Then convert WPM to the final units
  Case "WPM": TypingSpeed = WPM                 'WPM to WPM
  Case "MPW": TypingSpeed = 1 / WPM             'WPM to MPW
  
  Case "WPS": TypingSpeed = WPM / SPM           'WPM to WPS
  Case "SPW": TypingSpeed = SPM / WPM           'WPM to SPW


  Case "CPS": TypingSpeed = (WPM * CPW) / SPM   'WPM to CPS
  Case "SPC": TypingSpeed = SPM / (WPM * CPW)   'WPM to SPC


  Case "CPM": TypingSpeed = WPM * CPW           'WPM to CPM
  Case "MPC": TypingSpeed = 1 / (WPM * CPW)     'WPM to MPC
End Select


End Function

Let me know if you find any bugs or better ways to do anything.