Resolving or fixings compiler error in VBA

Hello Friends,

Most of the times we face compiler error even we have written all the code correctly.

Cause:

When we get "Compiler Error in hidden module" error that means Your excel project is missing one or more reference libraries or its not properly registered. 

Identify:

When you get such error goto VBA Editor window and check all libraries under Tool->Library and you wil find word missing before library name which is causing an error.

Fix:

If library is missing:

For temporary fix you can select lower version of library.

 For example if your project is showing Microsoft Active Data Object 6.1 library as missing then you can select Microsoft Active Data Object 2.0 which is a lower version. 

Or you can try permanent solution: Download that library and store on users machine and register it using below command

Open command prompt and type

Regsv32 "complete path of library"

If library is available on user machine and still showing as missing:

Then first unload library using below command and then register it.

Regsv32 /u "complete path of library"

How to avoid this error:

Write your library dependant object code in late binding form

Learn excel VBA online from an industry experience developer to strengthen your knowledge go for a structured VBA course. 

How to use dynamic array in VBA / macro

Sorry Pravin to keeping your query waiting. You asked me a query about how to use dynamic array in VBA but in my last post I explained about basic use of array but my blog is for all the readers ;-).

So here we go, We get into such scenario where we actually doesn't know what length of array we would need in our program and here dynamic array helps you. Lets check a example program:

Ex1:

Sub MyDynArray ()

Dim MyArr() as integer, iArrLenghth as Integer, iCntr as Integer

iArrLenghth =  Sheet1.Range("A1").End(xlDown).Row

Redim Preserve MyArr( iArrLenghth-1)

For iCntr = 0 To iArrLenghth
   MyArr(iCntr) = Sheet1.Range("A" & iCntr+1)
Next iCntr

For iCntr = 0 To Ubound(MyArr)-1
   If MyArr(iCntr) = 5 Then
        MsgBox "Number 5 found"
        Exit For
   End If
Next iCntr

End Sub

In example code is storing all numbers in column A of sheet1 to an Array variable and then searches in array for number 5 and throws message if number 5 found and ends.

In example procedure at first line I have declared dynamic array with other variables

Dim MyArr() as integer, iArrLenghth as Integer, iCntr as Integer

while declaring dynamic array we doesn't specify its length.

Next line I get count of rows having data in column A of sheet1 into variable.

iArrLenghth =  Sheet1.Range("A1").End(xlDown).Row

At next line we set length of array variable at runtime so if column A has 5 rows of data then Array's length would be 0 to 4.

Redim Preserve MyArr( iArrLenghth-1)

Here we can also use only Redim no need to use Preserve keyword.

Preserve is required only if we have data stored in array and we do not want to loose it.
If our array is having some data stored and if we do not use Preserve keyword as shown below then all stored data will wipe out.

Redim MyArr( iArrLenghth-1)

Next I have use a for loop to store value from column A of sheet1 to array variable

For iCntr = 0 To iArrLenghth
   MyArr(iCntr) = Sheet1.Range("A" & iCntr+1)
Next iCntr

After that I have used another fir loop which I am using to loop through all elements of array variable and to find if any element is having value as 5 and if found then exit loop.

For iCntr = 0 To Ubound(MyArr)-1
   If MyArr(iCntr) = 5 Then
        MsgBox "Number 5 found"
        Exit For
   End If
Next iCntr

Here I have used inbuilt function UBound() which gives length if array. and I have used Exit For which get use to terminate For loop.

So here are some basics which will help you to manipulate  data in array and access to flexible array length.

Let me know in case of any queries.

Thanks

.