The post Distance Calculator in Excel VBA [REVISED] appeared first on Welcome to LearnExcelMacro.com.

]]>Hi guys, on June 2016, google made some changes around pricing and authentication etc. of its FREE APIs. Distance calculator API was also affected by those changes.

I had written an article long back about how to calculate distance and time taken between two places in Excel VBA using google matrix api. In addition to this article, I had also built a small Excel tool which calculates distance and time taken between two places you type it in. It also has an option to choose your mode of transport. It looks something like below:

Ever since the changes taken place, I am getting many questions from LEM readers that this tool does not work for them anymore.

Therefore, I thought to write this article to explain you more in detail.

In the previous article, i have simply shared a simple VBA code to call Distance matrix API *– where no authentication (API Key) was required…*

After that changes, the tool I had created and article which I had published were not working. Although, I had replied via comments many time, but I keep getting this question again and again. Therefore, I decided to write this article to answer all the questions at once.

Now, In order to invoke Distance matrix API from google, first you need to get an API key using your google account. For that matter, in order to access any of its API, you need to get an API key to authenticate your API calls.

You can follow the simple steps given here in this article from google – https://developers.google.com/maps/documentation/distance-matrix/get-api-key . Before, you try to call the API from Excel VBA, get this API key for yourself.

As I mentioned previously, it is no longer for free (100%) , you will have some limitations by using your FREE account. more details can be found here

As you have learnt in the previous article how to create different parameters before your make a API call, that remains exactly same except the fact that now you need to append *&key=YOUR_API_KEY*

**For example: **Here is how a sample URL looks like using API key

https://maps.googleapis.com/maps/api/distancematrix/xml?origins=Amsterdam&destinations=Utrecht&mode=driving&key=YOUR_API_KEY

**There Two things: **

In the code, I have added an exception handling, so that you know what is wrong with the API call. It was my mistake that in my previous article, I did not use any exception handling, therefore it was not clear what exactly the problem is.

Secondly, I have added a placeholder for your API key which you can store it in the config sheet and tool is ready for your own use.

Here is the VBA code to call Google Distance Matrix API and get distance and time taken :

```
Function getDistanceAndTimeBetweenTwoPlaces() As Variant
' This function will return an array holding
' distance in meters and duration in seconds
Dim googleAPIRequest As XMLHTTP60
Dim domDoc As DOMDocument60
Dim xmlNodesList As IXMLDOMNodeList
Dim status As String
Dim errorMessage As String
Dim response(1) As Variant 'array to hold distance & duration
On Error GoTo err
'API URL is formed in excel sheet config using exccel formula
urlForDistance = Range("urlForDistance").Value
Set googleAPIRequest = New XMLHTTP60
' invoke the API to get the Distance Matrxi in XML format
googleAPIRequest.Open "GET", urlForDistance, False
googleAPIRequest.Send
' Get the response XML
Set domDoc = New DOMDocument60
domDoc.LoadXML googleAPIRequest.ResponseText
'using xPath first get the status of the API Call
status = domDoc.SelectSingleNode("//status[1]").nodeTypedValue
errorMessage = domDoc.Text
If status = "OK" Then
' Using xPath get the distance
Set xmlNodesList = domDoc.SelectNodes("//distance[1]/*")
response(0) = xmlNodesList(0).Text
' Using xPath get the duration
Set xmlNodesList = domDoc.SelectNodes("//duration[1]/*")
response(1) = xmlNodesList(0).Text
' Return response with distance and duration in array
getDistanceAndTimeBetweenTwoPlaces = response
' release memory
Set xmlNodesList = Nothing
Else
MsgBox errorMessage
End If
err:
Set domDoc = Nothing
Set googleAPIRequest = Nothing
End Function
```

Before you run this excel sheet, you need to get your API key generated from your google account and put that key in the excel sheet at specified place and then enjoy using it.

The post Distance Calculator in Excel VBA [REVISED] appeared first on Welcome to LearnExcelMacro.com.

]]>The post Support !! appeared first on Welcome to LearnExcelMacro.com.

]]>Dear Readers,

**Its been long since I posted any new article or any useful Excel Tools on the website.**

As you are aware of that all the tools which I publish here are always available to **download for FREE.**

I will continue to do so in future. You can also help me in doing this.

If any one of my article/Tools/Tips etc. has helped you in anyway, you can help me by donating here.

I have added PayPal donate button on the pages of the Blog as well. You can click here and donate whatever amount you would like to

Thanks in advance !!

The post Support !! appeared first on Welcome to LearnExcelMacro.com.

]]>The post Calculate MOD of Large Numbers in Excel appeared first on Welcome to LearnExcelMacro.com.

]]> This article is all about MOD function in Excel. Every aspect of MOD function is covered here in this article. At the end of this article, I have also explained a VBA code to calculate MOD of large numbers.

I have also explained shortcomings of MOD formula in Excel and how to overcome those shortcomings.

Lets start with the basics – What is MOD function?

MOD is short form of Modulus. Modulus is a function which returns the remainder when you divide a number with another number.**For Example:** 19 MOD 5 = 4 ( Explanation : If you divide 19 with 5 then remainder will be 4 (5*3 = 15 and 19-15 = 4)

Using simple Excel Formula, you can easily calculate MOD of any 2 numbers.

As shown in the above picture, you can see there are two input parameters required for this Function. *Number and Divisor.*

Using above formula, you can calculate MOD of maximum of 12 digits. *If number goes beyond 12 digits, then you get #NUM! error as shown below:*

Yesterday, In office, one of my colleague (Bas Vermeulen) was trying to calculate the IBAN Numbers for a Given BANK Name, Country Code and Bank Account Number. In the process of calculating IBAN Number, he needed to calculate ** MOD-97 **of really bog number (approx 22 digit number).

Therefore, to help him, I created a VBA function which can calculate MOD of any number of digits

To overcome the MOD function error, I came up with following logic, so that no matter how big the number is, MOD can still be calculated. Therefore, I went back to the basics of Division, which I had studied in school days – How to divide a big number.

So here if what you do..

**Step 1 : **First pick up first n length of digit from the Large Number provided (Where n = Length of Divisor + 1) – Just to make sure that big number is divided with small number.**Step 2 : **Now for this subset of digit, it is easy to calculate the MOD and get the Remainder**Step 3 : **Now, simply append this remainder in the remaining digit of the larger number**Step 4 : **Now using this as a new Number repeat the steps from Step 1 again…

Refer the image below for more clarity

With the above process, it is clear that, if we automate the above step in VBA then, there you can calculate MOD of any big number. Because at a time, you are dividing a subset of the whole number. Number of iteration will increase based on how big the number is – but it will be able to calculate.

```
Function MODForBigNum(bigNum As String, modVal As Integer) As Integer
Dim currentLenNum As Integer
Dim partitionCount As Integer
Dim remainingString As String
currentLenNum = VBA.Len(bigNum)
partitionCount = VBA.Len(modVal) + 1
remainingString = bigNum
While currentLenNum > partitionCount
stringToInt = VBA.Left(remainingString, partitionCount)
remainingString = VBA.Right(remainingString, currentLenNum - partitionCount)
remainder = stringToInt - (Int(stringToInt / modVal) * modVal)
remainingString = remainder & remainingString
currentLenNum = VBA.Len(remainingString)
Wend
MODForBigNum = remainingString - (Int(remainingString / modVal) * modVal)
End Function
```

**There is one shortcoming in the above code – in case when Divisor is more than 5 digits – it will give an error**

To overcome the above problem, I did some changes to the above code and now it should be *working any number of Number and Divisor*

The only, issue with the below function is that, it accepts Divisor as a string and also returns result as a string.

```
Function MODForBigNum2(bigNum As String, modVal As String) As String
Dim currentLenNum As Integer
Dim partitionCount As Integer
Dim remainingString As String
currentLenNum = VBA.Len(bigNum)
partitionCount = VBA.Len(modVal) + 1
remainingString = bigNum
While currentLenNum > partitionCount
stringToInt = VBA.Left(remainingString, partitionCount)
remainingString = VBA.Right(remainingString, currentLenNum - partitionCount)
remainder = stringToInt - (Int(stringToInt / modVal) * modVal)
remainingString = remainder & remainingString
currentLenNum = VBA.Len(remainingString)
Wend
MODForBigNum2 = remainingString - (Int(remainingString / modVal) * modVal)
End Function
```

So, now you have a solution to calculate Mod for a big Number and Big Divisor.

In my next article, I will Publish a Tool to calculate IBAN Numbers automatically.

The post Calculate MOD of Large Numbers in Excel appeared first on Welcome to LearnExcelMacro.com.

]]>The post UDF to Convert Numbers to Letters appeared first on Welcome to LearnExcelMacro.com.

]]>First of all, wouldn’t it be great if we have a built in function in Microsoft Excel to Convert **Numbers to Letters. ** It means, simply, by using a formula in excel, I could Spell Numbers in Words.

In banking or accountancy, this is used to write grand total amounts. Amounts are mentioned in Numbers as well as words. You probably have seen on invoices or on Bank Cheques.

We can write above number in words in many ways in different languages, countries and Number scales etc. Here are the two, for which, I have adjusted the function.

As we do not have any built in function as such in Microsoft Excel yet – therefore, I have created this User Defined function (UDF) to convert Numbers to Letters. *You can also download a FREE Excel Workbook with this UDF in it. *

Though there was a VBA code available Microsoft office wiki about spelling currency in words. I, too had written an article previously about converting Indian currency in words.

For example: if you want the maximum scale unit to be used to spell your amount is *Billion* (like Thousand, Million and Billion) and you provide an amount which is (more than 999 Billion) 1000 Billion or more. Then in this case, these functions were not capable of dealing that and returns strange result.

**To overcome above limitation,** I have created a function in VBA using **recursive method** to convert **Numbers to letters**. That means in the above example, this function will be able to give you correct spelling for 1000 Billion or even more.**For Example: ***One Thousand Billion or Ten Thousand Billion or Hundred Thousand Billion or One Million Billion etc.*

This is possible because of using**recursive function **. In simple language –* if a function call itself from its own body of the function is called recursive function and this method is called recursion.*

Since this is User Defined Function to **convert numbers to words ** or in other words, **spell numbers in Words. ** * – You can use it as a formula in any cell in your excel workbook. *

If you do not know what is UDF – User Defined Function – Refer this article.

To make it re-usable and simplify my previous code to convert numbers to letters, I have made a logical split of the whole code. I have made it in more modular way which will be lot easier to understand and change according to your need.

1. Below is the VBA function [recursive function] to spell numbers to letters. It does not care about the currency Unit or sub-Unit etc. This is simply used to **convert your number in words**. Benefit of creating this as a separate function is that you can use this function to convert any types of Numbers to Letters. It is not necessary that, you should use this for converting a currency amount to words only.**Most importantly, ** this function accepts whole number as an input. It does not accept decimal numbers. Therefore, if you want to convert a decimal number in words, you can split whole and decimal number and call this function separately. This is what I have done in *SpellCurrency* functions below. You can refer them below.

```
Function NumToLettersX(ByVal num As String, _
Optional NumLetters As String = "", _
Optional UnitCounter As Integer = 0) As String
' Add the unit until where you want to use
' in Number Scale - Like
' Arab, Kharab, etc. [in Indian Continent Number Scale System]
Dim Unit As Variant
Unit = Array( _
"", _
" Thousand ", _
" Lakhs ", _
" Crore ")
Dim iCount As Integer: iCount = 1
Do While num ""
' Check if you have reached at the last Unit
' defined in the array - Unit
' if yes, call this same function by providing
' the remaining digits
If (iCount = UBound(Unit) + 1 And Len(num) > 2) Then
NumLetters = Unit(UnitCounter) & NumLetters
NumToLettersX num, NumLetters
Exit Do
End If
If (iCount = 1) Then
Temp = GetHundreds(Right(num, 3))
Else
Temp = GetHundreds(Right(num, 2))
End If
If (Temp "Zero") Then NumLetters = Temp & Unit(UnitCounter) & NumLetters
If Len(num) > 2 And iCount = 1 Then
num = Left(num, Len(num) - 3)
ElseIf Len(num) >= 2 And iCount > 1 Then
num = Left(num, Len(num) - 2)
Else
num = ""
End If
iCount = iCount + 1
UnitCounter = UnitCounter + 1
Loop
NumToLettersX = Application.WorksheetFunction.Trim(NumLetters)
End Function
```

Important to note that above code is for Indian continent Number Scale System Only. You can customize it according to your need.

**Example: In Indian Continent Number Scale System : 6734862 : ** Sixty Seven Lakhs Thirty Four Thousand Eight Hundred and Sixty Two.

I have customized the above function for **American Number Scale system **. Following is the code for the American Number Scale System:

```
Function NumToLettersY(ByVal num As String, _
Optional NumLetters As String = "", _
Optional UnitCounter As Integer = 0) As String
' Add the unit until where you want to use
' in Number Scale - Like
' Million, Billion etc. [in American Number Scale System]
Dim Unit As Variant
Unit = Array( _
"", _
" Thousand ", _
" Million ", _
" Billion ", _
" Trillion ")
Dim iCount As Integer: iCount = 1
Do While num ""
' Check if you have reached at the last Unit
' defined in the array - Unit
' if yes, call this same function by providing
' the remaining digits
If (iCount = UBound(Unit) + 1 And Len(num) > 3) Then
NumLetters = Unit(UnitCounter) & NumLetters
NumToLettersY num, NumLetters
Exit Do
End If
Temp = GetHundreds(Right(num, 3))
If (Temp "Zero") Then NumLetters = Temp & Unit(UnitCounter) & NumLetters
If Len(num) > 3 Then
num = Left(num, Len(num) - 3)
Else
num = ""
End If
iCount = iCount + 1
UnitCounter = UnitCounter + 1
Loop
NumToLettersY = Application.WorksheetFunction.Trim(NumLetters)
End Function
```

**Example: In American Number Scale System : 6734862 : ** Six Million Seven Hundred Thirty Four Thousand Eight Hundred and Sixty Two.

For above function to work correctly, DO NOT FORGET to copy the generic functions below. Spell Numbers to Letters functions uses these generic functions.

Basically, this function does the following:

**1.** Round-Up the input number provided to Spell the currency up to two digit.**2.** Separate the whole number part and decimal part from the input [**Note:** This is because above function accepts only whole number as input]**3.** Call the Spell Number function for American Number Scale System to spell the numbers separately – 1 for Whole number and one for Decimal part(rounded to two digit)**4.** In both converted parts, append the currency Unit and Sub-Unit etc. in the returned Number spellings for Whole Number and Decimal part respectively.

**Note:** Likewise you can use this method for any other types of Numbers like Weight, Height or any kind of Units and Sub-Units.

```
Function spellCurrencyY(ByVal MyNumber) As String
Dim wholeNumber As String
Dim decimalNumber As String
Dim wholeCurrencyText As String
Dim decimalCurrencyText As String
Dim unitSingular As String
Dim subUnitSingular As String
Dim unitPlural As String
Dim subUnitPlural As String
unitSingular = " Dollar"
unitPlural = " Dollars"
subUnitSingular = " Cent"
subUnitPlural = " Cents"
' separate whole number part and decimal part
' Note: for decimal points, first round up to 2 digits
MyNumber = VBA.Format(Round(MyNumber, 2), "#0.00")
If (VBA.InStr(1, MyNumber, ".", vbTextCompare) > 0) Then
wholeNumber = VBA.Left(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) - 1)
decimalNumber = VBA.Mid(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) + 1, 2)
Else
wholeNumber = MyNumber
decimalNumber = "0"
End If
' get the number spelling separately
wholeCurrencyText = NumToLettersY(wholeNumber)
decimalCurrencyText = NumToLettersY(decimalNumber)
Select Case wholeCurrencyText
Case "One"
wholeCurrencyText = wholeCurrencyText & unitSingular
Case ""
wholeCurrencyText = "Zero" & unitSingular
Case Else
wholeCurrencyText = wholeCurrencyText & unitPlural
End Select
Select Case decimalCurrencyText
Case "One"
decimalCurrencyText = decimalCurrencyText & subUnitSingular
Case ""
decimalCurrencyText = "Zero" & subUnitSingular
Case Else
decimalCurrencyText = decimalCurrencyText & subUnitPlural
End Select
spellCurrencyY = wholeCurrencyText & " and " & decimalCurrencyText
End Function
```

**In American Number Scale System : 6734862.456 : ** Six Million Seven Hundred Thirty Four Thousand Eight Hundred and Sixty Two **Dollars** and **Fourty Six Cents.**

Basically, this function works on same principal as above. The only change from above function is that – you should call corresponding function for Indian Number Scale system.

```
Function spellCurrencyX(ByVal MyNumber) As String
Dim wholeNumber As String
Dim decimalNumber As String
Dim wholeCurrencyText As String
Dim decimalCurrencyText As String
Dim unitSingular As String
Dim subUnitSingular As String
Dim unitPlural As String
Dim subUnitPlural As String
unitSingular = " Rupee"
unitPlural = " Rupees"
subUnitSingular = " Paisa"
subUnitPlural = " Paise"
' separate whole number part and decimal part
' Note: for decimal points, first round up to 2 digits
MyNumber = VBA.Format(Round(MyNumber, 2), "#0.00")
If (VBA.InStr(1, MyNumber, ".", vbTextCompare) > 0) Then
wholeNumber = VBA.Left(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) - 1)
decimalNumber = VBA.Mid(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) + 1, 2)
Else
wholeNumber = MyNumber
decimalNumber = "0"
End If
' get the number spelling separately
wholeCurrencyText = NumToLettersX(wholeNumber)
decimalCurrencyText = NumToLettersX(decimalNumber)
' get the
Select Case wholeCurrencyText
Case "One"
wholeCurrencyText = wholeCurrencyText & unitSingular
Case ""
wholeCurrencyText = "Zero" & unitSingular
Case Else
wholeCurrencyText = wholeCurrencyText & unitPlural
End Select
Select Case decimalCurrencyText
Case "One"
decimalCurrencyText = decimalCurrencyText & subUnitSingular
Case ""
decimalCurrencyText = "Zero" & subUnitSingular
Case Else
decimalCurrencyText = decimalCurrencyText & subUnitPlural
End Select
spellCurrencyX = wholeCurrencyText & " and " & decimalCurrencyText
End Function
```

**In Indian Continent Number Scale System : 6734862.456 : ** Sixty Seven Lakhs Thirty Four Thousand Eight Hundred Sixty Two **Rupees** and **Fourty Six Paise.**

Following are the generic functions. We use these functions to translate single digits, tens and hundreds in corresponding numbers system.

In below example, I have used English translation for that.

Similarly, if you want to change them in any other language, you can easily change the corresponding Text part for each Numbers.

```
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then GetHundreds = "Zero": Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty"
Case 3: Result = "Thirty"
Case 4: Result = "Forty"
Case 5: Result = "Fifty"
Case 6: Result = "Sixty"
Case 7: Result = "Seventy"
Case 8: Result = "Eighty"
Case 9: Result = "Ninety"
Case Else
End Select
Result = Result & " " & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
```

Follow the following 5 simple steps to use this UDF.**Step 1. ** Open your Excel Workbook**Step 2. ** Press Alt + F11**Step 3. ** Add a Regular Module in Excel**Step 4. ** Copy and Paste the Below Code in the Module or in separate modules [your wish]**Step 5. ** Once you have copied and Pasted the above Code in a Module, you can use the Below Formula in your Workbook in any WorkSheet as shown in Below Picture:

Finally, you can download your FREE Excel workbook with UDF to convert numbers to word. Moreover, You can also look in to the code and customize it to fit your need.

Ofcourse, you can share your customized version here for other visitors who may get benefited – after all sharing is caring !!

Currently, this excel file has two user defined function – One for Indian Number Scale System and other for American English Scale system.

Finally here you go with your FREE Download file to play around !!

The post UDF to Convert Numbers to Letters appeared first on Welcome to LearnExcelMacro.com.

]]>The post Early Binding v/s Late Binding in Excel VBA Programming appeared first on Welcome to LearnExcelMacro.com.

]]>Early and Late Binding is a common phenomena across computer programming languages. In simple terms, Binding means – ** how and when methods or properties of an Object are compiled and checked. **

In case of Excel VBA [COM – Component Object Model] – this occurs when you are trying to automate something which is not part of **default object library of Excel** (in this case).

