In the last post I went through the steps for creating a class library for use with ManagedXLL to create an Excel add-in. We very briefly encountered the ManagedXLL way of marking methods for use in Excel. <\/p>\n
Now I’m going to delve a little deeper into what we can do with ManagedXLL. This will not be comprehensive, but will highlight some of the key features of ManagedXLL.<\/p>\n
ExcelAddInEvent<\/strong><\/p>\n We can mark a method with this attribute to carry out a task when the add-in is loaded (AddInEvent.Open), allowing us to carry out specific functionality, such as maybe getting an SSO token for the user or the likes. We can also use mark a method to be called when an add-in is being closed, i.e. when a workbook is closed, for example to allow us to save data to the cloud using AddInEvent.Close. We can also use AddInEvent.Add and AddInEvent.Remove to carry out a task when the add-in is added to the add-in manager or removed from it.<\/p>\n A couple of subclasses exist to handle Open and Close, named ExcelAutoOpen and ExcelAutoClose.<\/p>\n Excel Object<\/strong><\/p>\n Before moving onto more attributes, let’s quickly look at the ManagedXLL Excel object. It includes methods to interact with Excel from our code, such as displaying a message box in Excel or getting at the sheets collection and so on, for example<\/p>\n WorksheetFunction<\/strong><\/p>\n The WorksheetFunction attribute is used to tell Excel, not only the name of the function (as seen by Excel users), but also how Excel should use the method in the sense of how it should display exceptions and whether to detect the user aborting of the method.<\/p>\n ExcelDescription<\/strong><\/p>\n We can mark methods, arguments and return values with the ExcelDescription attribute, allowing us to document what the various arguments and\/or return values of a method are as well as what the method itself does.<\/p>\n ExcelHidden<\/strong><\/p>\n We can mark methods and hidden to hide them from the function wizard using the ExcelHidden attribute, this doesn’t stop us using the method, it just doesn’t appear in the function wizard<\/p>\n ExcelCategory<\/strong><\/p>\n The ExcelCategory is used on a class and allows us to create a category for groups of functions – this aids the function wizard in grouping the methods. You can create multiple classes and give them the same category or group together specific functionality into their own categories.<\/p>\n ExcelAbbreviation<\/strong><\/p>\n We can mark individual arguments of a method using this attribute. In essence we’re defining an alternate name for the parameter as viewed by Excel’s function wizard.<\/p>\n ExcelCommand<\/strong><\/p>\n The ExcelCommand attribute allows us to define methods that are registered as Excel macro functions which can be run from the command’s listed in the Managed.XLL.config<\/p>\n This will add a command to the Excel menu and when clicked will run our macro\/<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" In the last post I went through the steps for creating a class library for use with ManagedXLL to create an Excel add-in. We very briefly encountered the ManagedXLL way of marking methods for use in Excel. Now I’m going to delve a little deeper into what we can do with ManagedXLL. This will not […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[54,57],"tags":[],"class_list":["post-797","post","type-post","status-publish","format-standard","hentry","category-excel","category-xll"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/797","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=797"}],"version-history":[{"count":14,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/797\/revisions"}],"predecessor-version":[{"id":841,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/797\/revisions\/841"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\n[ExcelAddInEvent(AddInEvent.Open)]\r\npublic static void OnLoaded()\r\n{\r\n}\r\n<\/pre>\n\r\nExcel.MessageBox("You are not authorised to connect to the selected service", Excel.MessageBoxType.ErrorOK, "MyExcelAddin");\r\n<\/pre>\n\r\n[WorksheetFunction("CalculateFee", DetectUserAbort = true, Exceptions = Echo.Verbatim)]\r\npublic static double CalculateFee()\r\n{\r\n}\r\n<\/pre>\n\r\n[ExcelDescription("Gets a string based upon the supplied index")]\r\n[return: ExcelDescription("Returns the selected string")]\r\npublic static string DoSomething([ExcelDescription("The Index")] int index)\r\n{\r\n}\r\n<\/pre>\n\r\n[WorksheetFunction("MyName", DetectUserAbort = true, Exceptions = Echo.Verbatim)]\r\n[ExcelDescription("Returns a string with the name of the add-in.")]\r\n[return: ExcelDescription("A string representing the name of the add-in")]\r\n[ExcelHidden]\r\npublic static string Name()\r\n{\r\n}\r\n<\/pre>\n\r\n[ExcelCategory("My Date & Time Functions")]\r\npublic static class DateTimeFunctions\r\n{\r\n}\r\n<\/pre>\n\r\npublic static string Name([ExcelAbbreviation("Name")] String enterYourName)\r\n<\/pre>\n\r\n[ExcelCommand(".Run")]\r\npublic static void Run()\r\n{\r\n}\r\n<\/pre>\n\r\n<command title="My Run Command" macroName=".Run"\r\n description="Runs a command" edition="Professional"\/>\r\n<\/pre>\n