VBA Express : Multiple Apps - Functions for converting periodic values to their equivalents in their base period.

Multiple Apps

Functions for converting periodic values to their equivalents in their base period.

Ease of Use

Easy

Version tested with

2000

Submitted by:

MWE

Description:

Functions for converting periodic values to their equivalents in their base period.

Discussion:

Many engineering and scientific applications perform computations requiring that periodic values be converted to their base period. This may be only for display (so the user can more readily understand results), but may also be for those procedures that require values in base periods. Two obvious examples are [0,360] for angles in degrees and [0,2Pi] for angles in radians. If the base period start (X1) and end (X2) are integers and the target value (Xi) is an integer, and all values are positive, then the VB/VBA mod operator will yield the desired value (Xb) in the base period: Xb = Xi mod (X2-X1) However, if X1, X2 or Xi is a floating point or any value is negative, the results are probably bogus. The function BasePeriodVal will convert any periodic value to an equivalent value in its base period. Because the angular examples above are so common, the functions Zeroto360 and Zeroto2Pi are also provided. These functions will work for any VB/VBA application. The demonstration is Excel based.

Code:

instructions for use

			Option Explicit 
 
Function ZeroTo2Pi(Rad) As Double 
     '
     '       Function    converts any radian value to an equivalent value in the
     '                   range [0 , 2Pi]
     '       Passed Values:
     '           Rad       [in, numeric]  radian value to be converted
     '
     
    ZeroTo2Pi = BasePeriodVal(0#, 6.28318530717959, Rad) 
     
End Function 
 
Function ZeroTo360(Deg) As Double 
     '
     '       Function    converts any deg value to an equivalent value in the
     '                   range [0 , 360]
     '       Passed Values:
     '           Rad       [in, numeric]  deg value to be converted
     '
     
    ZeroTo360 = BasePeriodVal(0#, 360#, Deg) 
     
End Function 
 
Function BasePeriodVal(X, Y, Val) As Double 
     '
     '       Function    converts any periodic value (Val) to an equivalent value in its
     '                   base period [X , Y]
     '           NOTE:   if X, Y and Val are all integer (or longs), the VBA expression
     '                   Val mod (Y-X) will produce the same result as BasePeriodVal and is more
     '                   efficient.  Attempting to use the (VB/VBA) mod operator with
     '                   floating point numbers will yield bogus results
     '       Passed Values:
     '           X       [in, numeric]   beginning of range
     '           Y       [in, numeric]   end of range
     '           Val     [in, numeric]   value to be converted
     '
    Dim XY As Double 
     
    XY = Y - X 
    BasePeriodVal = Val 
TestVal: 
    If BasePeriodVal < X Then 
        BasePeriodVal = BasePeriodVal + XY 
        Goto TestVal 
    Else 
        If BasePeriodVal >= Y Then 
            BasePeriodVal = BasePeriodVal - XY 
            Goto TestVal 
        End If 
    End If 
     
End Function 
 
Sub BasePeriod_Test() 
     '
     '       Demonstration:  interacts with use to demonstrate base period conversion functions
     '
    Dim Ans     As String 
    Dim I       As Integer 
    Dim Title   As String 
    Dim X       As Single 
    Dim X1      As Single 
    Dim X2      As Single 
    Dim Xi      As Single 
     
    Title = "Test of base period conversion functions" 
     
GetInitial: 
    Ans = InputBox("enter test #:" & vbCrLf & _ 
    "1  convert degrees to [0 , 360]" & vbCrLf & _ 
    "2  convert radians to [0 , 2PI]" & vbCrLf & _ 
    "3  convert any period value to base period equivalent" & vbCrLf & vbCrLf & _ 
    "[enter nothing or click on Cancel to quit]", Title) 
    If Ans = "" Then Exit Sub 
    I = Ans 
    Select Case I 
    Case Is = 1 
        GetCase1:   Ans = InputBox("value in degrees ?", "Demo of ZeroTo360") 
        If Ans = "" Then Goto GetInitial 
        X = Ans 
        MsgBox X & " degrees = " & Format(ZeroTo360(X), "##0.###") & " degrees", _ 
        vbInformation, Title 
        Goto GetCase1 
    Case Is = 2 
        GetCase2:   Ans = InputBox("value in radians ?", "Demo of ZeroTo2Pi") 
        If Ans = "" Then Goto GetInitial 
        X = Ans 
        MsgBox X & " radians = " & Format(ZeroTo2Pi(X), "##0.###") & " radians", _ 
        vbInformation, Title 
        Goto GetCase2 
    Case Is = 3 
        GetCase3x1: Ans = InputBox("start of base period ?", "Demo of BasePeriodVal") 
        If Ans = "" Then Goto GetInitial 
        X1 = Ans 
        GetCase3x2: Ans = InputBox("end of base period ?" & vbCrLf & _ 
        "start of base period = " & X1, "Demo of BasePeriodVal") 
        If Ans = "" Then Goto GetCase3x1 
        X2 = Ans 
        GetCase3Xi: Ans = InputBox("target value ?" & vbCrLf & _ 
        "base period = [ " & X1 & " , " & X2 & " ]", "Demo of BasePeriodVal") 
        If Ans = "" Then Goto GetCase3x2 
        Xi = Ans 
        MsgBox "start of base period = " & X1 & vbCrLf & _ 
        "end of base period = " & X2 & vbCrLf & _ 
        "target value = " & Xi & vbCrLf & _ 
        "resulting value in base period = " & _ 
        Format(BasePeriodVal(X1, X2, Xi), "##0.###"), _ 
        vbInformation, Title 
        Goto GetCase3Xi 
    End Select 
    Goto GetInitial 
     
End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, hi-lite the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
See ?Test The Code? below

Test the code:

Open the example
The example contains a testing procedure, and the conversion functions. The test procedure prompts for what the user wants to test, then prompts for test values and finally displays results. After each cycle, the procedure cycles back and reprompts for "most recent" input. Entering nothing or clicking on Cancel ?backs up? the test procedure one level.
NOTE: these procedures will ultimately be called by some parent procedure or application. Thus final testing will depend on how the procs are to be used.

Sample File:

BasePeriodVal.zip 24.95KB

Approved by mdmackillop

This entry has been viewed 39 times.