none
How to call other subroutines? RRS feed

  • Question

  • Best Regards:

    I am developing an application automating Excel 2011 from Visual Studio 2010 with Visual Basic, the problem is that I can call other subroutines.

    I developed the following subroutine to add formatting to the selected cell:

    Sub FormatoCelda()
    
            With XLApp.Selection
                .HorizontalAlignment = XlHAlign.xlHAlignCenter
                .VerticalAlignment = XlVAlign.xlVAlignBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .MergeCells = False
            End With
    
            With XLApp.Selection.Interior
                .Pattern = XlPattern.xlPatternSolid
                .PatternColorIndex = XlPattern.xlPatternAutomatic
                .Color = 6182986
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            With XLApp.Selection.Font
                .ThemeColor = XlThemeColor.xlThemeColorDark1
                .TintAndShade = 0
            End With
    
    End Sub
    <br/>
    

    But if I use my as follows I get the following error:

    

    XLApp.ActiveWorkbook.Worksheets("Conditions").Cells(2, 2).Select()
     
    FormatoCelda()
    

    Wednesday, December 21, 2011 4:33 PM

Answers

  • You haven't got a response, maybe because...
      The error message is not in English.
      There is no call to another sub shown, even though you stated that was the problem.

    Problems could possibly arise if, when, because...
      Excel requires a sheet to be selected, before cells on it can be selected.
      An automated instance of Excel is not visible and therefore cannot have an active workbook.
      (you can of course make the application.visible)
      When automating Excel, one should avoid the use of the "With" construct,
      avoid the use of ActiveWorkbook, ActiveSheet, ActiveCell and avoid the use of the Selection object.
      It is best to use object references for the above and not select anything.

    Maybe some of the above applies to your problem, it is hard to tell.
    '---
    Jim Cone
    Portland, Oregon USA
    http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
    (workbook with "universal" Last Row function code - free)
    • Marked as answer by ChemicalB0y Monday, December 26, 2011 3:22 AM
    Sunday, December 25, 2011 5:52 AM

All replies

  • You haven't got a response, maybe because...
      The error message is not in English.
      There is no call to another sub shown, even though you stated that was the problem.

    Problems could possibly arise if, when, because...
      Excel requires a sheet to be selected, before cells on it can be selected.
      An automated instance of Excel is not visible and therefore cannot have an active workbook.
      (you can of course make the application.visible)
      When automating Excel, one should avoid the use of the "With" construct,
      avoid the use of ActiveWorkbook, ActiveSheet, ActiveCell and avoid the use of the Selection object.
      It is best to use object references for the above and not select anything.

    Maybe some of the above applies to your problem, it is hard to tell.
    '---
    Jim Cone
    Portland, Oregon USA
    http://blog.contextures.com/archives/2011/07/18/find-last-row-with-excel-vba/
    (workbook with "universal" Last Row function code - free)
    • Marked as answer by ChemicalB0y Monday, December 26, 2011 3:22 AM
    Sunday, December 25, 2011 5:52 AM
  • Thank u!
    Monday, December 26, 2011 3:22 AM