{"id":13749,"date":"2018-04-22T16:30:24","date_gmt":"2018-04-22T16:30:24","guid":{"rendered":"http:\/\/learnexcelmacro.com\/wp\/?p=13749"},"modified":"2022-08-17T19:17:34","modified_gmt":"2022-08-17T19:17:34","slug":"5-most-unused-tricks-in-excel-vba","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2018\/04\/5-most-unused-tricks-in-excel-vba\/","title":{"rendered":"5 rarely used features in Excel VBA"},"content":{"rendered":"
[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”4.16″ da_disable_devices=”off|off|off” global_colors_info=”{}” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on”][et_pb_row admin_label=”row” _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||”][et_pb_text _builder_version=”4.17.6″ _module_preset=”default” global_colors_info=”{}”]Dear friends,<\/p>\n
As promised last week, in this article, I am going to talk about 5 rarely used features of Excel VBA. [I am not saying that all of these features are awesome to use… I have provided my personal choices for each of them]<\/p>\n
IIF is a type of IF ELSE END IF. Of course, it is not exactly the same as a normal if-else statement.<\/p>\n
Syntax of IIF Statement<\/em><\/p>\n As you can see this, it pretty much looks like If, do this, else do this<\/em>. But this is not the same as the If else statement.<\/p>\n 2nd difference mentioned above is one of the main differences between the If Else and IIF statements. This is also one of the main reasons – I do not prefer<\/em> to use this more often.<\/p>\n<\/div>\n Above explanation will be more clear with the following example.<\/p>\n Note: <\/em>Above program will throw a run time error divide by zero error<\/em>. As mentioned above, even though condition x is not equal to zero – true and code should execute div = 0, it will also evaluate div = y\/x expression as well and therefore this error.<\/p>\n Same logic is written using If Else in the below code and it will run successfully without any error.<\/p>\n If you can type your IF and\/or Else statement(s) in single line as shown below then you do not need to type End If.<\/p>\n Remarks:<\/em> Personally I prefer this only when If any of the above criteria is not met, then I prefer using the If and End If – Complete set. This is my personal preference<\/>. This is also because it makes the code more readable else it will be difficult to read the If Else part. <\/em><\/p>\n As you know in VBA new line itself is considered the end of the statement. Therefore, if you want to type multiple statements in a single line then you can use a colon as the end of a statement in VBA.<\/p>\n Note:<\/em> Personally I do not like this at all. I prefer each statement written in a new line. Using multiple statements in a single line using a colon reduces the code readability drastically.<\/p>\n This is one of my favorite features of Excel VBA. <\/em> To know more about this, I have written a detailed article about this. Read this article to know more about: Using Mid function, you can replace text in a string.<\/p>\n The above code will result in the following message box with Text – “Excel is magical”<\/p>\n Integer Length specified in the Mid function should be the same as the length of the string which you want to replace it with. For example, in the above code, I have used 2 <\/em>in mid function and length of string ma<\/em> is also 2.<\/p>\n In case these two are not equal, the expression will not throw any error. It will simply take the lowest of both and does its job.<\/p>\n Example 1:<\/em> Example 1:<\/em> [content_boxes layout=”icon-on-side” columns=”1″ backgroundcolor=”#F4EB94″ ][content_box title=”Important Info” icon=”” backgroundcolor=””]This method can be used effectively when you want to replace some fixed number of characters located at some specific location in a given string irrespective of what those strings are. So this is a useful feature that can be used effectively in a particular situation.[\/content_box][\/content_boxes][\/et_pb_text][et_pb_blurb title=”You may like reading them too” use_icon=”on” font_icon=”||fa||900″ _builder_version=”4.17.6″ _module_preset=”0249c68e-4de8-4f44-84ff-a9b1850785b6″ hover_enabled=”0″ global_colors_info=”{}” content__hover_enabled=”off|desktop” sticky_enabled=”0″]<\/p>\n [\/et_pb_blurb][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":" Dear friends, As promised last week, in this article, I am going to talk about 5 rarely used features of Excel VBA. [I am not saying that all of these features are awesome to use… I have provided my personal choices for each of them] 1# Using IIF in Excel vba IIF is a type […]<\/p>\n","protected":false},"author":45,"featured_media":242631,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1679],"tags":[],"class_list":["post-13749","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-macro","category-excel-macro-beginner"],"yoast_head":"\nIIF(Expression to check, value if true, value if false )<\/code><\/pre>\n
Difference between If-Else and IIF<\/h2>\n
\n
Note:<\/h3>\n
\nSub IIF_Demo()\n\nDim x As Integer\nDim y As Integer\nDim div As Integer\nx = 0\ny = 2\nIIf x <> 0, div = y \/ x, div = 0\nDebug.Print div\nEnd Sub\n\n\n<\/code><\/pre>\n
\nSub IFELSE_Demo()\nDim x As Integer\nDim y As Integer\nDim div As Integer\nx = 0\ny = 2\nIf (x <> 0) Then\n div = y \/ x\nElse\n div = 0\nEnd If\nDebug.Print \"If Else executed and div value is calculated... div = \"; div\nEnd Sub\n<\/code><\/pre>\n
2# End If is not always mandatory for If statement<\/h1>\n
\n Sub IfWithoutElseDemo()\n Dim a As Integer\n Dim b As Integer\n a = 1\n b = 2\n If (a > b) Then MsgBox a Else MsgBox b\nEnd Sub\n<\/code><\/pre>\n
\n1. There is no Else part in If Statement
\n2. Single statement is executed as part of If<\/p>\n3# Colon in VBA as – End of Statement<\/h1>\n
\nSub ColonAsEndOfStatement()\n Dim a As Integer\n Dim b As Integer\n a = 1\n b = 2\n If (a > b) Then MsgBox \"Line 1\": MsgBox \"Line 2\": MsgBox \"Line 3\" Else MsgBox \"Else part 1\": MsgBox \"Else part 2\"\nEnd Sub\n<\/code><\/pre>\n
4# Using immediate window and Debug statement<\/h1>\n
\nWhat is immediate window? <\/a>
\nWhat is Debug command and how to use it? <\/a><\/p>\n5# Replace text in a String using mid function<\/h1>\n
\nSub replaceTextUsingMid()\n Dim inputStr As String\n inputStr = \"Excel is logical\"\n Mid(inputStr, 10, 2) = \"ma\"\n MsgBox inputStr\nEnd Sub\n<\/code><\/pre>\n
Result:<\/h3>\n
Note:<\/h3>\n
\nFollowing will result – Excel is magical<\/em><\/p>\n\n inputStr = \"Excel is logical\"\n Mid(inputStr, 10, 3) = \"ma\"\n<\/code><\/pre>\n
\nFollowing will result – Excel is magical<\/em><\/p>\n\n inputStr = \"Excel is logical\"\n Mid(inputStr, 10, 2) = \"man\"\n<\/code><\/pre>\n
\nIn this case VBA.Replace() <\/em>is not useful because to use this Replace() function – you need to know what exactly the string is at a given location in a string to replace.<\/p>\n\n