{"id":1148,"date":"2013-12-09T08:40:24","date_gmt":"2013-12-09T08:40:24","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=1148"},"modified":"2020-04-24T06:16:26","modified_gmt":"2020-04-24T06:16:26","slug":"excel-vba-2010-lesson-7-mathematical-functions","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/","title":{"rendered":"Excel 2010 VBA Lesson 7: Mathematical Functions"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-6-subroutines-and-functions\/\">[Lesson 6]<\/a>&lt;&lt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-tutorial\/\">[Table of Contents]<\/a>&gt;&gt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-8-formatting-functions\/\">[Lesson 8]<\/a><\/strong><\/h4>\n\n\n\n<p>Mathematical functions are very useful and important in Excel 2010 VBA programming because very often we need to deal with mathematical concepts in programming such as chance and probability, variables, mathematical logic, calculations, coordinates, time intervals and etc.<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p>The common mathematical functions in Excel 2010 VBA are&nbsp;<strong>Int, Sqr, Abs, <g class=\"gr_ gr_8 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace\" id=\"8\" data-gr-id=\"8\">Exp<\/g>, Log, Sin, Cos, <g class=\"gr_ gr_13 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace\" id=\"13\" data-gr-id=\"13\">Tan ,<\/g> Atn, <g class=\"gr_ gr_14 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace\" id=\"14\" data-gr-id=\"14\">Fix<\/g><\/strong><g class=\"gr_ gr_14 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Style multiReplace\" id=\"14\" data-gr-id=\"14\">&nbsp;,<\/g> <strong>Rnd<\/strong> <g class=\"gr_ gr_11 gr-alert gr_gramm gr_inline_cards gr_disable_anim_appear Punctuation only-ins replaceWithoutSep\" id=\"11\" data-gr-id=\"11\">and<\/g>&nbsp;<strong>Round<\/strong>)<br><strong>Rnd<\/strong>&nbsp;is very useful when we deal with the concept of chance and probability. The Rnd function returns a random value between 0 and 1. In Example 7.1. When you run the program, you will get an output of 10 random numbers between 0 and 1 each time you click on the OK button of the message box.<\/p>\n\n\n\n<p><strong>Example 7.1<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\n For x = 1 To 10\n  MsgBox Rnd\n Next x\nEnd Sub\n<\/pre>\n\n\n\n<p>Random numbers in its original form are not very useful in programming until we convert them to integers. For example, if we need to obtain a random output of 6 random integers ranging from 1 to 6, which make the program behave as a virtual die, we need to convert the random numbers using the format&nbsp;&nbsp;<strong>Int(Rnd*6)+1<\/strong>. Let\u2019s study the following example:<\/p>\n\n\n\n<p>In this example, Int(Rnd*6) will generate a random integer between 0 and 5 because the function&nbsp;<strong>Int<\/strong>&nbsp;truncates the decimal part of the random number and returns an integer. After adding 1, you will get a random number between 1 and 6 every time you click the command button. For example, let say the random number generated is 0.98, after multiplying it by 6, it becomes 5.88, and using the integer function Int(5.88) will convert the number to 5, and after adding 1 you will get 6.<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Example 7.2<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub Commandbutton1_Click ( )\n Num=Int(Rnd*6)+1\n MsgBox Num\nEnd Sub\n<\/pre>\n\n\n\n<p>Now, run the program and then click on the button, you will get an output of number 1 to 6 randomly display on the message box.<\/p>\n\n\n\n<p>b) Int is the function that converts a number into an integer by truncating its decimal part and the resulting integer is the largest integer that is smaller than the number. For example, Int(2.4)=2, Int(4.8)=4, Int(-4.6)= -5, Int(0.032)=0 and so on.<br>c) Sqr is the function that computes the square root of a number. For example, Sqr(4)=2, Sqr(9)=2 and etc.<br>d) Abs is the function that returns the absolute value of a number. So Abs(-8) = 8 and Abs(8)= 8.<br>e) Exp of a number x is the value of e<sup>x<\/sup>. For example, Exp(1)=e^1 = 2.7182818284590<br>f) Fix and Int is the same if the number is a positive number as both truncate the decimal part of the number and return an integer. However, when the number is negative, it will return the smallest integer that is larger than the number. For example, Fix(-6.34)= -6 while Int(-6.34)=-7.<br>g) Round is the function that rounds up a number to a certain number of decimal places. The Format is Round (n, m) which means to round a number n to m decimal places. For example, Round (7.2567, 2) =7.26<br>g) Log is the function that returns the natural Logarithm of a number. For example,<br>Log (10)= 2.302585<\/p>\n\n\n\n<p><strong>Example 7.3<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\n For i = 1 To 10\n  MsgBox Sqr(i )\n Next\nEnd Sub\n<\/pre>\n\n\n\n<p>This program will find the square root of number 1 to 10 and displays them on the message box each time you click on the OK button.<\/p>\n\n\n\n<p><strong>Example 7.4<\/strong><\/p>\n\n\n\n<p>In this example, we created an Excel 2010 VBA to compute the values of Int(x), Fix(x), Round(x,4) and Log(x) and displays them in respective cells. Notice that you can combine two or more functions in your code, like Round(Log(x)). It uses the Do Loop statement and the Rnd function to generate random numbers. The statement x = Rnd * 7 generate &nbsp;random numbers between 0 and 7 . Using commas in between items will create spaces between them and hence a table of values can be created. The program is shown below and the output is shown in Figure 7.1:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub CommandButton1_Click()\nDim n As Integer\nDim x As Single\n n = 2\nDo While n &lt; 12\n x = Rnd * 7\n\n Cells(n, 1) = x\n Cells(n, 2) = Int(x)\n Cells(n, 3) = Fix(x)\n Cells(n, 4) = Round(Cells(n, 1), 4)\n Cells(n, 5) = Round(Log(x), 4)\n Cells(n, 6) = Round(Sqr(x), 4)\n n = n + 1\nLoop\nEnd Sub\n<\/pre>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><a href=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg\"><img loading=\"lazy\" decoding=\"async\" width=\"669\" height=\"516\" src=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg\" alt=\"vba2010_fig7.1\" class=\"wp-image-1158\"\/><\/a><\/figure><\/div>\n\n\n\n<h4 class=\"wp-block-heading\">Figure 7.1<\/h4>\n\n\n\n<p><br><br>&lt;br \/&gt;\n     (adsbygoogle = window.adsbygoogle || []).push({});&lt;br \/&gt;\n<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-6-subroutines-and-functions\/\">[Lesson 6]<\/a>&lt;&lt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-tutorial\/\">[Table of Contents]<\/a>&gt;&gt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-8-formatting-functions\/\">[Lesson 8]<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>[Lesson 6]&lt;&lt;[Table of Contents]&gt;&gt;[Lesson 8] Mathematical functions are very useful and important in Excel 2010 VBA programming because very often we need to deal with mathematical concepts in programming such as chance and probability, variables, mathematical logic, calculations, coordinates, time intervals and etc. The common mathematical functions in Excel 2010 VBA are&nbsp;Int, Sqr, Abs, Exp, &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel 2010 VBA Lesson 7: Mathematical Functions&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[14],"tags":[38,44,42,39,45,36,40,46,47,41,37,43],"class_list":["post-1148","page","type-page","status-publish","hentry","category-function","tag-abs","tag-atn","tag-cos","tag-exp","tag-fix","tag-int","tag-log","tag-rnd","tag-round","tag-sin","tag-sqr","tag-tan"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel 2010 VBA Lesson 7: Mathematical Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"description\" content=\"This lesson article illustrates the use of mathematical functions in Excel VBA 2010 macro programming\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel 2010 VBA Lesson 7: Mathematical Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"This lesson article illustrates the use of mathematical functions in Excel VBA 2010 macro programming\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-24T06:16:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"669\" \/>\n\t<meta property=\"og:image:height\" content=\"516\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/\",\"url\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/\",\"name\":\"Excel 2010 VBA Lesson 7: Mathematical Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg\",\"datePublished\":\"2013-12-09T08:40:24+00:00\",\"dateModified\":\"2020-04-24T06:16:26+00:00\",\"description\":\"This lesson article illustrates the use of mathematical functions in Excel VBA 2010 macro programming\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#primaryimage\",\"url\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg\",\"contentUrl\":\"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg\",\"width\":669,\"height\":516},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel 2010 VBA Lesson 7: Mathematical Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel 2010 VBA Lesson 7: Mathematical Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"This lesson article illustrates the use of mathematical functions in Excel VBA 2010 macro programming","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:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/","og_locale":"en_US","og_type":"article","og_title":"Excel 2010 VBA Lesson 7: Mathematical Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"This lesson article illustrates the use of mathematical functions in Excel VBA 2010 macro programming","og_url":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-24T06:16:26+00:00","og_image":[{"width":669,"height":516,"url":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg","type":"image\/jpeg"}],"twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/","url":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/","name":"Excel 2010 VBA Lesson 7: Mathematical Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#primaryimage"},"image":{"@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg","datePublished":"2013-12-09T08:40:24+00:00","dateModified":"2020-04-24T06:16:26+00:00","description":"This lesson article illustrates the use of mathematical functions in Excel VBA 2010 macro programming","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#primaryimage","url":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg","contentUrl":"https:\/\/excelvbatutor.com\/wp-content\/uploads\/2013\/12\/vba2010_fig7.1.jpg","width":669,"height":516},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-7-mathematical-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel 2010 VBA Lesson 7: Mathematical Functions"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1148","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=1148"}],"version-history":[{"count":50,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1148\/revisions"}],"predecessor-version":[{"id":3478,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1148\/revisions\/3478"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=1148"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=1148"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=1148"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}