{"id":1606,"date":"2016-12-20T17:45:48","date_gmt":"2016-12-20T17:45:48","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=1606"},"modified":"2022-02-03T11:24:32","modified_gmt":"2022-02-03T17:24:32","slug":"vsto-vba-guideline-compile-test","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/","title":{"rendered":"VSTO to VBA Guideline to Compile and Test"},"content":{"rendered":"<p><span style=\"font-weight: 300;\">Common Error when compiling the VBA using the VSTO is found <\/span><a style=\"font-weight: 300;\" href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/office\/gg264504.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a><span style=\"font-weight: 300;\">. In order to fix it, I changed the long parameter in the VSTO to Int32. Then the VBA compiled fine.<\/span><\/p>\n<p>Have to close excel in order to build the project.<\/p>\n<p>Don&#8217;t need a public interface. Only these lines of code before the public worksheet object:<\/p>\n<p><code>[ComVisible(true)]\n[ClassInterface(ClassInterfaceType.AutoDual)]<\/code><\/p>\n<p>In order to add a reference to the VSTO you must click on &#8220;Register for COM interop&#8221;. This creates a .tbl file that is the reference that you need to add in order to use the VSTO. The &#8220;Register for COM interop&#8221; is found i the Project Properties -&gt; Build -&gt; Output.<\/p>\n<p>Once the reference is added (you may have to check it as well) to Excel, hit &#8220;F2&#8221; from the VBA code which opens the Object Browser. Navigate to the VSTO class and check that none of the Members has &#8220;Unknown&#8221; as a property. If so, then this is due to an improper variable type. See the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dd627739(v=office.12).aspx#VSTOForMereMortalsChapter4_LanguageDifferencesOfVBAAndVisualBasic2005\" target=\"_blank\" rel=\"noopener noreferrer\">Compatible Variable Types between VBA and VSTO link<\/a>.<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dd627739(v=office.12).aspx#VSTOForMereMortalsChapter4_LanguageDifferencesOfVBAAndVisualBasic2005\">\u00a0<\/a>Fix any unknown types in the VSTO code and recompile.<\/p>\n<p>In VBA I received the following error:<\/p>\n<p><a href=\"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture.jpg\"><img decoding=\"async\" class=\"alignnone wp-image-1627\" src=\"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg\" alt=\"capture\" width=\"274\" height=\"119\" \/><\/a><\/p>\n<p>In order to fix this, instead of using a cast to <code>(Int32)<\/code>, I used\u00a0<code>Convert.ToInt32<\/code>.<\/p>\n<ul>\n<li><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/dd627739(v=office.12).aspx#VSTOForMereMortalsChapter4_LanguageDifferencesOfVBAAndVisualBasic2005\" target=\"_blank\" rel=\"noopener noreferrer\">Compatible Variable Types between VBA and VSTO<\/a><\/li>\n<li>A basic diff beteen VBA and VSTO &#8211; a little to basic &#8211; but it is well written. <a href=\"http:\/\/checktechno.blogspot.ca\/2013\/04\/difference-between-vbnet-and-vba.html\">So here it is<\/a>.<\/li>\n<li><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/office\/gg264504.aspx\">Function marked as restricted error &#8230; (MSDN)<\/a><\/li>\n<li><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/tzat5yw6(v=vs.110).aspx\">Registering a file with COM (regasm.exe)<\/a><\/li>\n<li><a href=\"https:\/\/social.msdn.microsoft.com\/Forums\/vstudio\/en-US\/a2ff65d8-ff4d-4676-9eea-26c8e3ac147a\/expose-vsto-addin-to-vba-at-design-time?forum=vsto\">Expose VSTO addin to VBA (scroll down to July 13, 2012 &#8211; that started some success)<\/a>\n<ul>\n<li><a href=\"https:\/\/blogs.msdn.microsoft.com\/andreww\/2007\/01\/15\/vsto-add-ins-comaddins-and-requestcomaddinautomationservice\/\">VSTO Add-ins, COMAddins and RequestComAddinAutomationSerivice whitepaper by Andrew Whitechapel<\/a><\/li>\n<li><a href=\"http:\/\/www.stegriff.co.uk\/upblog\/add-a-reference-to-vsto-project-from-vba-editor\">Building a .tlb <\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/957575\/how-to-easily-create-an-excel-udf-with-vsto-add-in-project\" target=\"_blank\" rel=\"noopener noreferrer\">There might be some help in making UDF&#8217;s with VSTO<\/a> which is not straightforward.<\/li>\n<li><a href=\"https:\/\/exceldna.codeplex.com\/\" target=\"_blank\" rel=\"noopener noreferrer\">Excel-DNA is an interesting free idea<\/a>. It might have some solutions as they have gotten over some hurdles with UDFs etc.<\/li>\n<li><a href=\"https:\/\/www.add-in-express.com\/creating-addins-blog\/2013\/11\/05\/release-excel-com-objects\/\" target=\"_blank\" rel=\"noopener noreferrer\">Could it be 32bit<\/a> &#8211; WHOA &#8211; working &#8211; change the Regasm and the CPU to 32bit. Then we will try backing off as many 32 bit dependancies as possible. Ideally working in 64 bit would be better for pure VSTO.\n<ul>\n<li>it is 64 bit that make &#8220;unknown&#8221; in VBA&#8217;s object browser<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Common Error when compiling the VBA using the VSTO is found here. In order to fix it, I changed the long parameter in the VSTO to Int32. Then the VBA compiled fine. Have to close excel in order to build the project. Don&#8217;t need a public interface. Only these lines of code before the public [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1606","post","type-post","status-publish","format-standard","hentry","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>VSTO to VBA Guideline to Compile and Test - ELB Solutions.com Inc.<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VSTO to VBA Guideline to Compile and Test - ELB Solutions.com Inc.\" \/>\n<meta property=\"og:description\" content=\"Common Error when compiling the VBA using the VSTO is found here. In order to fix it, I changed the long parameter in the VSTO to Int32. Then the VBA compiled fine. Have to close excel in order to build the project. Don&#8217;t need a public interface. Only these lines of code before the public [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/\" \/>\n<meta property=\"og:site_name\" content=\"ELB Solutions.com Inc.\" \/>\n<meta property=\"article:published_time\" content=\"2016-12-20T17:45:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-03T17:24:32+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg\" \/>\n<meta name=\"author\" content=\"Etienne Bley\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Etienne Bley\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/\"},\"author\":{\"name\":\"Etienne Bley\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"headline\":\"VSTO to VBA Guideline to Compile and Test\",\"datePublished\":\"2016-12-20T17:45:48+00:00\",\"dateModified\":\"2022-02-03T17:24:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/\"},\"wordCount\":368,\"image\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/elbsolutions.com\\\/projects\\\/wp-content\\\/uploads\\\/2016\\\/12\\\/Capture-300x124.jpg\",\"articleSection\":[\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/\",\"name\":\"VSTO to VBA Guideline to Compile and Test - ELB Solutions.com Inc.\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/elbsolutions.com\\\/projects\\\/wp-content\\\/uploads\\\/2016\\\/12\\\/Capture-300x124.jpg\",\"datePublished\":\"2016-12-20T17:45:48+00:00\",\"dateModified\":\"2022-02-03T17:24:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#primaryimage\",\"url\":\"http:\\\/\\\/elbsolutions.com\\\/projects\\\/wp-content\\\/uploads\\\/2016\\\/12\\\/Capture-300x124.jpg\",\"contentUrl\":\"http:\\\/\\\/elbsolutions.com\\\/projects\\\/wp-content\\\/uploads\\\/2016\\\/12\\\/Capture-300x124.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/vsto-vba-guideline-compile-test\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"VSTO to VBA Guideline to Compile and Test\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\",\"name\":\"ELB Solutions.com Inc.\",\"description\":\"Bringing all your IT Pieces together\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\",\"name\":\"Etienne Bley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"caption\":\"Etienne Bley\"},\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/author\\\/etienne-bley\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"VSTO to VBA Guideline to Compile and Test - ELB Solutions.com Inc.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/","og_locale":"en_US","og_type":"article","og_title":"VSTO to VBA Guideline to Compile and Test - ELB Solutions.com Inc.","og_description":"Common Error when compiling the VBA using the VSTO is found here. In order to fix it, I changed the long parameter in the VSTO to Int32. Then the VBA compiled fine. Have to close excel in order to build the project. Don&#8217;t need a public interface. Only these lines of code before the public [&hellip;]","og_url":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/","og_site_name":"ELB Solutions.com Inc.","article_published_time":"2016-12-20T17:45:48+00:00","article_modified_time":"2022-02-03T17:24:32+00:00","og_image":[{"url":"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg","type":"","width":"","height":""}],"author":"Etienne Bley","twitter_misc":{"Written by":"Etienne Bley","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#article","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/"},"author":{"name":"Etienne Bley","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"headline":"VSTO to VBA Guideline to Compile and Test","datePublished":"2016-12-20T17:45:48+00:00","dateModified":"2022-02-03T17:24:32+00:00","mainEntityOfPage":{"@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/"},"wordCount":368,"image":{"@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#primaryimage"},"thumbnailUrl":"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg","articleSection":["General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/","url":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/","name":"VSTO to VBA Guideline to Compile and Test - ELB Solutions.com Inc.","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/#website"},"primaryImageOfPage":{"@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#primaryimage"},"image":{"@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#primaryimage"},"thumbnailUrl":"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg","datePublished":"2016-12-20T17:45:48+00:00","dateModified":"2022-02-03T17:24:32+00:00","author":{"@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"breadcrumb":{"@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#primaryimage","url":"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg","contentUrl":"http:\/\/elbsolutions.com\/projects\/wp-content\/uploads\/2016\/12\/Capture-300x124.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/elbsolutions.com\/projects\/vsto-vba-guideline-compile-test\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/elbsolutions.com\/projects\/"},{"@type":"ListItem","position":2,"name":"VSTO to VBA Guideline to Compile and Test"}]},{"@type":"WebSite","@id":"https:\/\/elbsolutions.com\/projects\/#website","url":"https:\/\/elbsolutions.com\/projects\/","name":"ELB Solutions.com Inc.","description":"Bringing all your IT Pieces together","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/elbsolutions.com\/projects\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39","name":"Etienne Bley","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","caption":"Etienne Bley"},"url":"https:\/\/elbsolutions.com\/projects\/author\/etienne-bley\/"}]}},"_links":{"self":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/1606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/comments?post=1606"}],"version-history":[{"count":16,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/1606\/revisions"}],"predecessor-version":[{"id":2691,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/1606\/revisions\/2691"}],"wp:attachment":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/media?parent=1606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/categories?post=1606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/tags?post=1606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}