Author Archive

Large Operation Warning: Excel

Posted: September 12, 2013 by Theodoulus in Excel
Tags: ,

We generally see a “Warning Dialogue Box” like:

large operation warning

This is seen when we try to put a formula, value or formatting to a big range (many cells) at a time in Excel worksheet.
So the good part is, we can modify the number of cells for which the dialogue box gets popped-up. And how do we do this:

Simply, Open Excel File -> Goto Excel Options-> Goto Advanced Tab -> In Editing Options, we can find one option like highlighted below:

large operation warning Solution

 

 

We can set any number in the box given there (in Thousands).

Now, if we perform any calculation on the a bigger range than specified, the Warning will be popped-up.

Advertisements

Opening URL @ new tab in Internet Explorer: VBA

Posted: September 11, 2013 by Theodoulus in Excel, General Tips, VBA
Tags:

Problem Statement: Suppose, we have an array of URLs and we need to open them one by one. By looping over all the URLs on the same tab at internet explorer, we need to check the internet explorer is in LOADED or LOADING state. If the explorer is in loading state, it may give an error. And by default, the URL will be opened on the same tab.

So the process of opening URL on the same tab will be lengthy in terms of Coding, and processing time.

Solution:

We can use different tabs of the Internet Explorer so that we would not have to check the READYSTATE and to REFRESH the tab again and again. The example is given below:

Option Explicit

Sub OpenURLOnNewTab()

    Dim lngC As Long
    Dim strUrl  As String
    Dim ieObj   As InternetExplorer

    Set ieObj = New InternetExplorer
    ieObj.Visible = True
    strUrl = "https://usefulgyaan.wordpress.com/"       'We have taken one URL but you can take an array of URLs

    For lngC = 1 To 100
        ieObj.Navigate2 strUrl, 2048            '2048 is to open the URL on the new tab
    Next lngC

End Sub

Format() Function for Strings : VBA

Posted: July 2, 2013 by Theodoulus in Excel, VBA
Tags: , , , ,

Many times we encounter with such a situation when we have a “String” and from this string we need to fetch a value in fromat of  Date, Percentage, Currency, Fixed, Scientific, Yes/No, On/Off etc.

Here comes the most used function of VBA, The “Format() Function”.

Syntax:
Format(Expression,[Format])

Expression is the String which you pass in order to fetch the desired output. Format is the optional argument. If you don’t pass Format then you will get the same String which you passed as input.
Although, Excel has it’s own formats for the data but You can define your own [Format] too. Here is the list of the Defined Formats in the Excel 2007:

General Number     -This gives you the same string as you passed as input
Fixed              -Atleast one digit on the left and two digits after the decimal place
Currency           -With Thousand Separators along with 2 decimal places
Standard           -Thousand separator, atleast one digit at the left of decimal place and two digit at
                    the right the decimal place
Percent            -Displays the percent value with two digits at the right of decimal place
True/False         -True for zero and False for Non-Zero
On/Off             -OFF for Zero and On for non Zero
Yes/No             -Displays No for zero and Yes for non-zero

Examples:

  Format("1234","General Number")            'returns 1234
  Format("1234","currency")                  'returns $1,234.00
  Format("1234","Fixed")                     'returns 1234.00
  Format("1234","Standard")                  'returns 1,234.00
  Format("1234","Percent")                   'returns 123400.00%
  Format("1234","Yes/No")                    'returns   Yes
  Format("1234","General Date")              'returns 5/18/1903
  Format("1234","Long Date")                 'returns Monday, May 18, 1903             
  Format("1234","MMM, DDDD YYYY")            'returns May, Monday 1903

IsDate() Function : VBA

Posted: June 3, 2013 by Theodoulus in Excel, VBA
Tags: , ,

This function returns TRUE if argument’s data type is Date otherwise it returns FALSEHowever, there is an exception. In case the argument type is String the result depends on the contents of the string. If string is in a recognisable date (or time) format then the function returns TRUE.

One could say that the name of the function is misleading since it checks not just for date but time as well.

The Syntax for the function is:            IsDate(expression) 

Examples:

