Home

The voltage analysis in Microsoft Excel

Print version

The macro displays the voltages of the nodes of the network of the "RastrWin" or "Actopus" software in Excel
'sorted in ascending order 
'Working with the script:
'Load the network to the "RastrWin" or "Actopus" software, which voltages is needed to put into Excel 
'and run the script
'
'(C) Alexandrov CDA of the Urals, 2003
'
'it is an "Excel.Application" object creates and variable objEx assigns a reference to it
Set objEx = CreateObject("Excel.Application")
'the number of pages changes in a new book in Excel 
objEx.SheetsInNewWorkbook = 1
' a new book in Excel is created with the number of pages of previously established
objEx.Workbooks.Add
'Returns to the original number of pages in a new Excel book
objEx.SheetsInNewWorkbook = 3
'the name of the first sheet in Excel is set
objEx.Worksheets(1).Name  = "data"

' ////////////////// the main program of the output voltage nodes in Excel  ///////////////////

' it is creates a "node" object and to tNode variable is assigned a reference to it
  Set  tNode = Rastr.Tables("node")
'the vector of the initial binding of the whole script to Excel spreadsheet is defined,
'below, all functions use the offsets of the[offset rows] [-offset columns] format
' формат [смещение по строкам][смещение по столбцам]
  dVstart                   = Array(4,4)   'Y,X binding of Data Table
'the back titles for the columns in the order to display
  aHeaders                  = Array("node number","node name","dev dV%") 'Headers for columns
'The titles of Rastr columns to be extracted is set (corresponding to a given in a Rastr!)
  aColHeaders               = Array("ny","name","otv")   ' headers of columns to be copy 
  
'function that copies data into Excel from Rastr, and arranges the numbers of the extracted records
'dVstart(0) Bind of the output data by Excel rows 
'dVstart(1) Bind of the output data by Excel cols
'objEx The Excel object reference 
'tNode The reference to the table where the Rastr data is copied 
''aColHeaders the column headers that must be extracted from the Rastr from the table that is referenced
' in the tNode
  CopyData dVstart(0),dVstart(1),objEx,tNode,aColHeaders
'function creates the data headers 
'dVstart(0) Bind of the output data by Excel rows
'dVstart(1) Bind of the output data by Excel cols
'objEx The Excel object reference
'aHeaders The headers
'"Deviation from the nominal voltage by the nodes in% " - a direct indication of the name of the entire table
  CreateHeader dVstart(0),dVstart(1),objEx,aHeaders,"Deviation from the nominal voltage by the nodes in %""   
  
'the sort of the data in Excel
'objEx.Worksheets("data") - Send a link to a table of the data to sort
'RangeA1(dVstart,0,1,3,tNode.Count) - Specifies the range of sorting data, using the RangeA1 that returns
'a range of cells Excel format A2F4 (default) 
'RangeA1(dVstart,3,1,3,1)- the column by which to sort is specifying, using the RangeA1 returns
'a range of cells Excel format A2F4 (default)
  Sort objEx.Worksheets("data"),RangeA1(dVstart,0,1,3,tNode.Count),RangeA1(dVstart,3,1,3,1)

'The range of values Y for plotting in Excel is specified using the RangeA1
  RangeY =   RangeA1(dVstart,3,1,3,tNode.Count)
'The range of values X for plotting in Excel is specified using the RangeR1C1
'RangeR1C1 The range of Excel cells in the R1C1 Excell format returns using this function 
'since Excel did not accept (default) range format to plot so the range for normalized values of the X graphics 
'was made in the special function
  RangeX = RangeR1C1(dVstart,2,1,2,tNode.Count)
  'Range X values of Chart in format R1C1
'function draws a graph in Excel
'RangeY, - range of Y values for the chart
'RangeX, - range of X values for the chart
'objEx, -  The Excel object reference 
'' "Deviation from the nominal voltage by the nodes in %", - directly specify the name of the chart
' "dV%" - set abbreviation name of the chart (displayed on the Graphics tab, and on axis Y)
  ChartGraph RangeY, RangeX, objEx, "Deviation from the nominal voltage by the nodes in %", "dV%" 
'///////////////////////////////End charting Z in Excell ///////////////////

'show the result 
objEx.Visible = true

'///////////////////////// End. of PROGRAM //////////////////////////////////
'////////////////////////////////////////////////////////////////////////////

'/////Here are the implementations of the functions that were used above//////////
'//////////////////////// Return the range ///////////////////////////////
'establishment of a normal Excel range data, based on the displacement of the initial binding.

'aStart - initial binding of the program, the point from which are all bias counts
'OffsetX, - The beginning of the range column 
'OffsetY, - The beginning of the range row
'EndX, - The end of the range column
'EndY - The end of the range row
Function RangeR1C1(aStart,OffsetX,OffsetY,EndX,EndY)
  'function create Excel Range in Format R1C1 Who now, that now
   RangeR1C1 = ("R"& (aStart(0)+OffsetY) &"C"& (aStart(1)+OffsetX) &":"&_
            "R"& (EndY+aStart(0)) &"C"& (aStart(1)+OffsetX))
End Function

' creation of the not normal Excel range of data 
' in a format (number of columns, the number of rows)
' from the beginning of the sheet based on the displacement of the initial binding
'aStart - initial binding of the program, the point from which are all bias counts
'OffsetX, - The beginning of the range column
'OffsetY, - The beginning of the range row
'EndX, - The end of the range column
'EndY - The end of the range row
Function RangeA1  (aStart,OffsetX,OffsetY,EndX,EndY)
  'function create Excell Range in format D34F45 (Excel Default)
   RangeA1   = (Chr(65+aStart(1)-1+OffsetX)& (aStart(0)+OffsetY) &":"&_
             Chr(65+aStart(1)-1+EndX)& (aStart(0)+EndY))
End Function
'///////////////////////End. Range Created //////////////////////////////////

'///////////////////////// Create Header Data table Sub /////////////////////
Sub CreateHeader(Ypoint,Xpoint,pObj,aHeaders,Header)
'Ypoint,Xpoint - initial binding of the program, the point from which are all bias counts
'pObj - pointer to the Excel object 
'aHeaders - an array of the column headers
'Header – header of the data table

'a reference to the Excel worksheet with the data is assigned to tObjData 
  Set tObjData = pObj.Worksheets("data")

'The loop iterates all the headers and prints them on a Excel sheet, with adjusting the width of the columns to the headings
  For i=LBound(aHeaders) to Ubound(aHeaders)
    tObjData.Cells(Ypoint  ,Xpoint+1+i).Value = aHeaders(i) ' Assign the header to the Excel column
    tObjData.Columns(Xpoint+i+1).autoFit                    ' fit the column width
  Next

  tObjData.Cells(Ypoint-2,Xpoint).Value = Header    'creation of a common header

End Sub




'///////////////////////////////////Copy Data sub /////////////////////////
Sub CopyData(Ypoint,Xpoint,pObj,pTable,aColHeaders)
'The function that copies the data from the Rastr table in Excel
'Ypoint,Xpoint - linking the output data to the starting point
'pObj - The Excel object reference 
'pTable - The reference to the table with the Rastr data 
'aColHeaders - The array of column names that must be extracted from pTable

'obtaining a reference to Excel spreadsheet where the data will be displayed
  Set tObjData = pObj.Worksheets("data")

'The loop that creates a number of records displayed
  For dCounter = Ypoint+1 To (pTable.Count+Ypoint)
  'cycle creating Numbers of rows
    tObjData.Cells(dCounter,dVstart(1)).Value = dCounter-dVstart(0)
  Next
