CURDIR Function (VBA)

Standard

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:\"
Advertisements

STRCONV Function (VBA)

Standard

We all get encountered with such a problem when we need to convert a string into different formats like Upper Case, Lower Case, Proper Case, Unicode etc. Here we can use  STRCONV() function.

Lets see how we can use this.

The Syntax for STRCONV() is:
                                                                 StrConv(text,Conversion,LCID)
 text is the string that you want to convert.
Conversion is the type of format:

  1. vbUpperCase – All the string in upper case
  2. vbLowerCase – All the string in lower case
  3. vbProperCase – all the string in proper case
  4. vbUnicode – unicode value of the string
  5. vbFromUnicode – from unicode value of the string to the default code page of the system

LCID is optional. If this parameter is omitted, the STRCONV function assumes the system LocaleID. (For more information on Locale ID click here)

Example

StrConv(“theodoulus at useful gyaan”,vbLowerCase,1)   returns “theodoulus at useful gyaan”

StrConv(“theodoulus at useful gyaan”,vbUpperCase,1) returns “THEODOULUS AT USEFUL GYAAN”

StrConv(“theodoulus at useful gyaan”,vbUnicode,1) returns “t h e o d o u l u s   a t   u s e f u l   g y a a n ”

StrConv(“theodoulus at useful gyaan”,vbFromUnicode,1) returns “??????4??????”

StrConv(“theodoulus at useful gyaan”,vbProperCase,1) returns “Theodoulus At Useful Gyaan”

Excel VBA Functions

Standard

Function:

Function is a type of Procedure or Routine. In VBA, a Function is one which returns a Value or Object and a Procedure is which does the action but not return something.

Functions are of two types:

  • Pre- defined
  • User- defined

A Pre- defined function is a name of a set of instructions. You just need to use this function by its name. These functions are written in the libraries of the language so you don’t need to write the code for these functions.

A user- defined function is one which is user’s requirement specific. These type of functions need to be written by the user in the code window.

Pre- defined Functions in VBA:

In VBA, all pre- defined functions are categorized in 10 various categories

  1. String Functions
  2. Numeric/Mathematical Functions
  3. Logical Functions
  4. Information Functions
  5. Date & Time Functions
  6. Lookup / Reference Functions
  7. Data Type Conversion Functions
  8. File/Directory Functions

String Functions

STRCONV() CURDIR() CHAR()
FORMAT() LTRIM() RTRIM()
MID() STR() LCASE()
TRIM() LEN() ASC()
INSTR() SPACE() VAL()
REPLACE() LEFT() RIGHT()
UCASE() INSTRREV()

 

 

Numeric/ Mathematical Function

ABS() FROMAT() SGN()
ATN() INT() SIN()
COS() LOG() TAN()
EXP() RND() FIX()
ROUND()

 

Logical Functions

CASE
IF-THEN-ELSE

Information Functions

ISDATE() ISNULL()
ISNUMERIC() ISERROR()

Date and Time Functions

DATE() DAY(0 NOW()
DATEADD() FORMAT() TIMESERIAL()
DATEDIFF() HOUR() TIMEVALUE()
DATEPART() MINUTE() WEEKDAY()
DATESERIAL() MONTH() WEEKDAYNAME()
DATEVALUE() MONTHNAME() YEAR()

 

Lookup and Reference Functions

CHOOSE ()

Data Type Conversion Functions

CBOOL() CDBL() CSNG()
CBYTE() CDEC() CSTR()
CCUR() CINT() CVAR()
CDATE() CLNG()

File/ Directory Functions

CHDIR() DIR() GETATTR()
CHDRIVE() FILEDATETIME() MKDIR()
CURDIR() FILELEN() SETATTR()

If the argument is missing: VBA

Standard

If you have a VBA function written on your code and it has some parameters.
Suppose one of your parameter is optional and you want to check that while calling the function the user is providing the optional argument : Use IsMissing()

The function IsMissing returns a boolean (True/False) which says whether the values for the parameter is given or not.


Function Avg(num1, num2, Optional num3)
    Dim totalNums As Integer
    totalNums = 3
    If IsMissing(num3)  Then
            num3 = 0
            totalNums = totalNums - 1
    End If
    Avg = (num1 + num2 + num3) / totalNums
End Function
Sub avgSub()
           MsgBox Avg(2, 3)
           MsgBox Avg(2, 3, 5)
End Sub

Arrays in VBA

Standard

What is an Array :

Mostly we work with a single variable or element.  But sometimes we face a situation where we need a variable to hold more than one value of same data type e.g. all the names from a class. Here, we can declare an array of variables instead of using a variable for each item.

Definition :
An Array is the collection of similar type of data.

Working with Arrays:
Let’s start working with Arrays in VBA. Before using an Array, You need to declare it in the code like:
        Dim arrStrNames() as String

Here, we have declared the Array but we can’t assign values to it since we have not mentioned the size (upper and lower indexes) of this Array. To fix this, we will write another line:

ReDim arrStrNames(5)

Now this Array is ready to contain 6 elements. Shocked?

Yes, this Array will contain 6 elements despite the upper bound being 5 as the number you have mentioned in parenthesis is upper index (not the number of elements). And the default lower index is 0 for Arrays in all VBA programs. So, as we have given the value 5 for upper index but not for lower index, this Array will start from 0 to 5 means 6 elements.

You can also set the lower index in your code by using the following line of code as the first line: Option Base 1

Now, if you write
Redim arrStrNames(5) Now “arrStrNames” will contain 5 elements e.g. 1 t o5.

The second way of declaring an Array is:
Dim arrStrNames(5) as String
OR

Dim arrStrNames(1 to 5) as String

By the above line of code, you wouldn’t need to Redim the Array as you already have given the lower and upper indexes.

Apart from the indexes, there is one more difference while declaring an Array.
If you declare an Array like “Dim arrStr() as String”. This would be a dynamic Array. You can give the indexes anywhere in the code and change too.
But if you declare an Array as “Dim arrStrNames(1 to 5) as String”, then it would be a static Array. You can’t change the lower and upper indexes in your whole program.

Now, you are ready to play with the array. Let’s write the following lines of code into the VBA code window:

Option Explicit 
Option Base 1
Sub aa()
 Dim lngCounter                  As Long     
 Dim arrStrStudentNames()        As String     
 Dim arrLngStudentMarks()        As Long       

    ReDim arrLngStudentMarks(10)     
    ReDim arrStrStudentNames(10)         
    For lngCounter = 1 To 10         
       arrStrStudentNames(lngCounter) = Application.InputBox("Please Give the name", "Name", "Useful Gyaan")
       arrLngStudentMarks(lngCounter) = Application.InputBox("Please Give the marks", "Marks", 100)
    Next lngCounter         
    Range("A1").Resize(10, 1).Value = arrStrStudentNames     
    Range("A1").Offset(, 1).Resize(10, 1).Value = arrLngStudentMarks
End Sub 

And have fun.See you soon…………..