' Create this macro sheet via |Insert|Macro|Module|
' In newer versions of Excel, go through |Tools|Macro|

'-----------------------------------------------------------------------
      Sub demo()
'-----------------------------------------------------------------------
' This subroutine demonstrates how to pass cell content between the worksheet
' and the macro module, and creates a plot.
' Instructor: Nam Sun Wang
'-----------------------------------------------------------------------
     
'-----------------------------------------------------------------------
' Program header usually goes here.
' Demonstrate how to output a message.  The general syntax is:
'     response = MsgBox(prompt, [Style], [Title, [Help_file, Ctxt])
' See on-line help on MsgBox via |View|Object Browser|Interaction|VBA|MsgBox|?|
'   or mark the text "MsgBox" and press F1.  The square brackets indicate that
'   the argument is optional.
      Call MsgBox("This subroutine demonstrates some I/O features.")
      
'-----------------------------------------------------------------------
' "Cells" is an object that has many attributes/properties, one of which
'   being "value", other attributes being Font, Font.Size, Font.Color,
'   Format, Width, color, size, format, etc.  The default is "value".
' See a (very long) list of properties by marking properties and press F1
' Each cell is addressed as a matrix with (row, column), which is the
'   opposite of the column-row convention normally used in a spreadsheet,
'   e.g., "A1".
' Send a label or a number to a cell
      Cells(4, 1).Value = "x"
      Cells(4, 2).Value = 1234
      Cells(5, 1) = "y="     'The default is ".value"; thus, it is not needed.
      Cells(5, 2) = 1234

'-----------------------------------------------------------------------
' Receive a label or a number from a cell
'     Dim abc As String        ' Declaration is optional.
      abc = Cells(4, 1)
      abc = abc & "="          ' concatenate
      Cells(4, 1) = abc
      x = Cells(4, 2)
      Cells(4, 2) = x + 1

'-----------------------------------------------------------------------
' Send a formula to a cell
      Cells(6, 1) = "=sum(B4:B5)"
      Cells(6, 2).Formula = "=Sum(B4:B5)"

'-----------------------------------------------------------------------
' Refer a cell with the "A1" format; do not forget the brackets.
' (Not preferred in programming because cell addresses have to be manipulated
' with string functions and because the expressions are specific to Excel.
      [A7] = "x+1="
      x = [B4]
      [B7] = x + 1
      
'-----------------------------------------------------------------------
' Work on a range of cells
      Range(Cells(9, 1), Cells(11, 2)) = Null   'Clear the cells
      Range(Cells(9, 1), Cells(11, 2)).Font.Color = RGB(0, 0, 255) 'Blue color
      Range(Cells(9, 1), Cells(11, 2)).Font.Bold = 1 'Set bold face
      Range(Cells(9, 1), Cells(11, 1)).Font.Name = "Courier" 'Set font name
      Range(Cells(9, 1), Cells(11, 1)).Font.Size = 6 'Set Font size
      x = InputBox("Fill cells with: ")
      Range(Cells(9, 1), Cells(11, 2)) = x   'Fill the cells with the same value

'-----------------------------------------------------------------------
' Assign a range of cells to a variable name (i.e., a vector)
      y = Range(Cells(4, 2), Cells(6, 2))
' Output a range of cells with different values
      Range(Cells(9, 5), Cells(11, 5)) = y

' The following is invalid because we can assign only to an array element,
' not an array.
'     Range(Cells(9, 5), Cells(11, 5)) = y + 1
' When a variable is dimensioned with an explicit type other than "Variant",
' the meaning is a bit different.  For example, after dimensioning y, the
' following refers to the array element y(0) only, not the entire array.
' The default base in Basic is 0; change with "Option Base 1"
'     Dim y(100) as Single
'     Range(Cells(9, 5), Cells(11, 5)) = y
' However, a non-dimensioned variable (which by default is a "Variant" type)
' is ok; this provides an easy way of working with vectors/matrices.
' However, as mentioned above, certain manipulations e.g., "y+1" does not
' work, and we still need to manipulate the array in an element-wise manner.
      
