Geek asks and answers

. Choose .Activesheet, .Activecell, etc ...

For this question, I refer to the post below to clarify:
Why is my conditional format shifted when adding VBA?

In many, many posts I see these days, the OP is silently allowed to use .Activate, .Select, .Offset, etc., while they are open to potential errors (most often caused by end users).
Sometimes the code is even supported.

My question is: is there any correct situation where you will use any of these statements without direct alternatives that will catch the typical errors that result from these stmts?

I mean dynamic solutions, which, in my opinion, are necessary when developing for Excel. Personally, for more than six years I cannot recall a single case when I need it; this seems to always be one of the worst options. In my previous company, it was a tacit rule to never use it, and it only improved my life for VBA (and its end user).

Why am I creating this question because I think it’s worth making newcomers to VBA aware of the risks that they take when using these statements (from the experience of proven risks when end users do something unexpected - in the end, t have any attachment to VBA) and offer direct alternatives (I will not say that I have always done this to myself, but I feel in my gut that there is something wrong, just offering quick solutions for already monster bugs).

, ( ), VBA ( , , Qaru Google , (!)).
, "" "", , imho. select stmt , ( ), .

VBA, , , ; ( imho "" , Excel Access). , , VBA "".

.
, ; .

+5
5

, ActiveWorkbook, ActiveSheet ActiveCell ( , , , , ). . , " ", ActiveCell. , ; . .

, Select ( , , ).

  • . Application.ConvertFormula, , , , .
  • . .
  • Shapes. , , Shapes. -, , .

Select Activate - .

+3

Excel , ActiveSheet/ActiveWorkbook .., getchas. , , . , , -

Sub SetZoom()
Dim ws As Worksheet
    Application.screenupdating = false

    For Each ws In Worksheets
        ws.Select
        ActiveWindow.Zoom = 80
    Next ws

    Application.screenupdating = true
End Sub
+2

.Select, , - , , Activate Select, , .

,

FinalViewWorkbook.FinalViewSheet.Range("A1").Select

, - - ", !" .

+1

, :

  • Active -something: , , . , (, " , " ).
  • Selection: , Active, . Userful , " ".
  • Select, Activate: Imho Selection, , .. , . . (. @user3357963) (. @enderland). ( , , PageView ActiveSheet).
  • Select, Activate 2nd: VBA Macro Recorder, , : Range("A5").Select, Selection.Value="NewValue". Range("A5").Value="NewValue".
  • Offset: .Offset() - . , " " " + 1" .

, , , OP .Activate,.Select,.Offset ..

. , , , ActiveCell.Value . , , , , , : -)

0

, , , Select, , / .

, , Selection, Range, :

Dim myRange as Range
Set myRange = Application.InputBox("Select your range", Type:=8)

, , , , Selection ( Pandora Box , ...).

, PowerPoint. RibbonUI XML VBA, Shapes PowerPoint . , "" - , .. t , GUID.

Selection, , - ,

Sub UpdateOrEditSelection(update As Boolean)
'This procedure invoked when user edits/updates a chart.
Dim uid As Variant
Dim sel As Selection
Dim s As Integer
Dim chartsToUpdate As Object
Dim multipleShapes As Boolean
Dim sld As Slide
Set sel = ppPres.Windows(1).Selection

If update Then
    Set chartsToUpdate = CreateObject("Scripting.Dictionary")
    Select Case sel.Type
        Case ppSelectionShapes
            For s = 1 To sel.ShapeRange.count
                uid = sel.ShapeRange(s).Name
                '....
                '...
                '..
                '.
            Next
        Case ppSelectionSlides
            For Each sld In sel.SlideRange
                For s = 1 To sld.Shapes.count
                    uid = sld.Shapes(s).Name
                    '....
                    '...
                    '..
                    '.

                Next
            Next
        Case ppSelectionText
            s = 1
            If sel.ShapeRange(s).HasTable Or sel.ShapeRange(s).HasChart Then
                uid = sel.ShapeRange(s).Name
                '....
                '...
                '..
                '.

            End If
    End Select
 '....
 '...
 '..
 '.

?

. , : , , , , .. . , , , , , , :

Excel VBA

, ?

. VBA mouseclicks, .

:

, Select. , IMO Excel, .

( VBA - Excel):

  • - , Selection.
  • PowerPoint - , , / - . "" -, .

:

    Set tb = cht.Shapes.AddTextbox(msoTextOrientationHorizontal, ptLeft, tBoxTop, ptWidth, ptHeight)
    tb.Select  '<--- KEEP THIS LINE OTHERWISE TEXTBOX ALIGNMENT WILL NOT WORK ## ## ##

:

    'PPT requires selecting the slide in order to export an image preview/jpg
sld.Select
ppPres.Windows(1).View.GotoSlide sld.SlideIndex
sld.Shapes(1).Chart.Export imgPath, ppShapeFormatJPG

, Point:

        pt.Select
        pt.format.Line.Visible = msoTrue
        pt.format.Line.Visible = msoFalse
        pt.MarkerSize = pt.MarkerSize + 2

, , . PowerPoint, PowerPoint , Excel, , Excel Word .

  • Outlook: I don't really like Outlook, it is very similar to Word and actually uses the Word object model in the inspector, but what I do with Outlook relies on things like ActiveInspector etc.

Neither Word nor PowerPoint has a "macro recorder" anymore (in fact, I think Word can, but it is so powerless that it is useless), and by the time most people do any development in other applications, they figured out most of it already.

0
source

More articles:


All Articles