Excel vba - au3 no object selected

lorenkinzel · April 19, 2015

I am doing a GUI to work with the shape objects in excel. Sort of a poor-mans' autocad.

Where I am running into trouble is avoiding errors if no object is selected.

The intent being: if an object is selected, do this. Else, do the other.

Function of focus is setZero(), line 306.

Any help would be appreciated.

;OPEN THE EXCEL BOOK FIRST - AS IN BEFORE YOU OPEN THE SCRIPT
#cs
    TO DO:
    At setZero(): if no object is selected, read the inputs? Something like that.
    Set all to use lower-left corner of selection rather than upper-left(subtract height).
    Set all to use positive as upward rather than down (multiple of -1)
    Secondary pop-up gui with controls for: get size, get location, group selection(s), group all, un-group all.
    Get your S- - T together with the multiplier-thing.
    *GET SOME FRIGGIN' ERROR PREVENTION IN THERE others may not understand the crash if no object is selected.*
    *last on list as not all Funcs are known yet
    ===============================================================
    IN PROGRESS:
    funcs that use fixed/relative:
    setLocation()
    insert()
    ===============================================================
    Funcs that NEED fixed/relative:
    setLocation()
    insert()
    getLocation();===========not in use
    ===============================================================
    Funcs that use lower-left rather than upper-left:
    none yet
    ===============================================================
    Funcs that use positive as upward rather than down:
    none yet
#ce
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>

Opt("GUIOnEventMode", 1)
Opt("WinTitleMatchMode", 2)

Global $relativeOrFixedChoice = 0;0 for relative, 1 for fixed
Global $zeroX_Coordinate, $zeroY_Coordinate;direct-read, does not have multiplier

$guiWidth = @DesktopWidth - 900
$guiHeight = (@DesktopHeight * .955) - 105

$hGUI = GUICreate("", 897, 78, $guiWidth, $guiHeight, $WS_POPUP, $WS_EX_TOOLWINDOW)
GUISetOnEvent($GUI_EVENT_PRIMARYDOWN, "move_it")
GUISetBkColor(0xABCDEF);(0x00ff00)while messing with gui. Else, (0xABCDEF)
GUISetStyle($WS_POPUP, $WS_EX_LAYERED, $hGUI)
_WinAPI_SetLayeredWindowAttributes($hGUI, 0xABCDEF, 255);ask someone who understands it. I copied it.
WinSetOnTop($hGUI, "", 1);keeps you visible while touching the Excel sheet

;=======================================================offset copy controls
$offsetX_Input = GUICtrlCreateInput("", 530, 8, 50, 21)
$offsetY_Input = GUICtrlCreateInput("", 530, 32, 50, 21)
$offsetX_Label = GUICtrlCreateLabel("X", 515, 8, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$offsetY_Label = GUICtrlCreateLabel("Y", 515, 32, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$offsetCopyButton = GUICtrlCreateButton("offset copy", 458, 8, 49, 41, $BS_MULTILINE)
GUICtrlSetOnEvent($offsetCopyButton, "offsetCopy")

;=======================================================insert drawing object controls
$insertW_Input = GUICtrlCreateInput("1.5", 368, 7, 50, 16)
$insertH_Input = GUICtrlCreateInput("96", 368, 25, 50, 16)
$insertWidthLabel = GUICtrlCreateLabel("W", 352, 7, 15, 16)
GUICtrlSetColor(-1, 0xff00ff)
$insertHeightLabel = GUICtrlCreateLabel("H", 352, 25, 12, 16)
GUICtrlSetColor(-1, 0xff00ff)
$insertX_Input = GUICtrlCreateInput("0", 368, 43, 50, 16)
$insertY_Input = GUICtrlCreateInput("0", 368, 61, 50, 16)
$insertX_Label = GUICtrlCreateLabel("X", 352, 43, 15, 16)
GUICtrlSetColor(-1, 0xff00ff)
$insertY_Label = GUICtrlCreateLabel("Y", 352, 61, 15, 16)
GUICtrlSetColor(-1, 0xff00ff)
$insertButton = GUICtrlCreateButton("insert", 296, 8, 49, 41)
GUICtrlSetOnEvent($insertButton, "insert")

;=======================================================move. relocate selection controls
$setX_LocationInput = GUICtrlCreateInput("", 208, 8, 49, 21)
$setY_LocationInput = GUICtrlCreateInput("", 208, 32, 49, 21)
$setX_LocationLabel = GUICtrlCreateLabel("X", 192, 8, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$setY_LocationLabel = GUICtrlCreateLabel("Y", 192, 32, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$setLocationButton = GUICtrlCreateButton("move", 144, 8, 41, 41)
GUICtrlSetOnEvent($setLocationButton, "setLocation")
;======================================================= toggle co-ordinate system from 'relative to selected object' to 'from a fixed point' from set-zero section controls.
$labelFixedCoOrd = GUICtrlCreateLabel("", 207, 54, 51, 19)
GUICtrlSetState($labelFixedCoOrd, $GUI_DISABLE)
GUICtrlSetBkColor($labelFixedCoOrd, 0xff0000)
$setLocationFixedButton = GUICtrlCreateButton("fixed", 208, 55, 49, 17)
GUICtrlSetOnEvent($setLocationFixedButton, "setRelativeOrFixedChoiceToFixed")
$labelRelativeCoOrd = GUICtrlCreateLabel("", 143, 54, 51, 19)
GUICtrlSetState($labelRelativeCoOrd, $GUI_DISABLE)
GUICtrlSetBkColor($labelRelativeCoOrd, 0x00ff00)
$setLocationRelativeButton = GUICtrlCreateButton("relative", 144, 55, 49, 17)
GUICtrlSetOnEvent($setLocationRelativeButton, "setRelativeOrFixedChoiceToRelative")
;=======================================================set grid
$setGridButton = GUICtrlCreateButton("set  grid", 78, 8, 41, 41, $BS_MULTILINE)
GUICtrlSetOnEvent($setGridButton, "setGrid")
;=======================================================resize object
$setWidthInput = GUICtrlCreateInput("", 690, 8, 50, 21)
$setHeightInput = GUICtrlCreateInput("", 690, 32, 50, 21)
$setWidthLabel = GUICtrlCreateLabel("W", 665, 8, 15, 17)
GUICtrlSetColor(-1, 0xff00ff)
$setHeightLabel = GUICtrlCreateLabel("H", 665, 32, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$setSizeButton = GUICtrlCreateButton("set  size", 620, 8, 41, 41, $BS_MULTILINE)
GUICtrlSetOnEvent($setSizeButton, "setSize")
;=======================================================set the zero co-ordinate
$setFixedZeroX_Input = GUICtrlCreateInput("48", 836, 8, 50, 21)
$setFixedZeroY_Input = GUICtrlCreateInput("240", 836, 32, 50, 21)
$setFixedZeroX_Label = GUICtrlCreateLabel("X", 815, 8, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$setFixedZeroY_Label = GUICtrlCreateLabel("Y", 815, 32, 11, 17)
GUICtrlSetColor(-1, 0xff00ff)
$setFixedZeroButton = GUICtrlCreateButton("set   zero", 778, 8, 33, 41, $BS_MULTILINE)
GUICtrlSetOnEvent($setFixedZeroButton, "setZero")

$xItButton = GUICtrlCreateButton("X", 26, 8, 33, 25);Not good physical placement within GUI.Re-work.
GUICtrlSetOnEvent($xItButton, "xIt")
GUICtrlSetFont(-1, 10, 800, 0, "MS Sans Serif")
GUICtrlSetColor(-1, 0xFF0000)

$oExcel = ObjGet("", "Excel.Application")
$wintitle = WinGetTitle(".xlsx")

;=======================================================a solid grab-point for moving the GUI
If $wintitle = 0 Then
    $labelText = "Excel must be open first. Press here to refresh"
Else
    $labelText = "   X-LK-cad   " & $wintitle
EndIf
$move_ItLabel = GUICtrlCreateLabel($labelText, 422, 56, 464, 17)
GUICtrlSetOnEvent($move_ItLabel, "refreshOexcel")
GUICtrlSetBkColor($move_ItLabel, 0x777777)
GUICtrlSetColor($move_ItLabel, 0xffffff)

;=======================================================enable/disable tooltips
$tipEnableCheckbox = GUICtrlCreateCheckbox("", 28, 40, 20, 20)
GUICtrlSetOnEvent($tipEnableCheckbox, "tipOnOff")
GUICtrlSetState(-1, $GUI_UNCHECKED)
GUICtrlSetTip(-1, "enable button tips")

GUISetState(@SW_SHOW)

readZero();set default zero X & Y coordinates

While 1
    Sleep(10)
WEnd

Func move_it();Grab the gray label to scoot the GUI around.
    _SendMessage($hGUI, $WM_SYSCOMMAND, 0xF012, 0)
EndFunc   ;==>move_it

Func xIt()
    Exit
EndFunc   ;==>xIt

Func setGrid();=======================================================sets grid to an emulation of 16" centers horizontal, 12" centers vertical.Gotta love Excels' 2-different-measurement system.
    With $oExcel
        .Rows.RowHeight = 19.50
        .Columns.ColumnWidth = 4.14
    EndWith
EndFunc   ;==>setGrid

Func offsetCopy();=======================================================copy & paste by a specified offset (inches). Multiple clicks/press enter gets multiple copies.
    Local $xMultiplier = 1.6048
    Local $yMultiplier = 1.604995
    Local $deltaX = Execute(GUICtrlRead($offsetX_Input)) * $xMultiplier
    Local $deltaY = Execute(GUICtrlRead($offsetY_Input)) * $yMultiplier
    With $oExcel
        $xLocation = .Selection.ShapeRange.Left
        $yLocation = .Selection.ShapeRange.Top
        $shapObject = .Selection.ShapeRange
        .Selection.Copy
        .ActiveSheet.Paste
        $x = $deltaX + $xLocation
        $y = $deltaY + $yLocation
        Sleep(10)
        .Selection.Left = $x
        .Selection.Top = $y
    EndWith
EndFunc   ;==>offsetCopy

Func insert();=======================================================insert drawing object. Rectangle, 1-point lineweight, black
    Local $width = Execute(GUICtrlRead($insertW_Input)) * 1.6048
    Local $height = Execute(GUICtrlRead($insertH_Input)) * 1.604995
    Local $xInsertPoint = Execute(GUICtrlRead($insertX_Input)) * 1.6048
    Local $yInsertPoint = Execute(GUICtrlRead($insertY_Input)) * 1.604995

    If $relativeOrFixedChoice = 0 Then
        $x = $xInsertPoint
        $y = $yInsertPoint
    EndIf

    If $relativeOrFixedChoice = 1 Then
        $x = $xInsertPoint + ($zeroX_Coordinate * 1.6048)
        $y = $yInsertPoint + ($zeroY_Coordinate * 1.604995)
    EndIf

    With $oExcel
        .ActiveSheet.Shapes.AddShape(1, $x, $y, $width, $height).Select;   shape type, X location, Y location, width, height
        $shapObject = .Selection.ShapeRange
        $shapObject.Fill.Visible = False
        $shapObject.Line.Weight = 1
        $shapObject.Line.ForeColor.SchemeColor = 0;black
        ;$shapObject.Placement = .xlPlacement.xlFreeFloating     (my) attempts to format as 'don't move or size with cells' has been fruitless
    EndWith
EndFunc   ;==>insert

Func setLocation();=======================================================essentially, 'move' selected. In excel, negatives move toward the left & top. A pain
    Local $deltaX = Execute(GUICtrlRead($setX_LocationInput)) * 1.6048;'Execute': Want to be able to enter '48.375+36.125' without doing head-math, focusing on the drawing instead.
    Local $deltaY = Execute(GUICtrlRead($setY_LocationInput)) * 1.604995
    Local $fixedX = GUICtrlRead($setFixedZeroX_Input) * 1.6048
    Local $fixedY = GUICtrlRead($setFixedZeroY_Input) * 1.604995
    If $relativeOrFixedChoice = 0 Then;move to a position relative to its' current position
        With $oExcel
            $xLocation = .Selection.ShapeRange.Left
            $yLocation = .Selection.ShapeRange.Top
            $shapObject = .Selection.ShapeRange
            $x = $deltaX + $xLocation
            $y = $deltaY + $yLocation
            Sleep(10)
            .Selection.Left = $x
            .Selection.Top = $y
        EndWith
    Else;                     move to location from fixed-zero point
        With $oExcel
            $x = $deltaX + $fixedX
            $y = ($deltaY * -1) + $fixedY
            Sleep(10)
            .Selection.Left = $x
            .Selection.Top = $y
        EndWith
    EndIf
EndFunc   ;==>setLocation
#cs
    Func getSize();================not in use
    With $oExcel
    Local $width = .Selection.ShapeRange.Width / 1.6048
    Local $height = .Selection.ShapeRange.Height / 1.604995
    GUICtrlSetData($getSizeInput, "w= " & $width & "   h= " & $height)
    EndWith
    EndFunc   ;==>getSize

    Func getLocation();===========not in use
    With $oExcel
    $xLocation = .Selection.ShapeRange.Left / 1.6048
    $yLocation = .Selection.ShapeRange.Top / 1.604995
    EndWith
    GUICtrlSetData($getLocationInput, "X= " & $xLocation & "   Y= " & $yLocation)
    EndFunc   ;==>getLocation

    Func groupAll();===========not in use
    With $oExcel
    .Application.ScreenUpdating = False
    .ActiveSheet.Shapes.SelectAll
    .Selection.Group
    .Application.ScreenUpdating = True
    EndWith
    EndFunc

    Func unGroupAll();===========not in use
    With $oExcel
    .Application.ScreenUpdating = False
    .ActiveSheet.Shapes.SelectAll
    .Selection.Ungroup
    .Application.ScreenUpdating = True
    EndWith
    EndFunc
#ce
Func setSize();=======================================================resize selected drawing object
    Local $widthMultiplier = 1.6048
    Local $htMultiplier = 1.604995
    Local $width = Execute(GUICtrlRead($setWidthInput))
    Local $height = Execute(GUICtrlRead($setHeightInput))
    Local $translatedWidth = $width * $widthMultiplier
    Local $translatedHeight = $height * $htMultiplier
    With $oExcel
        $shapObject = .Selection.ShapeRange
        $shapObject.Width = $translatedWidth
        $shapObject.Height = $translatedHeight
    EndWith
EndFunc   ;==>setSize

Func setRelativeOrFixedChoiceToFixed();=================================(fixed) set co-ordinate system to: relative to selected object; or from a fixed point from 'set zero' section.
    $relativeOrFixedChoice = 1
    GUICtrlSetBkColor($labelFixedCoOrd, 0x00ff000);change border-color surrounding the 2 buttons to help with visual I.D.
    GUICtrlSetBkColor($labelRelativeCoOrd, 0xff0000)
EndFunc   ;==>setRelativeOrFixedChoiceToFixed

Func setRelativeOrFixedChoiceToRelative();=============================(relative) set co-ordinate system to: relative to selected object; or from a fixed point from 'set zero' section.
    $relativeOrFixedChoice = 0
    GUICtrlSetBkColor($labelFixedCoOrd, 0xff0000)
    GUICtrlSetBkColor($labelRelativeCoOrd, 0x00ff00)
EndFunc   ;==>setRelativeOrFixedChoiceToRelative

Func readZero();====================================================set global variable for zero co-ordinates
    $zeroX = GUICtrlRead($setFixedZeroX_Input)
    $zeroY = GUICtrlRead($setFixedZeroY_Input)
    $zeroX_Coordinate = $zeroX
    $zeroY_Coordinate = $zeroY
EndFunc   ;==>readZero

Func setZero();====================================================get co-ordinates of selection   ;still lacking the all-important lower-left feature
    With $oExcel;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>attempting to make an exception for "no object selected". Not working-out well.
        ;IE: if a shape is selected, do this. Otherwise, do the other.
        If .TypeName.Object.Selection <> "Range" Then;>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>bad syntax. don't know what proper would be. Tried many methods. .Range, etc...crash
            $xLocation = .Selection.ShapeRange.Left / 1.6048; works fine without the "if"
            $yLocation = .Selection.ShapeRange.Top / 1.604995; works fine without the "if"
        Else
            Sleep(100)
            $xLocation = GUICtrlRead($setFixedZeroX_Input)
            $yLocation = GUICtrlRead($setFixedZeroY_Input)
        EndIf
    EndWith
    $xLocationRounded = Round($xLocation, 3)
    $yLocationRounded = Round($yLocation, 3)
    GUICtrlSetData($setFixedZeroX_Input, $xLocationRounded)
    GUICtrlSetData($setFixedZeroY_Input, $yLocationRounded)
    readZero()
EndFunc   ;==>setZero

Func tipOnOff();====================================================Allows tips when needed & removes the annoyance when not needed.
    If GUICtrlRead($tipEnableCheckbox) = $GUI_CHECKED Then
        tipsOn()
    ElseIf GUICtrlRead($tipEnableCheckbox) = $GUI_UNCHECKED Then;My very first ElseIf! A simple Else seemed fine....
        tipsOff()
    EndIf
EndFunc   ;==>tipOnOff

Func tipsOn()
    GUICtrlSetTip($offsetCopyButton, "Copy selection & paste with x, y offset." & @CRLF & " Multiple clicks (or press 'Enter') gets multiple pastes.")
    GUICtrlSetTip($insertButton, "Insert object. Set height, width & location first")
    GUICtrlSetTip($setLocationButton, "Move selection to x, y distance from current (relative)" & @CRLF & "Or to specific location (fixed)")
    GUICtrlSetTip($setGridButton, 'Set grid to an emulation of 16" x, 12" y.')
    GUICtrlSetTip($setSizeButton, "resize selection")
    GUICtrlSetTip($setFixedZeroButton, "Set fixed zero co-ordinates using location of selection")
    GUICtrlSetTip($setLocationRelativeButton, "Co-ordinates for 'move' will come from selections' current location")
    GUICtrlSetTip($setLocationFixedButton, "Co-ordinates for 'move' will come from the 'set zero' option")
    GUICtrlSetTip($xItButton, "Close")

EndFunc   ;==>tipsOn

Func tipsOff()
    GUICtrlSetTip($offsetCopyButton, "")
    GUICtrlSetTip($insertButton, "")
    GUICtrlSetTip($setLocationButton, "")
    GUICtrlSetTip($setGridButton, "")
    GUICtrlSetTip($setSizeButton, "")
    GUICtrlSetTip($setFixedZeroButton, "")
    GUICtrlSetTip($setLocationRelativeButton, "")
    GUICtrlSetTip($setLocationFixedButton, "")
    GUICtrlSetTip($xItButton, "")
EndFunc   ;==>tipsOff

Func refreshOexcel();==========================Allows reading Excel book that was started after script. I forget sometimes to stert Excel first.
    $oExcel = ""
    $wintitle = ""
    Sleep(100)
    $oExcel = ObjGet("", "Excel.Application")
    $wintitle = WinGetTitle(".xlsx")
    Sleep(100)
    If $wintitle <> "" Then
        $labelText = "   X-LK-cad   " & $wintitle
        GUICtrlSetData($move_ItLabel, $labelText)
    EndIf
    If $wintitle = "" Then
        $labelText = "Excel must be open first. Press here to refresh"
        GUICtrlSetData($move_ItLabel, $labelText)
    EndIf
EndFunc   ;==>refreshOexcel

water · April 19, 2015

Use a COM error handler to catch such errors. Check ObjEvent in the help file.

lorenkinzel · April 19, 2015

That did stop the crash.

I could not seem to get the _ErrFunc() to run without my Rube Goldberg style workaround.

I appear to be missing a point.

setZero is the func of interest.

Edit: thanks for the reply & help Water.

Func readZero();====================================================set global variable for zero co-ordinates
    $zeroX = GUICtrlRead($setFixedZeroX_Input)
    $zeroY = GUICtrlRead($setFixedZeroY_Input)
    $zeroX_Coordinate = $zeroX; a global
    $zeroY_Coordinate = $zeroY
EndFunc   ;==>readZero

Func setZero();====================================================get co-ordinates of selection   ;still lacking the all-important lower-left feature
    Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc");requires much more study of ObjEvent
    $tempZero_X = GUICtrlRead($setFixedZeroX_Input); a global
    $tempZero_y = GUICtrlRead($setFixedZeroY_Input)
    Sleep(100)
    With $oExcel
            $xLocation = .Selection.ShapeRange.Left / 1.6048; works fine without the "if"
            $yLocation = .Selection.ShapeRange.Top / 1.604995; works fine without the "if"
    EndWith
    $xLocationRounded = Round($xLocation, 3)
    $yLocationRounded = Round($yLocation, 3)
    GUICtrlSetData($setFixedZeroX_Input, $xLocationRounded)
    GUICtrlSetData($setFixedZeroY_Input, $yLocationRounded)
    readZero()
    If $zeroX_Coordinate = 0 And $zeroY_Coordinate = 0 Then
        _ErrFunc()
    EndIf
EndFunc   ;==>setZero

Func _ErrFunc();($oError)$sel
    $xLocation = $tempZero_X
    $yLocation = $tempZero_y
    $xLocationRounded = Round($xLocation, 3)
    $yLocationRounded = Round($yLocation, 3)
    GUICtrlSetData($setFixedZeroX_Input, $xLocationRounded)
    GUICtrlSetData($setFixedZeroY_Input, $yLocationRounded)
    readZero()
EndFunc

Edited April 19, 2015 by lorenkinzel

Sign In

Excel vba - au3 no object selected

Recommended Posts

lorenkinzel 17

Share this post

Link to post

Share on other sites

water 2,117

Share this post

Link to post

Share on other sites

lorenkinzel 17

Share this post

Link to post

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Browse

AutoIt Resources

Release

Beta