Posts by AndrewMB

  • Windows 10 update breaks VBA

    Windows 10 [FONT="Calibri"]version 1903 build 18362.295 [/FONT]has the error. Earlier versions do not.


    Microsoft know about the problem and are working on a fix.


    Interestingly, an empty array generated by Array() produces the error. An empty array generated by Split("","|") does not.

  • Windows 10 update breaks VBA

    The latest Windows 10 update has apparently introduced a serious bug in VBA (I have tested this in Office 2010 and Office 365 so probably applies to all versions). It now seems that if a variant contains an empty array, attempting to pass this to a second variant produces an error. See below;


    [VBA]Sub test()


    Dim x As Variant, y As Variant
    x = Array()
    y = x


    End Sub


    Sub test2(ParamArray pp())


    Dim x As Variant, y As Variant
    x = pp
    y = x


    End Sub


    Sub test3()


    test2
    End Sub
    [/VBA]


    Both test1 and test3 produce Run Time error 5 Invalid Procedure Call or Argument. The second example - a ParamArray with no parameters supplied - must be a rather common situation so I suspect a lot of code is going to break... Does anyone know how to get Microsoft to do something about this?


    Andrew

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    [ATTACH=CONFIG]65666[/ATTACH]


    Easily done, please see attached. I have added this as an optional parameter to the Parameters function, which is now =Parameters(GroupName,MinimumValue,MaximumValue,Modifier,HideOval,CurrentValue). This (like all the parameters except GroupName) can be included or omitted, if you omit a parameter then its existing value is unchanged. Alternatively if you want to be able to sometimes change the initial value without changing the formula, then setting this parameter to an empty string ("") will leave it unchanged. You'll need to avoid conflict with clicking the control, as if you leave the initial value in the formula, the control will revert back to this value every time the cell recalculates. So you'll need a formula in this parameter so it knows whether to initialise the current value or not.


    Best Regards

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    [ATTACH=CONFIG]65657[/ATTACH] Please see attached. Some intructions:


    You can have as many instances of the control as you want, on the same or different worksheets. I have three on the attached. To delete an instance, select it (right click) and press delete. To create a new instance, select an existing control and click on Copy. Then select the cell where you want the new one and run the macro Paste_Shape (easiest to run directly from the Macros button on the Developer tab). Don't paste it using a normal Paste as this does not name or initialise the new instance correctly and you'll find it doesn't work. You'll be able to click the new instance immediately and it should respond.


    You can move and resize the control as with any shape. As it is grouped, all the shapes within it will resize correctly when the group is resized. The font size in the oval will adjust itself as soon as the control is clicked.


    To get the value of the control onto the worksheet, first check the name of the control - this will be displayed when you select it, and will be Group followed by a number. I do not recommend changing the name, as this can cause problems when copying the group (see below). If (for example) the name is Group 53, then create a cell named Group_53_Current and/or Group_53_Modified. Whenever the value is changed by clicking the control, these cells are immediately updated and can be used in formulae throughout the workbook (preferably by name reference). This is just like the linked cells of normal controls.


    To adjust the values of a control's parameters, set up a formula somewhere =Parameters(GroupName,MinimumValue,MaximumValue,Modifier,HideOval) where the first parameter is the name of the control, the next 3 parameters are numeric values of the minimum value, maximum value and modifier, and the last is TRUE or FALSE to hide the oval. The function returns the value 1 if it runs successfully, and whenever it calculates the appropriate parameters are adjusted and the shape will change accordingly.


    If you want to modify the design of a control, this time copy it and paste it somewhere (don't run the Paste_Shape macro). Ungroup the new instance, then you can move or copy individual small circles. However it is essential that they all remain exactly the same size and that all the circles which are meant to be in the same row are accurately aligned - use the Align buttons to ensure this is the case. Finally select all the sub-shapes and group them. Check that the group has assigned macro Control_Click. You should then be able to click your new control and it should work correctly.


    If you want to transfer this to a different workbook, I suggest using the Paste_Shape macro as above. You'll then need to transfer all the code to the new workbook (including the class module, which must retain the same name), and in VBA use Tools References to set a reference to Microsoft Scripting Runtime. Finally change the assigned macro to point to the version in your new workbook.


    I've given it a fair bit of testing (using Excel 2007), but there's a lot of code, so let me know if you hit any snags.


    Best Regards

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    Mostly done, hope to finish tomorrow. One small change - a control will return it's value to the worksheet through an appropriately named cell. The reason for this rather than a user-defined function is that a user-defined function won't recalculate because a user has clicked on a control, so would retain its old value till something else forces a recalculation. So a better alternative is to allow you to identify cells where the current value (or modified value) will go, these will be immediately updated when a control is clicked and of course can be subsequently used in any calculations.


    Best Regards

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    Thanks, that's fine, I can make a start.


    What you are going to end up with is a set of shapes on a worksheet, which will be grouped (and which, having done some testing, can stay grouped throughout). So you can copy this group anywhere in the workbook, resize as required and have as many instances as you wish. The user will interact with this object by clicking on any of the circles.


    In the first instance, I will provide a set of procedures which can be called in VBA to set any of the parameters, or to get the current value. These will be generalised (to work on any instance) and will identify the object by its group name (visible when the object is selected). This essentially provides a toolkit for integration.


    In order to return the current value to a cell in the worksheet, the appropriate procedure can just be used directly as a user-defined function. In order to set parameters from a cell on a worksheet, it should also be possible to do this with a user-defined function . So a user-defined function can have the parameters of the control as its parameters, and whenever Excel recalculates it, the control will adjust (Excel restricts what can be done in a user-defined function but I've run some tests and it does appear to be possible to make alterations to shapes, so I'll try this method). This should allow you to link the controls to the worksheet as you wish.


    In order to create alternative versions with dfferent numbers/positions of the circles, you will need to do this by modifying the original. It should be simple enough, I can give you some guidance on what you need to do to ensure that the code will still work on any modified version.


    Best Regards

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    The terms of use of this forum state "PLEASE PAY (via PayPal) 10% of your cost to [email protected] BEFORE you post. Then pay the remaining 90% to the person who completes the job on completion". Naturally you need to be happy that the solution does what was agreed. And the reason for my questions in my previous post is to enable me to ensure you are happy with the result.

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    No problem, I'll wait to hear from you.


    It would also be helpful if you could give me a little information about the context:
    - what is the purpose of the control?
    - if on a worksheet, what else will be on this or other worksheets?
    - is there just one instance of your control or many?
    - what do you want to be able to do with the result of the user's selection?
    - any other information on how you might want to use the control?


    All these would be helpful in the design of the solution.

  • USD $20.00: New Skill-Dot User Control for VBA

    Re: USD $20.00: New Skill-Dot User Control for VBA


    Creating a new custom control is certainly a complex task, it can be done in .NET but is not possible in VBA.


    However, if this is wanted on a worksheet rather than a userform, it should be possible to produce the effect you want with shapes. A 'master template' version could be created with 10 individual circles + oval, grouped. This could be copied to anywhere required and resized, which would preserve aspect ratio. VBA code could then be used to ungroup and assign code to each individual circle, so the individual circles would be clickable and the code could fill/unfill them as you describe. Associating a class module with the group would enable initial parameters to be set and could support multiple instances through custom events.


    However this is a significant amount of work, I'd think $100 may be more realistic though someone else might wish to pick it up for the amount you are offering.

  • $25 Excel Keyboard

    Re: $25 Excel Keyboard


    Unfortunately I don't have the TM1 addin, so I can't test this myself. However loading an addin in VBA just requires one line of code, but it depends which type of addin the TM1 addin is either an Excel addin (which is a workbook) or a COM addin (which is likely to be DLL file), as the code is different for each. When you click Excel Options, Addins, you get the option to choose whether to manage Excel Addins of COM addins - you can see what type of addin you have by which list it is in.

  • $25 Excel Keyboard

    Re: $25 Excel Keyboard


    I understand the problem is that your workbook in the startup directory opens and its code runs before the TM1 addin loads; How about explicitly opening the addin within your code rather than have it open automatically, then switching on the NUMLOCK key?


    Your code will probably have to be an add-in which remains loaded so it can also unload/disconnect the addin before it (and Excel) are closed.


    The code is slightly different to load an Excel addin and for a COM addin, which do you have?

  • $20,merge .cvs files, add column with source file names, output over 1 million rows

    Re: $20,merge .cvs files, add column with source file names, output over 1 million r


    Just a couple of observations...


    The simplest solution is probably to process the files entirely in VBA using Line Input, dropping the headers on all except the first (assuming all files have the same headers) and adding the extra field. Since no data would be loaded into Excel, Excel limits would not apply.


    If a csv file is loaded into Excel and saved again as a csv file (using any of the several ways of doing this), the output will be different from the input, which may or may not matter depending on how 'fussy' is the program which eventually receives the file. Examples: different logic for quoting text fields, anything which looks like a number is treated as a number and so long numbers get rounded or converted to E format. There are workrounds for these, but keeping the processing in code avoids these problems and preserves the original records.