Question

For VBA:

Complete this problem on Sheet1.

The user will select a cell on Sheet1 and input a positive number (you do not know which cell will be selected ahead of time). This cell automatically becomes the active cell. Next, create a macro named problem1 that will perform the following tasks:

• Obtain the value in the active cell and store it in a variable.

• In the cell to the right of the original active cell, output 5 plus the value in the original active cell

• In the cell above the original active cell, output one-half the value in the original active cell

• In the cell below the original active cell, output the square root of the value in the original active cell

Create a run button on Sheet1 that executes problem1.

Your macro must work for any positive number input into any cell. For example, if you type 36 in cell B3 and then click the run button, the number 41 will appear in C3, the number 18 will appear in B2, and the number 6 will appear in B4. Then if you click on cell D5, type 25, and click the run button, the number 30 will appear in E5, the number 12.5 will appear in D4, and the number 5 will appear in D6.

You can assume the user will not input the value in the first or last row, nor the last column (Think about what will happen if the value is input into one of those cells. Or better yet, try doing it and see what happens.).

Assume the user will input a value in the active cell before clicking the run button.

Use the Option Explicit statement for this macro and in all future macros that you create. This will force you to declare your variables.

Hint: When the run button is clicked, whatever cell is selected at that time automatically becomes the active cell. You can move to the right, above, and below the active cell by using relative references in your program. This will require you to use ActiveCell.Offset() three times.

PLEASE take screenshots of VBA code and excel. Thank you

Answer 1

The VBA Code (in text) is:

Option Explicit
'Option Explicit would mean all variables need to be declared as Dim

Sub Add() 'Make the sub routine and name it
Dim x As Integer 'Declare the variable
x = ActiveCell.Value 'Store the Value of the Active Cell in the variable
ActiveCell.Offset(0, 1).Value = x + 5 'Offset the Active Cell by 1 column to the right and store x+5 in it
ActiveCell.Offset(-1, 0).Value = x / 2 'Offset the Active Cell by 1 row above and store x/2 in it
ActiveCell.Offset(1, 0).Value = x ^ 0.5 'Offset the Active Cell by 1 row below and store square root of x in it
End Sub 'End the sub routine

The VBA Code (screenshot) is:

To make a macro:

Step 1.

Step 2:

Step 3. Assign Macro to a Button:

The file is now ready. Enter a number in a cell and click on the box/button while the cell is selected.

The Output looks like this:

An up-vote/thumbs-up would be greatly appreciated!