VBA to Ruby code again

[Li Chen]

Hi all,

I want to add error bars to a chart. I record excel macro and then try
to translate them into Ruby codes but fail. I wonder if someone there
can help me out.

Thanks,

Li

# VBA macro codes

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY,
Include:=xlBoth, _
Type:=xlCustom, Amount:="=Sheet1!R5C2:R5C6", MinusValues:= _
"=Sheet1!R5C2:R5C6"

#my Ruby codes
# I already creat an object reference called chart1
# also define Excel constants at the start of the script

chart1.SeriesCollection(1).Select
chart1.SeriesCollection(1).HasErrorBars = "True"

chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R5C2:R5C6"
chart1.MinusValues="=Sheet1!R5C2:R5C6"

ruby macro1.rb

macro1.rb:64: undefined local variable or method `xlY' for main:Object
(NameError)

 

[James Britt]

Hi all,

I want to add error bars to a chart. I record excel macro and then try
to translate them into Ruby codes but fail. I wonder if someone there
can help me out.

Thanks,

Li

# VBA macro codes

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY,
Include:=xlBoth, _
Type:=xlCustom, Amount:="=Sheet1!R5C2:R5C6", MinusValues:= _
"=Sheet1!R5C2:R5C6"

What are xlCustom and xlBoth?

#my Ruby codes
# I already creat an object reference called chart1
# also define Excel constants at the start of the script

chart1.SeriesCollection(1).Select
chart1.SeriesCollection(1).HasErrorBars = "True"

chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R5C2:R5C6"
chart1.MinusValues="=Sheet1!R5C2:R5C6"

macro1.rb:64: undefined local variable or method `xlY' for main:Object

I'm guessing that the VBA code is using built-in constants that your
Ruby code knows nothing about.


You may need to find out the VBA values of xlCustom and xlBoth (I'm
thinking they are integers), then recreate these in your Ruby code.

(If you're doing much VBA porting it can be a big help to get a list of
VBA constants and their values, and create a Ruby file to define them in
one place.)

James Britt

 

[Jan Svitok]

What are xlCustom and xlBoth?



I'm guessing that the VBA code is using built-in constants that your
Ruby code knows nothing about.


You may need to find out the VBA values of xlCustom and xlBoth (I'm
thinking they are integers), then recreate these in your Ruby code.

(If you're doing much VBA porting it can be a big help to get a list of
VBA constants and their values, and create a Ruby file to define them in
one place.)

James Britt

James:
From the previous discussions I assume the OP is getting the constants
somehow, but it seems that something is messed there.

Li:
Could you post the whole code as it seems you've got the constants
wrong - notice the error appers at the first constant. Where are you
putting them? Kernel? Or did you include the constant module?

...

And I'll answer to myself ;-) : The constants are imported with
initial capital letter. So use XlY, XlBoth, etc. See documentation
for the const function (I don't remember the name). There was an
exteption to this rule, so you'd rather check it out.

Moral of the story: in ruby, all constants start with a capital letter.

 

[Li Chen]

Li:
Could you post the whole code as it seems you've got the constants
wrong - notice the error appers at the first constant. Where are you
putting them? Kernel? Or did you include the constant module?

Moral of the story: in ruby, all constants start with a capital letter.

Hi Jan,

I post my the whole codes here. Here are some information about my
script:
1) read the raw data from a text file and extract the columns I need
into an array 2) calculate the mean and SD 3)transform results of mean
and standard error into a 2D array( each row is a group of exp),
respectively 4) draw the plot using win32ole. (In my current example I
use two arrays, one for each group)

Thanks,

Li




####
require 'win32ole'

module ExcelConst
end

#create an excel object and make it visible
excel=WIN32OLE.new('Excel.Application')

#load excel constant
WIN32OLE.const_load(excel, ExcelConst)

#create an excel workbook and make it visible
excel.Visible=TRUE
workbook1=excel.Workbooks.Add
worksheet1=workbook1.Worksheets(1)

#bring worksheet1 to the front
worksheet1.Select

#process data into an array(in my future script)

#fill the data into worksheet1

worksheet1.Range('B1:F1').Value=[1,2,3,4,5] # time points

# mean data
worksheet1.Range('A2:F2').Value=['Group A',100,200,150,200, 200]
#Group A
worksheet1.Range('A3:F3').Value=['Group B', 150,100,80,60,50]
#Group B

# SD data/standard deviation
worksheet1.Range('A5:F5').Value=['Group A', 10,15,20,10,30] # SD for
Group A
worksheet1.Range('A6:F6').Value=['Group B',20,13,40,20,10] # SD for
Group B


