0

I am able to control the weight of a graph in Excel VBA using the following code:

ActiveChart.Axes(xlCategory).Select
With Selection.Border
    .ColorIndex = 57
    .Weight = xlMedium
    .LineStyle = xlContinuous
End With

When I try running this in Word VBA, I get the error

Compile error: Method or data member not found.

.Border is highlighted.

I experimented with the code

salesChart.Axes(xlCategory).Select
Selection.Borders(wdBorderBottom).Visible = True

and got the message

Run-time error '4605' This method or property is not available because the object refers to a drawing object.

I want to figure out a way to highlight the axes in the graph I'm embedded in a MS Word document through Word VBA

khargushoghli
  • 55
  • 1
  • 1
  • 12
  • 1
    What is the origin of this chart? If it is from a copy/paste-special (enhanced metafile) you may not be able to access the same properties/etc. that are available to a full-blown MS Excel chart. – David Zemens Mar 30 '15 at 02:58
  • Thanks, @DavidZemens It's an embedded graph generated within Word: Dim salesChart As Chart Dim chartWorkSheet As Object Set salesChart = ActiveDocument.Shapes.AddChart.Chart Set chartWorkSheet = salesChart.ChartData.Workbook.WorkSheets(1) You may have put your finger on the problem, in which case I'll have to go back to opening Excel from within Word and using VBA code preplaced there, which works like a charm, of course. It's a shame, though. Opening and closing Excel is much more of a time drag, as well as being somehow inelegant. – khargushoghli Mar 30 '15 at 10:12

1 Answers1

0

I see a few things now that I look at this more closely. The first snippet is Excel code, this doesn't run and can't run in Word, and in fact it's raising a compile error.

When I try running this in Word VBA, I get the error

ActiveChart.Axes(xlCategory).Select
With Selection.Border
    .ColorIndex = 57
    .Weight = xlMedium
    .LineStyle = xlContinuous
End With

The compiler highlights .Border because there is no Border property of a Selection object in Word.

The Selection object is a bit ambiguous, because it can be different things. Think of it like a variant which refers to whatever is selected or the cursor point if nothing is selected.

So that explains the method or data member not found which isn't a run-time error, but a compile error suggesting that your code cannot possibly execute as-is.

If you replace Select and Selection with the object itself, like so:

With ActiveChart.Axes(xlCategory).Border
    .ColorIndex = 57
    .Weight = xlMedium
    .LineStyle = xlContinuous
End With

You'll get another error, because there is no such thing as ActiveChart in Word, that is an Excel object only. (Using Option Explicit would have alerted you to this problem). In word, you need to refer to the shape or chart, and you can do this with a handle on the shape as you have done, with a variable. Try:

With salesChart.Axes(xlCategory).Border
    .ColorIndex = 57
    .Weight = xlMedium
    .LineStyle = xlContinuous
End With

This might be useful:

How to avoid using Select in Excel VBA macros

While written for Excel specifically, it is a better programming concept: Select is a pretty crude way of doing most things, and although there are some exceptions, nearly everything you can do with VBA can be done more efficiently by directly working with the object, rather than first selecting and then acting on the selection -- it's redundant. It's also messy because you need to constantly keep selecting things and your code is necessarily ambiguous, referring to vague Selection object (variant) rather than strongly typed object, etc.

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130