'-----------------------------------------------------------------------
' Work on a range of cells with the "A1" format.
      [C9:C11] = Null                        'Clear the cells
      x = InputBox("Fill cells with: ")
      [C9:C11] = x                           'Fill the cells with the same value
      y = [B4:B6]                            'Input a range of cells
      [F9:F11] = y                           'Output a range of cells with different values

'-----------------------------------------------------------------------
' Another way of working with a range
      y = Range("B4:B6")
      Range("G9:G11") = y

'-----------------------------------------------------------------------
' Work on a noncontiguous set of cells with "Union" (equivalent to Ctrl-Click)
      y = Union(Range("B4:B5"), [B7])
      Range("H9:H11") = y
      
'-----------------------------------------------------------------------
' Work with an array/vector/matrix with ".FormulaArray", not just "Formula".
' First identify a range of cells where the formula goes.  Then issue the
' formula.
      Range("I9:J10").Select
      Selection.FormulaArray = "=TRANSPOSE(A9:B10)"
' Any one of the ranging methods will do.  Here is an equivalent one.
      Range(Cells(9, 9), Cells(10, 10)).Select
      Selection.FormulaArray = "=TRANSPOSE(A9:B10)"
' However, an Excel worksheet does not recognize "Cells(1,1)", although
' Visual Basic understands it.  Hence, the following is no good.
'     Selection.FormulaArray = "=TRANSPOSE(Range(Cells(9,1),Cells(10,2)))"
' Conversely, Visual Basic does not recognize "TRANSPOSE", although Excel
' does.  Visual Basic complains about an invalid function/sub.  Hence, the
' following is no good.
'     Selection = Transpose(Range(Cells(9,1),Cells(10,2)))
' Lesson: EXCEL FUNCTIONS & VISUAL BASIC FUNCTIONS ARE NOT THE SAME!
     
'-----------------------------------------------------------------------
' For-loop
      For i = 9 To 11
        Cells(i, 4) = x + i
      Next i

'-----------------------------------------------------------------------
' Demonstrate how to input a number of a text string.  The general syntax is:
'   InputBox(Prompt, Title, Default, XPos, YPos, HelpFile, Context)
' An abbreviated example is given above in reading x and another one is shown
'   below.
      response = InputBox("Want to continue (Y/n)?", "This is a demo", "Y")
      If response = "n" Or response = "N" Then Exit Sub
      Call MsgBox("You have entered '" & response & "'" & Chr(10) & Chr(13) & "We are about to read from a file")
    
'-----------------------------------------------------------------------
' Excel's Visual Basic does not support I/O with screen and keyboard.
' Thus, the following Basic statements are invalid within Excel.
'     Input x       ... use InputBox
'     Print x       ... use MsgBox
' However, I/O from a file is o.k. (Make sure that Ctrl-Z is not present
' in a file; otherwise, we get an error.  Also make sure that the present
' directory kept by Excel is where the file "steam.dat" is located.)
      Close #1: Beep: Beep: Beep     ' Three beeps signal the beginning.
      Cells(17, 1) = "Temperature": Cells(17, 2) = "Pressure"
      Open "steam.dat" For Input As #1
      i = 0
      Dim temperature(200), pressure(200) As Single
      Do Until EOF(1)
        i = i + 1
        Input #1, temperature(i), pressure(i)
        Cells(17 + i, 1).NumberFormat = "0.00"
        Cells(17 + i, 2).NumberFormat = "0.00"
        Cells(17 + i, 1) = temperature(i): Cells(17 + i, 2) = pressure(i)
      Loop
      Close #1: Beep: Beep: Beep     ' Another three beeps signal the end.
      ipt = i
      
'-----------------------------------------------------------------------
' Add a few drawing objects
      ActiveSheet.Ovals.add 0, 0, 50, 50
      ActiveSheet.Rectangles.add 30, 30, 50, 100

'-----------------------------------------------------------------------
' This section demonstrates how to plot a x-y graph.
' Add a chart object on the current worksheet and select it.
' Syntax: (An easy way is to record the action with |Tools|Record Macro|)
'     ActiveSheet.ChartObjects.add(x_offset, y_offset, width, height).Select
      ActiveSheet.ChartObjects.add(150, 250, 300, 200).Select