---------It accepts Date and Time in many formats------------------------ 
IsDate(#01/31/2013#)             'Returns True, as argument is of Date Type
IsDate(#jan-2013#)               'Returns True,as argument is of Date Type
Isdate("January,2013")           'Returns True, as String is in date format 
Isdate(10/15)                    'Return False, It treats it as a double value 
ISDate("10.15")                  'Returns True 
ISDate("10:15")                  'Returns True 
ISDate("10:15 AM")               'Returns True
ISDate("10:15:12")               'Returns True

Filter In Arrays

Posted: May 28, 2013 by Theodoulus in Excel, VBA
Tags: , ,

Problem Statement:

Suppose, You have a data-set like the following:

Now you want to take this data in an Array and then want to filter it on the basis of  column “Course”.

Solution:

We don’t have any predefined function that can filter an array so we have to code it. (more…)

Paste As Link

Posted: May 27, 2013 by Theodoulus in Excel
Tags: , , ,

Situation:
You have created a dashboard in which you have applied all the required formatting like border, color, cell merge, background, font- style, margins, row-column height or width etc. You have data in the dashboard and you have spent many hours in this whole creation.
What if you want to show the same dashboard in a different sheet too (means all the efforts you did in the previous sheet while creating that dashboard).
Solution: In this situation, Excel provides us “Paste Picture link” by which we can get all the desired output. You just need to copy the range of the dashboard that you created and paste it anywhere you want as “Paste Picture Link”. Now you are the GOD for the dashboard means if you are extending the row height, unmerge the merged cell, giving different background image and even your data is dynamic (getting changed automatically) or anything else you want in your Parent dashboard.
“THE ALL CHANGES WILL GET REFLECTED IN THAT PICTURE LINK WHERE YOU PASTED IT”.

Isn’t this a smart way of doing things!
So how to accomplish the task, let’s see:

First Create you Dashboard………………………..
a1
Now Select the range or the whole area of the dashboard and copy it.
Go to that place where you want this to be pasted and find the paste option on the HOME tab and Paste Picture Link

a2

Now whatever change would come in the original Dashboard will be reflected in the Picture Link which you have pasted.

See You Soon…………

LTRIM() Function VBA: To Remove Spaces

Posted: May 10, 2013 by Theodoulus in VBA
Tags: , ,

In VBA, LTRIM() function is used to remove the leading spaces from the given string.

Syntax:
The syntax for LTRIM() function is
LTRIM(text)

 text is the String that you wish to remove the leading spaces from.

Example:

Ltrim(“                  UsefulGyaan”)                      ‘ returns  “UsefulGyaan”
Ltrim(“                 UsefulGyaan          “)             ‘ returns  “UsefulGyaan          “
Ltrim(“UsefulGyaan          “)                             ‘ retruns “UsefulGyaan          “

Note: – LTRIM() function doesn’t remove the spaces between or after the string.

CHR() Function VBA

Posted: May 10, 2013 by Theodoulus in VBA
Tags: , ,

In VBA, we have CHR() function that returns a character based on the ASCII value.

Syntax:
The syntax for CHR() function is
CHR(Ascii_Value)
The parameter “Ascii_value” is used to retrieve the character.

How to use:

                                 Dim chrA   As String
                                 ChrA=CHR(87)

In the above example, chrA will hold a value “W” which is ASCII equivalent to 87.

Problem Statement:
To insert an IF() statement in a cell as formula, which includes double quotes.
Solution:
People fix this in two ways

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0,"""",Sheet1!A1)"

Or

Worksheets("Sheet1").Range("A1").Formula = "IF(Sheet1!A1=0," & CHR(34) & CHR(34) & ",Sheet1!A1)" 

In the first way, we may get confused with the number of double quotes to put in whereas in the second example there is no chance of confusion. So, the second one is the nice choice.

See you soon!

CurDir() function VBA

Posted: May 8, 2013 by Theodoulus in VBA
Tags: ,

Many times you want to know the complete path where you are currently working in.
So, CurDir() is the solution.

Now, the question is, “How to work with Curdir() in VBA”.

Ok, let me tell you the The syntax for the CURDIR function is:
                                                                                                     CurDir( drive )

drive is an optional parameter. If you provide this parameter, this function will give you the path which you provide but if you omit this parameter or pass an empty string, this will return the complete path where your current workbook is placed in.

For Example:

curdir("")                  ' will return "C:\Users\user\Documents"
CurDir()                    ' will return "C:\Users\user\Documents"
CurDir("E")                 ' will return "E:\"

STRCONV Function (VBA)

Posted: May 7, 2013 by Theodoulus in VBA
Tags: , , ,

We all encounter situations when we need to convert a string to different formats like Upper Case, Lower Case, Proper Case, Unicode etc. Here we can use STRCONV() function.

The Syntax for STRCONV() is:               STRCONV(text, Conversion, LCID)  

Parameters Description –
     Text : String that you want to convert.
     Conversion : Format type

  1. vbUpperCase : To convert a string to Upper Case
  2. vbLowerCase : To convert a string to Lower case
  3. vbProperCase : To convert a string to Proper Case
  4. vbUnicode : To convert a string to Unicode
  5. vbFromUnicode: To convert a string from unicode value of the string to the default code page of the system

(more…)