VBA Compile error after Update

You may get one or all of the below mentioned errors (or no error notification at all, but excel workbook may not work) after updating our software’s/Libraries (Hello Kite v2.0, Hello Upstox v2.0, KiteNet, UpstoxNet). This article may look lengthy but the procedure is very simple and the terms/examples used here can be understand easily by non-technical people as well.

1. Can’t find project or library
2. ActiveX component can’t create object
3. Automation Server Can’t Create Object
4. class does not support Automation or does not support expected interface
5. Method or data member not found

The below article is for KiteNet/Hello Kite v2.0, but the procedure is same for other software’s as well.

Why excel throws error after updating DLL?
This error mainly happens if your VBA code calls functions from external DLL and COM object.

Macro-Enabled excel workbook is just like normal windows software, it will work only with the matching CPU/OS/Program architecture in which the excel workbook is compiled. For example, if you made (compile) a excel workbook in your system which is 32 bit and you send that excel file to your friend who is having 64 bit system, then he will get VBA error on opening your excel file. The solution is, you have to just re-compile the VBA (excel workbook) just once.

When we compile VBA,all the codes (UDF) are converted to machine code along with the information of external DLL/COM locations and versions.

Example1:
Assume your system is 32 bit and installing KiteNet v1.3.0.
KiteNet will install the DLL in C:\windows\System32 and will make a registry key with value 1.3.0

Now you have made a macro-enabled excel workbook with functions from KiteNet and it is working fine.After sometime, you updated KiteNet v1.4.0, this time registry value modified to 1.4.0. When you open your excel workbook after update, it will give error, because the excel is searching for registry value with 1.3.0. When you recompile your workbook, the VBA will update the information’s related to KiteNet.dll (i.e location, version etc).

Example2:
Assume your are sending the same above compiled workbook to your friend who is using 64 bit system. He also installed KiteNet, but as per his system bit, the location of the DLL is C:\windows\SysWow64

When he opens your workbook, he will get error, because the excel is searching for the DLL in C:\windows\System32. When he re-compiles your workbook, the VBA will update the information’s related to KiteNet.dll (i.e location, version etc).

Ensure you have installed latest version, else download and install from https://codeadvisor.in/trading-apps
Close all excel workbooks, check task manager for excel process and kill it if running
Close all unnecessary running applications (like chrome, amibroker, pi, nest trader etc)

1. Disable Macros
Open a new excel sheet
Open ‘Trust center’ settings
Home –> Excel Options –> Trust Center –> Trust Center Settings
Macro settings –> Select ‘Disable all Macros without notification’
Press OK to close Trust Center settings
Close the new excel sheet without saving

2. Remove DLL References
Open your excel workbook which has error.
Goto Visual Basic editior (Alt + F11)
Excel Menu –> Developer Tab –> Visual Basic

In Visual Basic editor
Menu –> Tools –> References

In References window
Uncheck ‘DotNet Library For Kite Connect API’ if checked
Uncheck ‘Excel Tools for Trading’ if checked
Press Ok to close Reference window

3. Add DLL References
Again open the Reference window
Menu –> Tools –> References
Check ‘DotNet Library For Kite Connect API’
Check ‘Excel Tools for Trading’
Press Ok to close Reference window

4. Compile VBA
Before compiling VBA, we have to edit each module where the external DLL is initialized or code contains functions from external DLL (Ex: Public Kite As New Kite, Kite.GetOrderStatus). Some of the module names are KiteXL, UpstoxXl, BridgeOrder, OrbMod, PlbMod etc.

Go to each modules, double click on the module name to open the code.
1. Select the entire code (Ctrl + A)
2. Cut the entire code (Ctrl + X)
3. Again, paste the entire code (Ctrl + V)

Once you done editing, try compiling the VBA project.
Menu –> Debug –> Compile VBA Project

If you get error ‘Method or data member not found’
Go to the error line and comment it by adding single quote (‘) at the start of the line or you can delete the entire Sub/Function.
Now compile the VBA, if you get another error, repeat the above till your VBA project is compiled successfully without any errors.
Save the VBA (Ctrl + S)

5. Enable Macros
Open ‘Trust center’ settings
Home –> Excel Options –> Trust Center –> Trust Center Settings
Macro settings –> Select ‘Enables all Macros’
Press OK to close Trust Center settings
Save and close your excel workbook

Now your excel workbook will work.

Follow the above procedure for all your excel workbooks which has above errors.

1. Disable Macros

 

 

 

 

 

 

 

 

 

 

 

2. Remove DLL References

 

 

 

 

 

 

 

 

 

 

 

3. Add DLL References

 

 

 

 

 

 

 

 

 

 

 

4. Compile VBA


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. Enable Macros

 

 

 

 

 

 

 

 

 

 

 

Was this article helpful?

Related Articles

Leave A Comment?