' which is equivalent to the following two lines:
'     ActiveSheet.ChartObjects.add 150, 150, 300, 200
'     ActiveSheet.ChartObjects.Select
' See a list of arguments via |View|Object Brower|Excel|Chart|ChartWizard|
'  Format = 1 (squares); 2 (squares & line); 3 (squares); 4 (semilog,squares);
'           5 (log-log,squares); 6 (line)
' Continue onto the next line with "_"
      ActiveChart.ChartWizard _
        Source:=Range("A17:B208"), _
        Gallery:=xlXYScatter, Format:=1, _
        PlotBy:=xlColumns, _
        CategoryLabels:=True, SeriesLabels:=True, HasLegend:=True, _
        Title:="Vapor Pressure", _
        CategoryTitle:="Temperature (F)", _
        ValueTitle:="Pressure (psia)", _
        ExtraTitle:=""
' Modify the active chart a little bit
      With ActiveChart
' x-axis
        With .Axes(xlCategory)
          .MinimumScale = 0
          .MaximumScale = 800
          .MajorUnit = 200
          .TickLabels.NumberFormat = "0"
        End With
' y-axis
        With .Axes(xlValue)
          .MinimumScale = 0
          .MaximumScale = 4000
          .MajorUnit = 1000
          .TickLabels.NumberFormat = "0"
        End With
    End With

'-----------------------------------------------------------------------
' Demonstrates how to plot on a new worksheet -- with ChartWizard.
' Add a chart sheet
      Charts.add
' The rest is the same as above except that "Source" points to sheet1
      ActiveChart.ChartWizard _
        Source:=Sheets("Sheet1").Range("A17:B208"), _
        Gallery:=xlXYScatter, Format:=1, _
        PlotBy:=xlColumns, _
        CategoryLabels:=True, SeriesLabels:=True, HasLegend:=True, _
        Title:="Vapor Pressure", _
        CategoryTitle:="Temperature (F)", _
        ValueTitle:="Pressure (psia)", _
        ExtraTitle:=""
' At this point, we can modify the plot as in the last section.

'-----------------------------------------------------------------------
' Demonstrate how to add a chart on a new worksheet -- without ChartWizard
' There are a large number of attributes/properties/methods.
' See |View|Object Browser| for possible objects.
' An easier way is to record the macro and modify the settings recorded.
      
' Go back to the main worksheet -- the next line is needed when the previous
' action takes us to a different worksheet.
      Sheets("Sheet1").Select
' Mark/Select (equivalent to clicking on) the data range
      Sheets("Sheet1").Range("B18:B208").Select 'Do not include the label on the top row.
      Charts.add
' Modify the active chart a little bit
      With ActiveChart
        .Type = xlXYScatter
        .SubType = 1
        .HasTitle = True
        .ChartTitle.Text = "Vapor Pressure"
        .ChartTitle.Font.Size = 16
' Line/marker/legend for the 1st series
        With .SeriesCollection(1)
          .XValues = Worksheets(1).Range("A18:A208")
'          .Values = Worksheets(1).Range("B18:B208")
          .Name = "Pressure"                    ' Legend
          .MarkerBackgroundColorIndex = 1       ' Markers: Black color
          .MarkerForegroundColorIndex = 1
          .MarkerStyle = xlSquare
          .Smooth = False                       ' Smooth Line?
          With .Border                          ' Lines
            .ColorIndex = 1
            .Weight = xlThin
            .LineStyle = xlContinuous
          End With
        End With
' x-axis
        With .Axes(xlCategory)
          .HasTitle = True
          With .AxisTitle
            .Caption = "Temperature (F)"
            .Font.Size = 14
          End With
          .MinimumScale = 0
          .MaximumScale = 800
          .MajorUnit = 200
          .TickLabels.NumberFormat = "0"
        End With
