PDA

View Full Version : [SOLVED] One Function: Different Formulas for Different Inputs



Jennifer
02-19-2017, 07:59 PM
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
02-19-2017, 08:30 PM
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
02-20-2017, 08:46 AM
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
02-20-2017, 01:53 PM
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
02-20-2017, 03:09 PM
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
02-20-2017, 05:29 PM
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
02-20-2017, 06:39 PM
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! :yes


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. :thumb :beerchug:

mikerickson
02-20-2017, 06:53 PM
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
02-20-2017, 07:48 PM
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
02-20-2017, 07:49 PM
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
02-20-2017, 08:14 PM
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. : pray2:


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. :think:

snb
02-21-2017, 01:27 AM
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
02-21-2017, 07:29 AM
Completely agree, but when I learned KISS, the second "s" had a different, more pointed, meaning. :think:


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

SamT
02-21-2017, 10:22 PM
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
02-26-2017, 12:40 PM
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.