From: Masaki Suketa <masaki.suketa@...>
Date: 2007-09-30T19:19:17+09:00
Subject: Re: OLE Object - combo box problem

Bulhac Mihai wrote:
 > i have this:
 >
 > $excel = WIN32OLE.new('Excel.Application')
 > $worksheet_asitt_menu = $asitt_workbook.Worksheets(1)
 > $worksheet_asitt_menu.name="ASITT_MENU"
 >
 > 
$combo_box=$worksheet_asitt_menu.OLEObjects.Add("Forms.Combobox.1").object
 >
 > modu =
 > $asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)
 > modu.CodeModule.InsertLines(1, "Private Sub ComboBox1_Change")
 > modu.CodeModule.InsertLines(2, " MsgBox \"asdasd\"" )
 > modu.CodeModule.InsertLines(3, "End Sub")
 >
 > -combobox1 is the name for combo box
 >
 > with this code i still have that error:
 >
 > excel2.rb:208:in `method_missing': Item (WIN32OLERuntimeError)
 >     OLE error code:800A0009 in VBAProject
 >       Subscript out of range
 >     HRESULT error code:0x80020009
 >       Exception occurred.  from excel2.rb:208:in `excel_asitt_menu'
 >   from excel2.rb:352
 >
 > #line 208:
 > modu =
 > $asitt_workbook.VBProject.VBComponents.Item($worksheet_asitt_menu.name)

That's because you have changed $worksheet_asitt_menu.name to 'ASITT_MENU',
and you don't specify correct VBComponent name as Item argument.
(The error message is 'Subscript out of range'.)

At first, check available name by using following code
before line 208.
   $asitt_workbook.VBProject.VBComponents.each do |comp|
     puts comp.name
   end

Or try to invoke VBE of Excel and check available names.
If you could see 'Sheet1(ASITT_MENU)' in VBAProject, then
specify 'Sheet1' instead of $worksheet_asitt_menu.name

   modu =
   $asitt_workbook.VBProject.VBComponents.Item('Sheet1')

   Regards,
   Masaki Suketa