Questions about Addin Express Toys for Excel

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi,

I am studying this sample:
Add-in Express Toys?В?Ф?? .NET for Microsoft Excel
https://www.add-in-express.com/free-addins/net-excel-addin.php

If you go to this page, you will see the picture which I am discussing now.

There are 2 buttons near the LHS of the Ribbon:
1. Wrap Text
2. Shrink to Fit.

2 relevant codes for the "Shrink to Fit" button are shown below:

'1st code
    Private Sub RibbonButtonShrinkToFit_OnClick(sender As System.Object, _
     control As AddinExpress.MSO.IRibbonControl,pressed As System.Boolean) _ 
     Handles RibbonButtonShrinkToFit.OnClick

      btnShrinkToFit_Click(Nothing)     'it calls the 2nd code
                        
    End Sub

'2nd Code
    Private Sub btnShrinkToFit_Click(ByVal sender As Object) _
       Handles btnShrinkToFit.Click

        DoCellFormat(XldCellFormat.cfShrinkToFit)  
        
    End Sub

Both codes are Event Handlers and respond to click events.

Code 1 is the Event Handler for the "Shrink to Fit" button.

Code 1 calls Code 2 which calls DoCellFormat.

Code 1 Handles RibbonButtonShrinkToFit.red, whereas
Code 2 Handles btnShrinkToFit.red

Here are my questions:

red

Seems very puzzling to me. Hope you can enlighten me.

Thanks
Leon

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Leon,

Note the Handles clause which shows what event of what object these methods handle. These objects are of different types and they do have different event names! While RibbonButtonShrinkToFit is obviously of the ADXRibbonButton type; btnShrinkToFit should be of the ADXCommandBarButton type. The latter is used in Office 2000-2003.

Leon Lai Kan writes:
But what triggers Code 2. Which button (or other object) is clicked to trigger code 2?

There are two scenarios:

1) You click RibbonButtonShrinkToFit; this invokes RibbonButtonShrinkToFit_OnClick, which calls btnShrinkToFit_Click().
2) You load the add-in in Office 2000-2003 and click btnShrinkToFit; this invokes btnShrinkToFit_Click().

Andrei Smolin
Add-in Express Team Leader

PS. We suppose the problem that you see when you click Modify is caused by using a symbol missing in windows-1250 encoding.

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi, Andrei

Because I am only interested developing in Excel 2010 and above, I am concentrating my attention on the Ribbon, and forget considering the Command Bar!

Very insightful explanation.

Now I understand (a bit) how ADX implements version neutrality!

Thanks and Best Regards,
Leon

-----
Concerning my Office Computer.
I did a complete virus scan all night.
This morning, it is working properly. I can modify my message.
-----

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi, Andrei

I am studying the Paste Special Ribbon Menu in the sample.

I find the Sub DoPasteSpecial a bit difficult to understand because the Sub mixes the code for the Paste Special Menu with the code for the 7 buttons to its right.

The Paste Special Menu has the following choices:

-- All --------------------- xlPasteType = -4104
-- All Except Borders ------ xlPasteType = 7
-- Formulas ---------------- xlPasteType = -4143
-- Values ------------------ xlPasteType = -4163
-- Formats ----------------- xlPasteType = -4122
-- Comments ---------------- xlPasteType = -4144

Below are the codes for Paste Special Formulas as an example:

' Ribbon: Paste Formulas

    Private Sub RibbonButtonFormulas_OnClick(sender As System.Object, _
      control As AddinExpress.MSO.IRibbonControl, _
      pressed As System.Boolean) _
      Handles RibbonButtonFormulas.OnClick

      btnFormulas_pp_Click(Nothing)     
    End Sub

' Command Bar: Paste Formulas

    Private Sub btnFormulas_pp_Click(ByVal sender As Object) _ 
      Handles btnFormulas_pp.Click
      DoPasteSpecial(Excel.XlPasteType.xlPasteFormulas)
    End Sub

And here is the Sub DoPasteSpecial:

Private Sub DoPasteSpecial(ByVal paste As Integer)
        Dim range As Excel.Range = GetSelectedRange()

        If range IsNot Nothing Then
            Dim operation As Excel.XlPasteSpecialOperation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone
            If Me.HostMajorVersion > 11 Then ' 9 = Excel 2000, 10 = XP, 11 = 2003, 12 = 2007 (ribbon), 14 = 2010, 15 = 2013
                ' ribbon controls
                For i As Integer = 71 To 75
                    If Me.FindRibbonControl(i).Pressed Then
                        Select Case i
                            Case 71
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone
                            Case 72
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd
                            Case 73
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationSubtract
                            Case 74
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationMultiply
                            Case 75
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationDivide
                        End Select
                        Exit For
                    End If
                Next i
            Else
                ' command bar bontrols
                For i As Integer = 71 To 75
                    If ControlByTag(i.ToString()).State = AddinExpress.MSO.ADXMsoButtonState.adxMsoButtonDown Then
                        Select Case i
                            Case 71
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone
                            Case 72
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationAdd
                            Case 73
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationSubtract
                            Case 74
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationMultiply
                            Case 75
                                operation = Excel.XlPasteSpecialOperation.xlPasteSpecialOperationDivide
                        End Select
                        Exit For
                    End If
                Next i
            End If

            Dim skipBlanks As Boolean
            Dim transpose As Boolean
            If Me.HostMajorVersion > 11 Then ' 9 = Excel 2000, 10 = XP, 11 = 2003, 12 = 2007 (ribbon), 14 = 2010, 15 = 2013
                ' ribbon controls
                skipBlanks = Me.FindRibbonControl(76).Pressed
                transpose = Me.FindRibbonControl(77).Pressed
            Else
                ' command bar bontrols
                skipBlanks = (ControlByTag("76").State = AddinExpress.MSO.ADXMsoButtonState.adxMsoButtonDown)
                transpose = (ControlByTag("77").State = AddinExpress.MSO.ADXMsoButtonState.adxMsoButtonDown)
            End If
            Select Case paste
                Case xlLinks
                    Dim objParent As Object = range.Parent
                    If TypeOf objParent Is Excel._Worksheet Then
                        objParent.Paste(Type.Missing, True)
                    End If
                    Marshal.ReleaseComObject(objParent)
                Case Else
                    range.PasteSpecial(paste, operation, skipBlanks, transpose)
            End Select
            range = Nothing
        End If
    End Sub

My Question:

red

Just copy and paste the relevant lines in your reply. Will be OK for me.

I understand that clicking the Formula sub-menu will send the integer -4123 to DoPasteSpecial (ByVal paste As Integer)

I suppose the answer is here:

Case Else
     range.PasteSpecial(paste, operation, skipBlanks, transpose)

but not sure...

Thanks
Leon

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Leon,

That method prepares data to pass to range.PasteSpecial(), which is part of the Excel object model.

To get assistance with host applications?В?Р?й objects, their properties, and methods as well as help info, use the Object Browser. Go to the VBA environment (in the host application, press {Alt+F11}), press {F2}, select the host application in the topmost combo and/or specify a search string in the search combo. Select a class /property /method and press {F1} to get the help topic that relates to the object.

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Andrei,

Thanks

I have been studying those Paste Special which have an xlPasteType.

Apparently, the following don't have an xlPasteType (at least I have not seen them in Object Browser):

Private Const xlLinks As Integer = &HFF
Private Const xlColumnWidths As Integer = 8
Private Const xlDataValidation As Integer = 6

red

Thanks
Leon
------

red

ok, I googled for &HFF and found that it meant 255. It can also mean the hexadecimal representation of a color.
red

-------

red

The 5 Paste Action buttons (Just after the Paste Special Menu) don't seem to be working.
I click on them; nothing happens.

See the picture here:
https://www.add-in-express.com/free-addins/net-excel-addin.php

I reproduce the relevant codes (slightly modified to simplify my question) below so that you can easily what's going on.
I removed the Command Bar codes as I am only interested in Ribbon Codes.

'Ribbon Button: Paste Add

Private Sub RibbonButtonPasteAdd_OnClick _
  (sender As System.Object, _ 
   control As AddinExpress.MSO.IRibbonControl, _ 
   pressed As System.Boolean) _ 
  Handles RibbonButtonPasteAdd.OnClick

  DoPastAction(sender)    

End Sub

There are similar Subs for None, Subtract, Multiply, Divide. Not shown here

Private Sub DoPastAction(ByVal sender As Object)

        If TypeOf sender Is AddinExpress.MSO.ADXCommandBarButton Then
           ' leon deleted the codes for Command Bar            

        ElseIf TypeOf sender Is AddinExpress.MSO.ADXRibbonButton Then

            If sender.Pressed Then
                For i As Integer = 3 To 7
                    If RibbonButtonGroup2.Controls(i) IsNot sender Then
                        RibbonButtonGroup2.Controls(i).AsRibbonButton.Pressed = False
                    End If
                Next i
            Else
                RibbonButtonPasteNone.Pressed = True
            End If
        End If
    End Sub

red

red

Thanks
Leon

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Leon Lai Kan writes:
What was the idea using &HFF instead of the more direct 255 in your Addin Toy?

This integer value is sort of a flag that DoPasteSpecial() uses to invoke Worksheet.Paste(Link: True). Introducing that flag allows us to uniformly invoke DoPasteSpecial() in the Click events of all buttons. Being a flag, the value itself doesn't make real sense; you must ensure however that it doesn't match any other value passed. To highlight the idea the idea that the value itself is not that relevant, the source code uses xlLinks rather than its value. As to using &HFF, on one hand, this is a matter of taste. On the other hand, &HFF better reflects the idea of the value being used as a flag.

Leon Lai Kan writes:
ok, I googled for &HFF and found that it meant 255

Use the calculator in Windows.

Leon Lai Kan writes:
Please help.Seems a code is missing in DoPastAction() to perform the action.

Does the original code work for you?

Leon Lai Kan writes:
What do the 5 integers 3 to 7 represent?

These are indexes used with the RibbonButtonGroup2.Controls collection; these integers reflects the Ribbon controls used and their posistioning in the Ribbon UI.

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi, Andrei

Thanks for your reply. I have completely rewritten my comments to shorten them.

Does the original code work for you?

No. I downloaded your sample file anew. It does not work.
I also downloaded your plugin. It also does not work.

What is not working?
The last 7 buttons on the 2nd row of the Ribbon do not work at all.

Why do they not work?
Don't know.I tried to analyse the codes and found the following:

(a) The 1st 5 Operation buttons:
The click events call Sub DoPastAction().
In my opinion, they should have called DoPasteSpecial() instead.

(b) The last 2 buttons (Skip Blanks and Transpose)
It seems that the author has omitted to put the code in the event handlers (ribbon).

I think it would be simpler for you to modify your sample and make it work.

A suggestion: Please add Option Strict On because many codes are converted implicitly.
If I Set Option Strict On, I will have to modify many codes. I am not sure I can do that.

Best Regards,
Leon

Andrei Smolin

Add-in Express team

Posts: 18821
Joined: 2006-05-11

Hello Leon,

Well, I'll look in this project. I expect that I'll provide a result next Monday.

Andrei Smolin
Add-in Express Team Leader

Leon Lai Kan

Posts: 200
Joined: 2018-12-20

Hi, Andrei

Thanks and I hope you will make the toy work properly. For toys are great learning materials!

If I post several questions on the same topic, can I post them on the same thread, or would you prefer me to post them on new threads?

Leon