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

water

lorenkinzel

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta