{"id":32898,"date":"2023-02-02T22:23:53","date_gmt":"2023-02-02T16:53:53","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/"},"modified":"2023-02-02T22:23:53","modified_gmt":"2023-02-02T16:53:53","slug":"solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/","title":{"rendered":"[Solved] Why is VBA throwing type mismatch errors when passing a Range object?"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-37865255\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"37865255\" data-parentid=\"37844961\" data-score=\"1\" data-position-on-page=\"2\" data-highest-scored=\"0\" data-question-has-accepted-highest-score=\"0\" itemprop=\"suggestedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code (notice the explicit reference to the Excel lib when defining Foo and the Range variable r): <\/p>\n<pre><code>Function Foo(r As Excel.Range) As String\n    MsgBox \"WHAT. The function worked? Wow!\"\n    Foo = \"Test\"\nEnd Function\n\nSub main()\n    Set workbook = Excel.Workbooks.Open(\"example.xlsx\")\n    Set datasheet = workbook.ActiveSheet\n\n    Dim rng As Excel.Range\n    Set rng = datasheet.Range(\"A2:A100\")\n    Dim t As String\n    t = Foo(rng)\n    Debug.Print t\nEnd Sub\n<\/code><\/pre>\n<p>As @ScottCraner and others pointed out the issue could easily have been that I had a variable\/function called Range defined somewhere else. In a way that is sort of what happened, only it was defined in the Word Library, not in my code.<\/p>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">1<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved Why is VBA throwing type mismatch errors when passing a Range object? <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code &#8230; <a title=\"[Solved] Why is VBA throwing type mismatch errors when passing a Range object?\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\" aria-label=\"More on [Solved] Why is VBA throwing type mismatch errors when passing a Range object?\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[352,400,5942,401],"class_list":["post-32898","post","type-post","status-publish","format-standard","hentry","category-solved","tag-net","tag-excel","tag-type-mismatch","tag-vba"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] Why is VBA throwing type mismatch errors when passing a Range object? - JassWeb<\/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:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] Why is VBA throwing type mismatch errors when passing a Range object? - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2023-02-02T16:53:53+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] Why is VBA throwing type mismatch errors when passing a Range object?\",\"datePublished\":\"2023-02-02T16:53:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\"},\"wordCount\":142,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\".net\",\"excel\",\"type-mismatch\",\"vba\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\",\"name\":\"[Solved] Why is VBA throwing type mismatch errors when passing a Range object? - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2023-02-02T16:53:53+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] Why is VBA throwing type mismatch errors when passing a Range object?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] Why is VBA throwing type mismatch errors when passing a Range object? - JassWeb","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:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] Why is VBA throwing type mismatch errors when passing a Range object? - JassWeb","og_description":"[ad_1] So the problem turned out to be that because this code was run from word, with the Excel library as a reference, the Range type defaulted to a MS-Word Range object instead of a MS-Excel Range object, meaning that my function was looking for the wrong type of parameter. Here is the corrected code ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/","og_site_name":"JassWeb","article_published_time":"2023-02-02T16:53:53+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] Why is VBA throwing type mismatch errors when passing a Range object?","datePublished":"2023-02-02T16:53:53+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/"},"wordCount":142,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":[".net","excel","type-mismatch","vba"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/","url":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/","name":"[Solved] Why is VBA throwing type mismatch errors when passing a Range object? - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2023-02-02T16:53:53+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-why-is-vba-throwing-type-mismatch-errors-when-passing-a-range-object\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] Why is VBA throwing type mismatch errors when passing a Range object?"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/32898","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=32898"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/32898\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=32898"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=32898"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=32898"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}