' y-axis
        With .Axes(xlValue)
          .HasTitle = True
          With .AxisTitle
            .Caption = "Pressure (psia)"
            .Font.Size = 14
          End With
          .HasMajorGridlines = False
          .MinimumScale = 0
          .MaximumScale = 4000
          .MajorUnit = 1000
          .TickLabels.NumberFormat = "0"
        End With
      End With

' Go back to the main worksheet -- the next line is needed when the previous
' action takes us to a different worksheet.
      Sheets("Sheet1").Select
       
'-----------------------------------------------------------------------
' A longer example of a message.  See comments at the beginning of this sub.
' See the constants (e.g., vbNo) via |View|Object Browser|VBA|Constants|vbNo|
'   or mark the text "vbNo" and press F1.
' Note: On-line help on Visual Basic is not accessed via |Help| on the menu.
' Generate a long prompt/message:
'     Dim msg As String        ' Declaration is optional.
      msg = "Demonstrate how to output a message."                 'continue to next line with concatenation
      msg = msg & "  The general syntax is:" & Chr(10) & Chr(13)   '/w a Line feed and carriage return
      msg = msg & "   response = MsgBox(msg, Style, Title, Help_file, Ctxt)" & Chr(10) & Chr(13)
      msg = msg & "Press yes to continue displaying the current time."
      response = MsgBox(msg, vbYesNo, "This Is a Test")
      If response = vbNo Then Exit Sub
      Cells(12, 2) = response
        
'-----------------------------------------------------------------------
' An Infinite D0-loop and Output from a pre-defined Excel function
      Cells(13, 2).NumberFormat = "yy/mm/dd"
      Cells(14, 2).NumberFormat = "hh:mm:ss"
      Cells(13, 2) = Now()   'Since "Now" does not need any arguments, parentheses are optional.
      Do
        DoEvents             'Yield control to other Windows programs
        Cells(14, 2) = Now()
      Loop

      End Sub


'-----------------------------------------------------------------------
      Function add(x, y)
'-----------------------------------------------------------------------
' This function simply adds two (scalar) arguments together.
' Note that a function cannot return a vector quantity.  Thus, this function
' fails if the arguments x and y are vectors.
' Furthermore, a function CANNOT change the Excel environment, i.e., cannot
' insert, delete, or change cell values, ...
'-----------------------------------------------------------------------
      add = x + y
      End Function


'-----------------------------------------------------------------------
      Function addvec(x)
'-----------------------------------------------------------------------
' This function simply adds the first three vector elements together.
' Note: Do not declare the array passed to a subroutine/function.
'       Furthermore, with "Dim" the base is 0, but without "Dim" the base is 1.
' In practice, we only need to pass only the first two elements.  Visual Basic
' extends the reference automatically (e.g., x(9)) for a column vector
' just as in FORTRAN.  However, it does not extend the same way for a row
' vector or for a matrix.
'-----------------------------------------------------------------------
      addvec = x(1) + x(2) + x(3)
      End Function


'-----------------------------------------------------------------------
      Static Function addsub(x, y, i)
'-----------------------------------------------------------------------
' This function demonstrate how to output a vector by declaring a "static" function and create
' a variable "doneCalculation" to avoid unnecessary re-calculation.
' Static function remembers the values assigned from the last call
'     i ... mimic vector array index
'     doneCalculation=0 ... The function is called the first time; need to do calculation
'     doneCalculation=1 ... The function has been called before with the same set of arguments;
'                           thus, no need to recalculate; just output the finding from the previous call
'-----------------------------------------------------------------------
      Dim outvec(2)          ' temporary storage for output vector

' Calculation section
      If (doneCalculation = 0 Or x <> xold Or y <> yold) Then
        Call MsgBox("Going through calculation.")
        outvec(1) = x + y
        outvec(2) = x - y
        doneCalculation = 1
      End If

' Output section
      addsub = outvec(i)

' Update the input variables
      xold = x
      yold = y
      
      End Function



'-----------------------------------------------------------------------
      Sub test()
'-----------------------------------------------------------------------
' Include lines of Visual Basic to be tested here.
'-----------------------------------------------------------------------
      End Sub