xlf EandA series

Array power 2

0. Preliminary

The EXERCISE - complete the following tasks

Task 1

Worksheet setup

  • Tab name: Sheet1
  • B3: 2, C3: 5
  • B4: 3, C4: 6
  • B5: 4, C5: 7

1.1 Write a user defined function (UDF) named ArrPwr2 that takes one argument, a range object, and returns the square (^ 2) of the argument as an array. The function will be called from the worksheet as an array formula (CSE). Assume that the values in the array object are valid numbers.

1.2 Write a test procedure, named TestArrPwr2 to pass the Sheet1!B3:C5 array to the ArrPwr2 UDF, and assign the return value to a variable named Ans of type Variant

Task 2

Requires completion of task 1. Add a label Data1 to Sheet1!B2

Write a sub procedure with the following specifications:

  1. Procedure name: Power2
  2. Procedure scope (explicit): Public
  3. Invoke the Application.Inputbox Method with syntax .InputBox( Prompt , Title , Default , Left , Top , HelpFile , HelpContextID , Type )
  4. Include suitable values for the Prompt, and Title arguments
  5. The Default argument is the CurrentRegion (CR) excluding the header row, conditional on the CR containing all numbers. A single cell <CurrentRegion is permitted
  6. Process the Range with the ArrPwr2 function
  7. Display a second Inputbox to send the output to the range chosen by the User. Allow for the user selecting a multi-cell target of the wrong dimension
  8. Code the Cancel button for each InputBox



Task 1.1: ArrPwr2

Code 1: the ArrPwr2 function interpretation of task 1.1
Option Explicit

'' Solution file to xlf | exercise - range object - array udf - inputbox
'' Source: http://excelatfinance.com/online/topic/xlf-exercise-range-object-array-udf-inputbox/

'' ================================
'' Task 1
'' ================================

Function ArrPwr2(Arr As Range) As Variant
Dim Pwr2Arr() As Double
Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim j As Integer

    r = Arr.Rows.Count
    c = Arr.Columns.Count
    ReDim Pwr2Arr(1 To r, 1 To c)   ' Multi dimensional array

    For i = 1 To r
        For j = 1 To c
            Pwr2Arr(i, j) = Arr(i, j) ^ 2
        Next j
    Next i

    ArrPwr2 = Pwr2Arr

End Function

Task 1.2: TestArrPwr2

Code 2: TestP2R macro
Private Sub TestArrPwr2()
Dim Ans As Variant

    Ans = ArrPwr2(Range("Sheet1!B3:C5"))

Stop    ' Allow access to Locals Window
End Sub

Task 2: Power2

Code 3: Power2 macro
 '' ================================
'' Task 2
'' ================================

Public Sub Power2()
Dim inRng As Range, outRng As Range
Dim DataSq() As Double
Dim Prompt1 As String, Prompt2 As String, Title As String, Default As String
Dim CR As Range, CRr As Integer, CRc As Integer
Dim i As Integer, j As Integer
Dim WSF As WorksheetFunction

    Title = "xlf Square Converter [^2]"
    Prompt1 = "Select range to square - numbers only"
    Prompt2 = "Select range for output"

    Set CR = ActiveCell.CurrentRegion
    Set WSF = WorksheetFunction

    CRr = CR.Rows.Count
    CRc = CR.Columns.Count

    ' Process Header row and Data row(s)
        If WSF.IsText(CR(1, 1)) And WSF.IsNumber(CR(2, 1)) Then
            Set CR = CR.Offset(1, 0).Resize(CRr - 1, CRc)
        End If

        If WSF.Count(CR) = CR.Cells.Count And WSF.Count(CR) >= 1 Then
            Default = CR.Address
        End If

    ' Display InputBox method - 1
        On Error Resume Next
Box1:   Set inRng = Application.InputBox(Prompt:=Prompt1, Title:=Title, Default:=Default, Type:=8)
            If inRng Is Nothing Then Exit Sub
        On Error GoTo 0

    ' Calculate squared values
        DataSq = ArrPwr2(inRng) ' DataSq inherits the dimension of inRng

    ' Display InputBox method - 2
        On Error Resume Next
        Set outRng = Application.InputBox(Prompt:=Prompt2, Title:=Title, Type:=8)
            If outRng Is Nothing Then GoTo Box1
        On Error GoTo 0

    ' Write output to WS
            Set outRng = outRng.Resize(1, 1)
            For i = 1 To UBound(DataSq, 1)
                For j = 1 To UBound(DataSq, 2)
                    outRng(i, j).Value = DataSq(i, j)
                Next j
            Next i

End Sub