Posts by Tom Rowe

    Hi Rory,

    Yes I have discovered that VBA Code Obsfucation is not an exact science.:)


    In the project I was obfuscating I had an onLoad event. Which I don't actually do anything with but always include if I want to do so in the future.

    Code
    1. <customUI onLoad="ConwaysGameOfLifeRibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    2. <ribbon startFromScratch="false">
    3. <tabs>
    4. <tab id="conways_game_of_life" label="Conway's Game of Life">

    So I deleted it and you guessed it I no longer get the two 400 Errors. So most probably as you suggested the Ribbon was still executing the callback. The reason there were two was I have two instances of the project to obsfucate open. The initial a Read Only which I then do a SaveAs for the obsfucated version.


    On an even happier note the Conway's Game of Life project obsfucated successfully on my latest VBA Code Obsfucator version and all the code worked perfectly.


    Regards,


    Tom Rowe

    Hi Rory,


    Regarding : Curious as to how the ribbon callbacks could work if you give all procedures random names?


    By changing the callbacks manually in the Ribbon XML Files to the random names which I keep a log of. Or alternatively don't change them. But haven't got the project to the state yet where I can successfully compile and run the obsfucated code. :)


    Regards,


    Tom Rowe.

    Hi,


    I am currently working on a a project to obsfucate VBA code I use the following code from the late Chip Pearson's article on 'Programming The VBA Editor'.


    Chip Pearson - Programming The VBA Editor

    http://www.cpearson.com/excel/vbe.aspx


    to get the project modules which I then loop through to obsfucate the code by generating random names for the projects variables and procedures.

    The code is called by...

    Code
    1. GetModules objWorkbookToObsfucate

    This works fine, until I try obsfucating a project that has a custom ribbon. When I receive two 400 Errors :( when ThisWorkbook is accessed. If I remove the Custom Ribbon XML I don't get the errors. The ribbon code for the project all works perfectly. So failing to understand why I am getting the 400 Errors ?


    Probably a bit of a long shot but was wondering if anyone has experienced a similar problem ?


    Regards,


    Tom Rowe

    Hi,


    rngFoundCell is not nothing is determined by the .Find which returns nothing if the What:= item is not found.


    I have updated the code for you to handle the Job #. Also the code is now more generic so you can pass a worksheet object to the subroutinest specifying what Worksheet you want. I also found a minor bug with the sort routine in that it was using the wrong worksheet to set the Last Row on this line...


    Code
    1. .SetRange Range("A2:C" & Trim(Str(LastRow(objOrderWorksheet, 1))))


    Which may have been causing your problem.


    I have attached an updated workbook for you.


    Regards,


    Tom Rowe...


    The updated VBA Code follows...


    Hi,


    Don't know what's causing the error for you as the sorting works fine for me. Did you delete the X's from the Order column before you ran it which could cause problems ? If you can post a debug of the code where it is failing I will have a look to see if I can replicate it or add some error handling to get more information. Also what version of Excel are you using which can cause problems if different from mine which is Office 365 Excel 2016.


    It should be relatively simple to add the job Id as that would be done last when you check what order length matches what combination. I was also thinking it would be better to make the subroutine AddBestCombinationRowToOrder that does that more generic so you can pass a worksheet object to it specifying what Worksheet you want the order on rather than being limited to wsOrder.


    Regards,


    Tom

    Hi,


    To accomplish exactly what you are after is quite difficult as you have a lot of variables to deal with.


    As it was a typical June rainy day yesterday here in the UK and I like a challenge thought I would have a stab at trying to do this.


    My solution uses a SQL cross join query. A cross join (or cartesian join) is used when you wish to create combination of every row from two or more tables. All row combinations' are included in the result. The tables used for the cross join are just a copy of the order data. You have to specify the number of lengths you want to cut from a stock pieces i.e. in my code below it is 4 so 4 tables are create and the SQL cross join query is...


    SQL
    1. SELECT * FROM [B:B], [C:C], [D:D], [E:E]


    The solution is not 100% perfect as some of the order lengths don't fit a generated combination. But I guess you could always try running it with different parameters or work out the remaining lengths yourself. You don't want to do yourself completely out of your job... :smile:


    I have attached an example 2016 Workbook 'Best Combinations.xlsm' with the working code.


    Regards,


    Tom Rowe...


    VBA Code Follows...


    Hi,


    Try something along the following lines...



    Note: The code assumes that the column headings are in row 1.


    I have attached an example workbook 'Check and Insert Missing Columns.xlsm' with the above code.


    Regards,


    Tom Rowe...

    Hi,


    Perhaps just try looping through all the columns, something along the following lines...



    Note: The above is off the top of my head so not tested.


    Regards,


    Tom Rowe...

    Hi,


    The way to accomplish this to have a For Next Loop to loop through the ActiveSheet shapes. With a


    Code
    1. Select Case objWindowDoorType.Fill.ForeColor]


    with Case that checks for the RGB fill ForeColor i.e.


    Code
    1. Case RGB(255, 255, 0) ' Yellow - Glazing Bead - Replacable


    From the Case you then call a subroutine to count the glazing beads according to the Window and Door Type.


    Code
    1. CountGlazingBeads objWindowDoorType.TextFrame2.TextRange.Text


    Note : Because you are reliant on checking the shape's RGB fill ForeColor it's important that you use the actual colour to fill the shape.


    The following code should accomplish what you are after...



    Note: You didn't say how many glazing beads there are for each Window and Door type so I set arbitrary values in subroutine CountGlazingBeads. So these need to be set to their correct values.


    The beauty of this approach is that you can count any other required items for the Windows and Doors i.e. Handles, Hinges, Locks etc by simple creating and calling a subroutine for it from the ActiveSheet shapes For Next Loop.


    I have attached an Excel 2016 32Bit workbook 'Object Sample Sheet.xlsm' with working code for you.


    Regards,


    Tom Rowe...

    Hi,


    Firstly the structure of your workbook worksheet doesn't lend itself to doing this type of thing easily so I have altered it.


    The following code should accomplish what you are after...



    Note : There are two modes ' Delete the moved row mode' or '' Don't delete the moved row mode' depending on the setting of wsTest.chkDeleteSample.


    I have attached an Excel 2016 32Bit Workbook 'Trends.xlsm' with the working code.


    Regards,


    Tom Rowe...

    Files

    • Trends.xlsm

      (46.58 kB, downloaded 106 times, last: )

    Hi,


    You seem to be over complicating things by using Visual Studio. An Excel Ribbon is essentially just an XML file I build them for my projects and Add_ins in NotePad ++ i.e.


    <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false">
    <tabs>
    <tab id="UK_postcode_search_and_geocoding_ribbon" label="UK Postcode Search and Geocoding">
    <group id="Group01" label="Postcode Search and Geocoding">
    <button id="myButtonG0101" label="Postcode Search" imageMso="WebOpenSearchPage" size="large" onAction="rbnPostcodeSearch" supertip="UK postcode search." />
    <button id="myButtonG0102" label="Postcode Geocoding" imageMso="CreateMap" size="large" onAction="rbnPostcodeGeocoding" supertip="UK postcode geocoding." />
    </group>
    <group id="Group02" label="OS Code-Point Open Data">
    <button id="myButtonG0201" label="Code-Point Open Data" imageMso="PublishWizard" size="large" onAction="rbnOpenCodePointOpenFolder" supertip="Open the OS Code-Point Open data folder." />
    </group>
    <group id="Group03" label="UK Postcode Google Maps">
    <button id="myButtonG0301" label="Show on Map" imageMso="ClipArtInsert" size="large" onAction="rbnPostcodeGoogleMaps" supertip="Show Postcode on Google Maps." />
    <button id="myButtonG0302" label="Show on Street View" imageMso="RmsNavigationBarHome" size="large" onAction="rbnPostcodeGoogleStreetView" supertip="Show Postcode on Google Street View." />
    <button id="myButtonG0303" label="Show on Satellite View" imageMso="QuickPartsInsertFromOnline" size="large" onAction="rbnPostcodeGoogleSatelliteView" supertip="Show Postcode on Google Satellite View." />
    <button id="myButtonG0304" label="Postcode GPS Coordinates" imageMso="GroupCachedModeStatus" size="large" onAction="rbnPostcodeGoogleGPS" supertip="Get postcode GPS coordinates from Google Maps API." />
    </group>
    <group id="Group04" label="Manage Application">
    <button id="myButtonG0401" label="Close UK Postcode Search and Geocoding" imageMso="BroadcastEnd" size="large" onAction="rbnCloseWorkbook" supertip="Close UK postcode search and geocoding." />
    </group>
    </tab>
    </tabs>
    </ribbon>
    </customUI>


    OnAction is the name of the Macro you want to run from the ribbon.


    From here rename your excel file to a .zip extension and copy the xml ribbon file to the root folder.


    Then create an xml .rels file to point at xml ribbon file i.e. Target="UK_postcode_search_and_geocoding_ribbon.xml.


    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
    <Relationship Id="customUIRelID" Target="UK_postcode_search_and_geocoding_ribbon.xml" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility"/>
    </Relationships>


    which you copy to the _rels folder.


    If you do a Google Search for creating excel ribbons there are plenty of examples on how to do this.


    Also there is a very good Add-in for the Microsoft Office built in Ribbon Icons available at...


    https://www.spreadsheet1.com/how-to-...-in-excel.html


    You can also have your own custom icons but that gets a little bit more complex as you have to set up a CustomUI folder for them within the zip file and have references to these in a custom .rels file.


    Finally if you post a copy of your Workbook you will be more likely to get exact help on how to do this.


    Regards,


    Tom Rowe...

    Hi,


    I suggest defining the Lookup Value as a Variant type i.e.


    Code
    1. Dim lotnum As Variant


    That way it is not dependent on the cell format in the look up array. i.e. suppose the cell format in the lookup array is Text and the Lookup Value is an Integer type you will get an error.


    Also define the Return Variable Lot_List as either a Double or Long Type.


    Regards,


    Tom Rowe...

    Hi,


    Try the following...



    I have attached an Excel 2016 Workbook 'copy_to_another_sheet.xlsm' with the working code...


    Regards,


    Tom Rowe...