Highlighting every other line in a spreadsheet - Statistica Visual Basic - Statistica - Dell Community

Highlighting every other line in a spreadsheet

Highlighting every other line in a spreadsheet

This question is not answered

I wrote a macro to highlight every other line in a spreadsheet.  This helps me not to get lost when reviewing my data and scrolling across the spreadsheet.  Does anyone have a more efficient way of doing it?  Feel free to use the macro if you would like.  It is copied below:

Sub Main

 Dim S1 As Spreadsheet
 Set S1=ActiveSpreadsheet

'add two variables
 Dim nvar As Integer
 nvar=S1.NumberOfVariables
 S1.AddVariables "origional order", nvar, 1, scDouble, 8, -999999998, "General", "=v0+1"
 S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2), 1,2)"
 S1.Recalculate
'sort data by every other
 S1.SortData(nvar +2)
'set cell formating to highlight grey
 Set Cells=S1.CellsRange(1, 1, S1.NumberOfCases/2, S1.NumberOfVariables)
 Set CellFont=Cells.Font
 CellFont.Background=RGB(200,200,200)

'return data to origional order and delete variables
S1.SortData(nvar +1)
S1.DeleteVariables nvar +1, nvar +2
 
End Sub

All Replies
  • Thanks for sharing Jennifer. This is a fun little macro. And I can see how it can help visualized data.

    I ran this against the example dataset; adstudy. The top 50% turned grey. I decided that I wanted to mark the spreadsheet with four different colors. So I played around.  

    The macro below marks up the spreadsheed with four different colors. It isn't perfect. I was only working with spreadsheets that had even numbers of cases (100, 24).... numbers that had no remainder when divided by 4.

     

    Sub Main

    Dim S1 As Spreadsheet
     Set S1=ActiveSpreadsheet

    'add two variables
     Dim nvar As Integer
     nvar=S1.NumberOfVariables
     S1.AddVariables "origional order", nvar, 1, scDouble, 8, -999999998, "General", "=v0+1"
     S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2), 1,2)"
     S1.Recalculate
    'sort data by every other
     S1.SortData(nvar +2)


     Dim NumCases As Integer
     NumCases = (S1.NumberOfCases/4)

    'set cell formating to highlight grey, top 25%
     Set Cells=S1.CellsRange(1, 1, NumCases, S1.NumberOfVariables)
     Set CellFont=Cells.Font
     CellFont.Background=RGB(200,200,200)

     'set cell formating to highlight red, 26% to 50%
     Set Cells=S1.CellsRange(NumCases + 1, 1, NumCases * 2, S1.NumberOfVariables)
     Set CellFont=Cells.Font
     CellFont.Background=RGB(200,20,20)

     'set cell formating to highlight green, 51% to 75%
     Set Cells=S1.CellsRange((NumCases * 2) + 1, 1, NumCases * 3, S1.NumberOfVariables)
     Set CellFont=Cells.Font
     CellFont.Background=RGB(20,200,20)

    'set cell formating to highlight blue, 75% to 100%
     Set Cells=S1.CellsRange((NumCases * 3) + 1, 1, NumCases * 4, S1.NumberOfVariables)
     Set CellFont=Cells.Font
     CellFont.Background=RGB(20,20,200)

    'return data to origional order and delete variables
    S1.SortData(nvar +1)
    S1.DeleteVariables nvar +1, nvar +2

    End Sub
     

  • If you tried the macro and only the top half of the data were highlighted in grey, i have a suggestion.  An SVB command was added sometime recently so data can sort with or without formatting.  To sort with formatting in Version 9, you need SortDataEx().  SortData() will sort while leaving the formatting stationary.  I learned something new.

    Here is the updated macro:

    Sub Main

     Dim S1 As Spreadsheet
     Set S1=ActiveSpreadsheet

    'add two variables
     Dim nvar As Integer
     nvar=S1.NumberOfVariables
     S1.AddVariables "origional order", nvar, , , , , , "=v0+1"
     S1.AddVariables "every other", nvar +1, 1, , , , , "=iif( v0/2=trunc(v0/2), 1,2)"
     S1.Recalculate
    'sort data by every other
     S1.SortData(nvar +2)
    'set cell formating to highlight yellow
     Set Cells=S1.CellsRange(1, 1, S1.NumberOfCases/2, S1.NumberOfVariables)
     Set CellFont=Cells.Font
     CellFont.Background=RGB(200,200,200)

    'return data to origional order and delete variables
    'updated sort function to keep formatting
    S1.SortDataEx(nvar +1)
    S1.DeleteVariables nvar +1, nvar +2
     
    End Sub

  • I tried your macro, however an error was encoutered in the row

    S1.Recalculate

    , i.e. "Formula error. Unexpected sign"

    could you explain it?

    I am using the Statistica 10 Pl

    apl

  • Different regional settings require a different way of writing spreadsheet functions. I wrote this macro under US regional settings. Here, a comma is used to seperate agruments in the "iff" function. When a regional setting uses commas as decimal seperators in numbers, then semicolons are used to seperate arguements in the function.

    My guess is this is the issue. So in the macro, this line:

    S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2), 1,2)"

    should be changed to this:

    S1.AddVariables "every other", nvar +1, 1, scDouble, 8, -999999998, "General", "=iif( v0/2=trunc(v0/2); 1; 2)"

     

    , changed to ; in the function. I think that should do it.

  • Thank you very much, Jenifer, your macro works now.

    Try my macro MultiANOVA (http://main3.amu.edu.pl/~apl/index.html)

    Sincerely yours

    apl