Question

Homework 06b For...Next Statements The elements of a Hilbert matrix are given by the formula bu=1/(i+j-1), where į and j are the row and column indices of the matrix. For example, the 5x5 Hilbert matrix is: [ 1 172 1/3 1/4 175 172 173 174 175 176 173 174 175 176 177 174 175 176 177 178 175 176 177 178 179 Write a Sub that writes the elements of a mxm Hilbert matrix on an Excel spreadsheet, where m is the number of row and columns in the square matrix. The user should be able to input the value of m. You will need to use nested For...Next statements to accomplish this. The output of your program should look something like the matrix in the figure below. Home Page Layout Formulas Dute Review Vio Fle A To me what you Developer - Record Macro Uwe Heative Reference Properties e Expansion Pos mport t ir COM Addin Ali Vw Code Run Dog - Made K L M N O P R S 1/10 VI 1/12 10 Hint: To get Excel to display the elements of the matrix as fractions, use Format Cells/Number/Fraction/Up to three digits to modify the formatting of the of the matrix. You can use the Macro Recorder to find the VBA statements to format the cells.

Answer 1

Use below code to update the excel sheet with Hilbert matrix in VBA:

Sub PrintHilbertMatrix()

'

' PrintHilbertMatrix Macro

'

'

Dim m As Integer

Dim denominator As Integer

'take the size of the matrix

m = InputBox("Enter size of the matrix")

'select the excel sheet

Application.Workbooks("Matrix.xlsm").Worksheets("Hilbert").Select

    'loop to print hilbert matrix

    For i = 1 To m

        For j = 1 To m

            Cells(j, i).NumberFormat = "# ???/???" 'to format the cell to show fraction till 3 digits

            denominator = (i + j - 1)

           

            Cells(j, i).Value = 1 / denominator

           

        Next

    Next

End Sub

================================screen shot===============================

Microsoft Visual Basic for Applications - Matrix.xlsm - [Module 1 (Code)] Eile Edit View Insert Format Debug Run Tools Add-Ins Window Help 23 % I K * ** In 26, Col 1 Project - VBAProject (General) Sub PrintHilbertMatrix() PrintHilbertMatrix ' PrintHilbertMatrix Macro - BVBAProject (Matrix.xlsm) - Microsoft Excel Objects Sheet1 (Hilbert) ThisWorkbook Modules Module1 Dim m As Integer Dim denominator As Integer 'take the size of the matrix m = InputBox ("Enter size of the matrix") 'select the excel sheet Application.Workbooks ("Matrix.xlsm").Worksheets ("Hilbert").Select 'loop to print hilbert matrix For i = 1 Tom For j = 1 Tom Cells (j, i).NumberFormat = "# ???/???" 'to format the cell to show fraction till 3 digits denominator = (i + j - 1) Cells (j, i).Value = 1 / denominator Properties - Module 1 Module1 Module Alphabetic Categorized (Name) Module1 Next Next End Sub

Output:

Matrix - Excel Abhishek Varshney AV AutoSave Off Hydro File Home Insert Page Layout Formulas Data Review View # ✓ Ruler Ruler u Formula Bar ✓ Formula Bar a Lido la L 100 Jormal Page Break Page Custom ✓ Gridlines Headings Zoom 100% Zoom to Preview Layout Views Selection ST U Help Team E New Arrange Freeze Window All Panes O Search Split - View Side by Side Hide D[ Synchronous Scrolling Unhide Reset Window Position Windows Y o Switch Macros Workbook Views Show Zoom Window Macros Microsoft Excel - A B C D E F I N O P Enter size of the matrix OK Cancel m st 1 m Hilbert

Click on ok.

AutoSave off Havero File Home Insert Page Layout A Ruler Normal Page Break Page Custom ✓ Gridlines Preview Layout Views H Formulas Data Review Formula Bar Q Lide 100 Zoom 100% Zo Headings Se Workbook Views Show Zoom c3 - : X fx 0.2 - A E F G 1 1 1/5 B 1/2 1/3 1/4 1/2 1/3 1/4 1/5 C 1/3 1/4 1/5 1/6 1/7 D 1/4 1/5 1/6 1/7 1/8 1/6 1/7 1/8 1/9 1/5 1/6 Hilbert

Please rate answer. Thanks