#add a chart
worksheet1.Range('A8').Select
chart1=excel.Charts.Add
#chart1.Type=ExcelConst::XlLine
chart1.ChartType=ExcelConst::XlLineMarkers
chart1.SetSourceData Source=worksheet1.Range("A1:F3")

#chart specifications
chart1.PlotBy=ExcelConst::XlRows #plot by rows
#chart1.PlotBy=1 #plot by rows
#chart1.PlotBy=0 #plot by columns
#chart1.Location Where=ExcelConst::XlLocationAsObject

chart1.HasTitle ="True"
chart1.ChartTitle.Characters.Text = "Chart1"

chart1.Axes(ExcelConst::XlCategory, ExcelConst::XlPrimary).HasTitle =
"True"
chart1.Axes(ExcelConst::XlCategory,
ExcelConst::XlPrimary).AxisTitle.Characters.Text = "Day"

chart1.Axes(ExcelConst::XlValue, ExcelConst::XlPrimary).HasTitle =
"True"
chart1.Axes(ExcelConst::XlValue,
ExcelConst::XlPrimary).AxisTitle.Characters.Text = "cpm"


# add SD error bars

chart1.SeriesCollection(1).Select
chart1.SeriesCollection(1).HasErrorBars = "True"

chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R5C2:R5C6"
chart1.MinusValues="=Sheet1!R5C2:R5C6"

chart1.SeriesCollection(2).Select
chart1.SeriesCollection(2).HasErrorBars = "True"

chart1.SeriesCollection(2).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R6C2:R6C6"
chart1.MinusValues="=Sheet1!R6C2:R6C6"

#save workbook
path='C:\Ruby\self\win32\macro-1.xls'
workbook1.Saveas(path)

#close workbook
workbook1.Close()

#ending session
excel.Quit
excel=nil
GC.start

ruby macro1.rb

macro1.rb:61: undefined local variable or method `xlY' for main:Object
(NameError)

 

[Jan Svitok]

Li:
Could you post the whole code as it seems you've got the constants
wrong - notice the error appers at the first constant. Where are you
putting them? Kernel? Or did you include the constant module?

Moral of the story: in ruby, all constants start with a capital letter.

Hi Jan,

I post my the whole codes here. Here are some information about my
script:
1) read the raw data from a text file and extract the columns I need
into an array 2) calculate the mean and SD 3)transform results of mean
and standard error into a 2D array( each row is a group of exp),
respectively 4) draw the plot using win32ole. (In my current example I
use two arrays, one for each group)

Thanks,

Li




####
require 'win32ole'

module ExcelConst
end

#create an excel object and make it visible
excel=WIN32OLE.new('Excel.Application')

#load excel constant
WIN32OLE.const_load(excel, ExcelConst)

#create an excel workbook and make it visible
excel.Visible=TRUE
workbook1=excel.Workbooks.Add
worksheet1=workbook1.Worksheets(1)

#bring worksheet1 to the front
worksheet1.Select

#process data into an array(in my future script)

#fill the data into worksheet1

worksheet1.Range('B1:F1').Value=[1,2,3,4,5] # time points

# mean data
worksheet1.Range('A2:F2').Value=['Group A',100,200,150,200, 200]
#Group A
worksheet1.Range('A3:F3').Value=['Group B', 150,100,80,60,50]
#Group B

# SD data/standard deviation
worksheet1.Range('A5:F5').Value=['Group A', 10,15,20,10,30] # SD for
Group A
worksheet1.Range('A6:F6').Value=['Group B',20,13,40,20,10] # SD for
Group B


#add a chart
worksheet1.Range('A8').Select
chart1=excel.Charts.Add
#chart1.Type=ExcelConst::XlLine
chart1.ChartType=ExcelConst::XlLineMarkers
chart1.SetSourceData Source=worksheet1.Range("A1:F3")

#chart specifications
chart1.PlotBy=ExcelConst::XlRows #plot by rows
#chart1.PlotBy=1 #plot by rows
#chart1.PlotBy=0 #plot by columns
#chart1.Location Where=ExcelConst::XlLocationAsObject

chart1.HasTitle ="True"
chart1.ChartTitle.Characters.Text = "Chart1"

chart1.Axes(ExcelConst::XlCategory, ExcelConst::XlPrimary).HasTitle =
"True"
chart1.Axes(ExcelConst::XlCategory,
ExcelConst::XlPrimary).AxisTitle.Characters.Text = "Day"

chart1.Axes(ExcelConst::XlValue, ExcelConst::XlPrimary).HasTitle =
"True"
chart1.Axes(ExcelConst::XlValue,
ExcelConst::XlPrimary).AxisTitle.Characters.Text = "cpm"


# add SD error bars

chart1.SeriesCollection(1).Select
chart1.SeriesCollection(1).HasErrorBars = "True"

chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R5C2:R5C6"
chart1.MinusValues="=Sheet1!R5C2:R5C6"

chart1.SeriesCollection(2).Select
chart1.SeriesCollection(2).HasErrorBars = "True"

chart1.SeriesCollection(2).ErrorBar Direction=xlY
chart1.Include=xlBoth
chart1.Type=xlCustom
chart1.Amount="=Sheet1!R6C2:R6C6"
chart1.MinusValues="=Sheet1!R6C2:R6C6"

#save workbook
path='C:\Ruby\self\win32\macro-1.xls'
workbook1.Saveas(path)

#close workbook
workbook1.Close()

#ending session
excel.Quit
excel=nil
GC.start

ruby macro1.rb

macro1.rb:61: undefined local variable or method `xlY' for main:Object
(NameError)