**For example: **If you are trying to access Outlook, Word, Internet Explorer, RegEx etc. using Excel VBA – you need to link those Object Libraries in your Excel to be able to use all the functions, methods, properties etc. from that Object Library. It is similar to *Import statement in Java* or *#Include in C *. If you do not know about these languages, you don’t need to worry about it. I am going to explain this concept in *“Excel Only Context”* and no other programming language.

Before you could access those Objects in your Excel VBA programming, you need to add those references in your Excel Workbook. This process of Referencing to non default object libraries is also called *Binding*.

**This Binding can be done in two ways in Excel VBA:-****1.**Early Binding or Static Binding**2.**Late Binding Or Dynamic Binding

As the name suggests, in early binding you add relevant reference before your program compiles. Early binding is done by adding the reference in Excel VBE screen itself. By doing this, all the methods, functions etc. are loaded.

**Step 1.** Go to VB Editor Screen (Alt+F11)**Step 2.** Tools –> References…

**Step 5.** Click OK

After clicking **OK** this object library is added in your VBE Project.

To check that, **press F2** and on the Object Library Window of Excel VBA, you can see that PowerPoint Object Library is added there as shown in below picture.

This is where you can see all the classes, methods, properties etc. related to the Reference you added in Excel VBA.

That’s all. Now you can declare variables of all the types defined in that Object Library, use their corresponding methods, properties etc. You can see in the below image.

This is why it is called early binding. Object, methods, properties etc. are checked during compilation and not at run time.

Early binding is defined something like following:

```
Dim newPowerPoint As PowerPoint.Application
Set newPowerPoint = New PowerPoint.Application
```

`Dim newPowerPoint As New PowerPoint.Application`

Following are the main advantage about Early binding:**Better Performance: ** Early binding is considerably faster than late binding. Reason for better performance is that program is already compiled before running it.**VBE Intellisense:** One of the major advantage of **Early Binding** is that VBE intellisense start displaying all the object, methods, properties etc. after pressing dot (.) like any default Object. Refer below image

**Compatibility Error: **This is version compatibility prone method of binding. If the version of the application is different in the computer where you are running the VBA application, then you will get a compilation error.

**For example: **if the Object Library which you have referenced in your Excel File is XYZ-V-1.0 and shared it with your colleague who has next version of this object library V-2.0. In this case, program will not even compile because if you have reference V-1.0 which this file is missing in his/her computer.

**More Object References – Bigger file size** The more number of reference you add in your Excel VBA application, bigger the file size becomes and it takes longer time to compile.

This was all about Early binding.

Opposite to the early binding, in late binding, Objects are checked and compiled at run time. You do not have to reference the Object Library before you run the program. In late binding Objects are created run time and then method or property related statements are compiled and then executed.

If your typed method or property does not exist, then you would not get any error until you run the program unlike early binding.

This is how late binding created in VBA. First you need to define a variable of Object type and then using **CreateObject(“Object Library Name”)** method of VBA, you can create and instance of that Object.

Since Object Libraries are not referenced before, you can not use **New** keyword to instantiate the Object.

```
Sub Create_PowerPoint_Object
'Define one variable of Object Type to hold the Application Object
Dim objNewPowerPoint as Object
'Create an Object for PowerPoint Application
Set objNewPowerPoint = CreateObject("PowerPoint.Application")
End Sub
```

**Version Independent: ** Since you are not referencing a version specific Object Library and referring instance of that Object in your program, it will not through any error if shared to another computer with a different version.

**Note:** If the Method, properties or objects are changed in different version then it may fail even by using this late binding.

**Faster compilation** Since there is no Object Library files attached with the VBA project, file size remains as is and compilation becomes faster.

**Faster compilation**

No Intellisense In this method you do not see intellisense any more. To use any of the method, property etc, you need to know the exact name of it otherwise you will see error at run time.

Compilation Error at run time All compilation error occurs at run time. You can not uncover such issue during compilation.

No Access to Object Model in Object Library In VBA project, you do not see Object Model in Object Library in excel VBA like Early Binding.

The post Early Binding v/s Late Binding in Excel VBA Programming appeared first on Welcome to LearnExcelMacro.com.

]]>