Question

Use VBA to create a table of in Excel for 0 < x lessthanorequalto 10 incrementing x by 0.1.

Answer 1

Sub CreateTable()

   

    Dim wsTest As Worksheet

   

    '''Sheet Name

    SheetName = "Table"

    Set wsTest = Nothing

   

    On Error Resume Next

    Set wsTest = ActiveWorkbook.Worksheets(SheetName)

    On Error GoTo 0

    

    If wsTest Is Nothing Then

        Worksheets.Add.Name = SheetName   '''If sheet is not exist

    End If

    Sheets(SheetName).Select

   

    '''Columns Names

    Range("A1").Value = "x"

    Range("B1").Value = "y=x^-2"

    Range("C1").Value = "y=x^-1"

    Range("D1").Value = "y=x^0"

    Range("E1").Value = "y=x^1"

    Range("F1").Value = "y=x^2"

    Range("G1").Value = "y=x^3"

   

    x = 0

    Row = 2

    Do While x <= 10

        If x = 0 Then

            x = x + 0.1

        End If

       

        '''Formulas

        Cells(Row, 1).Value = x

        Cells(Row, 2).Value = x ^ -2

        Cells(Row, 3).Value = x ^ -1

        Cells(Row, 4).Value = x ^ 0

        Cells(Row, 5).Value = x ^ 1

        Cells(Row, 6).Value = x ^ 2

        Cells(Row, 7).Value = x ^ 3

        Row = Row + 1

        x = x + 0.1

    Loop

   

    '''Draw Borders

    Range("A1").Select

    Range(Selection, Selection.End(xlDown)).Select

    Range(Selection, Selection.End(xlToRight)).Select

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone

    Selection.Borders(xlDiagonalUp).LineStyle = xlNone

    With Selection.Borders(xlEdgeLeft)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeTop)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeBottom)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlEdgeRight)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideVertical)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

    With Selection.Borders(xlInsideHorizontal)

        .LineStyle = xlContinuous

        .ColorIndex = 0

        .TintAndShade = 0

        .Weight = xlThin

    End With

   

    '''Heading in Bold

    Range("A1:G1").Select

    Selection.Font.Bold = True

    ActiveWorkbook.Save

    MsgBox ("Done")

   

End Sub