Exit code: 1

Now, see for yourself: you use ExcelConst::XlWhatever in the
beginning, and only xlY later. So add ExcelConst:: and make the x X
and you'll be done.

 

[Li Chen]

Hi Jan,



worksheet1.Range('A2:F2').Value=['Group A',100,200,150,200, 200]

#chart1.PlotBy=0 #plot by columns
chart1.Axes(ExcelConst::XlValue, ExcelConst::XlPrimary).HasTitle =
chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Type=xlCustom
#ending session
excel.Quit
excel=nil
GC.start

Now, see for yourself: you use ExcelConst::XlWhatever in the
beginning, and only xlY later. So add ExcelConst:: and make the x X
and you'll be done.

Hi Jan,

This is not true. I have two versions: one is the above and the other
same as you point out. Here is the result I run the second version:

###
ruby macro1.rb
macro1.rb:65:in `method_missing': ErrorBar (WIN32OLERuntimeError)
OLE error code:0 in <Unknown>
<No Description>
HRESULT error code:0x8002000f
Parameter not optional. from macro1.rb:65

Exit code: 1

line 65 is

chart1.SeriesCollection(1).ErrorBar Direction=ExcelConst::XlY


Li

 

[Jan Svitok]

Hi Jan,



worksheet1.Range('A2:F2').Value=['Group A',100,200,150,200, 200]

#chart1.PlotBy=0 #plot by columns
chart1.Axes(ExcelConst::XlValue, ExcelConst::XlPrimary).HasTitle =
chart1.SeriesCollection(1).ErrorBar Direction=xlY
chart1.Type=xlCustom
#ending session
excel.Quit
excel=nil
GC.start

Now, see for yourself: you use ExcelConst::XlWhatever in the
beginning, and only xlY later. So add ExcelConst:: and make the x X
and you'll be done.

Hi Jan,

This is not true. I have two versions: one is the above and the other
same as you point out. Here is the result I run the second version:

###
ruby macro1.rb
macro1.rb:65:in `method_missing': ErrorBar (WIN32OLERuntimeError)
OLE error code:0 in <Unknown>
<No Description>
HRESULT error code:0x8002000f
Parameter not optional. from macro1.rb:65

Exit code: 1

line 65 is

chart1.SeriesCollection(1).ErrorBar Direction=ExcelConst::XlY

This is aonther type of error. I suppose you should do:

chart1.SeriesCollection(1).ErrorBar 'Direction' => ExcelConst::XlY

but it's a tip from documentation. See the programming ruby for how to
transform XXX=YYY from VB to Ruby.

What I'm sure about is that Direction=ExcelConst::XlY is an assignment
and as such it can't work. Your line is equal to the following:

Direction=ExcelConst::XlY
chart1.SeriesCollection(1).ErrorBar(ExcelConst::XlY)

 

[Li Chen]

This is aonther type of error. I suppose you should do:

chart1.SeriesCollection(1).ErrorBar 'Direction' => ExcelConst::XlY

but it's a tip from documentation. See the programming ruby for how to
transform XXX=YYY from VB to Ruby.

What is the page in 2nd edition? I read through the automation with
excel part but I don't remember the part talking about VB to Ruby.

What I'm sure about is that Direction=ExcelConst::XlY is an assignment
and as such it can't work. Your line is equal to the following:

Direction=ExcelConst::XlY
chart1.SeriesCollection(1).ErrorBar(ExcelConst::XlY)

Either of them doesn't work and Ruby still complains it.

Li