' loop to retrieve the data from Rastr tables 
  For i=LBound(aColHeaders) to UBound(aColHeaders)
  ' cycle changing column for copy to Excell Sheet
      'loop to retrieve the data from a single table column
      For dCounter = YPoint+1 To (pTable.Count+Ypoint)
      'cycle copying data from Rastr table.column to Excell.Sheets("data").column
      ' copy not scaled data from the Rastr table in Excel 
        tObjData.Cells(dCounter,Xpoint+i+1).Value = pTable.Cols(aColHeaders(i)).Z(dCounter-Ypoint-1)
      Next
  Next

End Sub


' //////////////////////////////// Charting sub ///////////////////
Sub ChartGraph(RangeY,RangeX,pObj,CTitle,YTitle)
'Excel chart drawing function
  'obtain a reference to the Excel table with data
  Set tObjData = pObj.sheets("data")
  
  'do the list with data active, otherwise it could be trouble
  tObjData.Select
  'select the range of cells in Excel to construct Y values
  tObjData.Range(RangeY).Select
  'create a new chart in Excel, on the basis of the selected cell range
  'specifies the name of the chart pObjChart
  Set pObjChart                      = pObj.Charts.Add()
  'specifies the name of the chart
  pObjchart.Name = YTitle
  'specifies the type of chart (histogram, columns, etc.)
  pObjChart.ChartType                = 51  'xlColumnClustered
  'disable the legend of the chart
  pObjChart.HasLegend                = False
  'give the name of the chart data series on the Y axis
  pObjChart.SeriesCollection(1).Name = CTitle

  With pObjChart
          .Axes(2, 1).HasTitle = True                    ' Y-axis has a title
          .Axes(2, 1).AxisTitle.Characters.Text = YTitle 'set the title
  End With

  With pObjChart.Axes(2)        'xlValue - Work with the chart Y axis 
        .MinimumScale = -15     'Max value axes Y - setting the maximum value on the Y axis
        .MaximumScale =  15     'Min value axes Y - setting the minimum value on the Y axis
        .MinorUnit = 1          'minor steep axes Y - step of the auxiliary grid line
        .MajorUnit = 5          'major steep axes Y - step of the main grid line
        .Crosses = -4105        'xlAutomatic - automatic determination of the point of intersection of the axis of the chart
        .ReversePlotOrder = False 'does not apply reversals schedule to the chart data
  End With

  pObjChart.PlotArea.Interior.ColorIndex = -4105 ' - the field painting color of the chart is white

  ' adding to the range of X values the link to the data table
  RangeX = "=data!"& RangeX 
  ' set the range of X values to the chart 
  pObjChart.SeriesCollection(1).XValues = RangeX
  'disable the display of the X axis values on the chart (too much), they can be seen when the mouse scrolling!
  pObjChart.HasAxis(1,1)= False    'from Excell Chart.HasAxis(xlCategory, xlPrimary) = False

End Sub
'//////////////////////////////// End My Sub /////////////////////


'////////////////////////// sorting Sub //////////////////////////
Sub Sort(tData, RangeData, RangeBy)
'The subprogram of transferred range sorting 
'RangeData – sorted range
'RangeBy - column by which to sort

'The standard sort Excel function is used
'in place of the missing parameters there is nothing, for help go to in Excel.
'You can add to not allocate a comma missing parameters
'you can not skip parameters, those which are in Excel HELP
'that is, the call coincides with a call for high-level languages

'Selection.Sort Key1:=Range("G5"), Order1:=xlAscending, Header:=xlGuess, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'xlAscending =1 , xlGuess = 0,xlTopToBottom = 1
'tData.Range("D4:G199").Sort tData.Range("G5"), 1, , , , , , 0,1, False, 1
' "D4:G199" "G5" 
tData.Range(RangeData).Sort tData.Range(RangeBy), 1, , , , , , 0,1, False, 1

End Sub
'///////////////// End. sorting Sub //////////////////////////////