VBA & Macro Problems

How to Fix Invalid Use of Property Error in VBA Connection Code

Kushani Nimanthika Sep 27, 2026 871 views

Question details

The user needs to resolve an 'Invalid Use of Property' error triggered when writing an ADODB connection string in VBA.

How to Fix Invalid Use of Property Error in VBA Connection Code

Product: Excel / VBA
Device & OS: not provided
Scenario: Writing a VBA macro to connect to an external database using the ADODB library.
Observed behavior: The VBA compiler halts execution and displays an 'Invalid Use of Property' error because the 'Set' keyword is incorrectly used to assign a string property.

Before you start

Ensure you have enabled the 'Microsoft ActiveX Data Objects (ADO)' reference in your VBA Editor (Tools > References) to allow ADODB connection scripts to compile.

Solution 1Recommended

Remove the 'Set' Keyword from the Property Assignment

Fix the error by using the 'Set' keyword only when creating the connection object, and removing it when assigning the string value to the ConnectionString property.

In Visual Basic for Applications (VBA), the 'Set' keyword is strictly reserved for assigning objects to variables (like Worksheets, Ranges, or Connection objects). A connection string, however, is a standard text string data type. When you try to assign a basic string to an object's property using 'Set', VBA throws the 'Invalid Use of Property' error.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor and locate the macro script causing the error.

Instantiate the Connection Object

Ensure you are correctly creating the connection object using 'Set'. Your code should begin with: Dim conn As ADODB.Connection followed by Set conn = New ADODB.Connection.

Correct the String Assignment

Locate the line assigning the connection string. Remove the 'Set' keyword so it reads exactly like this: conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\CeramicCrafters\CeramicCraftersProduction.accdb".

Run and Test the Macro

Save your code and press F5 or click the Run button to execute the macro. The connection should now establish without throwing the property error.

Remove the 'Set' Keyword from the Property Assignment

Best Practice: Always remember the basic VBA rule: use 'Set' for objects, and the standard equals sign '=' for properties and basic data types.

Advanced Spreadsheet Capabilities

Write and Execute VBA Macros Flawlessly in WPS Office

WPS Spreadsheets provides a powerful, built-in VBA editor that is fully compatible with Excel macros. This allows you to seamlessly connect to external databases, automate repetitive tasks, and manage complex datasets without unexpected syntax headaches.

1. Download WPS Office: Visit the official website to download and install WPS Office on your computer.
2. Open WPS Spreadsheets: Launch WPS Spreadsheets and open your macro-enabled workbook (.xlsm).
3. Access the VBA Editor: Navigate to the 'Developer' tab in the ribbon and click on 'Visual Basic', or use the Alt + F11 shortcut.
4. Write and Run Your Macro: Paste your corrected ADODB connection script into the module and click 'Run' to execute your database automation.

Seamless compatibility with Microsoft Excel VBA syntax and macrosBuilt-in robust VBA Editor for advanced database automationHigh performance handling of ADODB connections and external data queriesLightweight, fast, and familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'User-defined type not defined' error when declaring ADODB.Connection?

This error occurs if the ADO library is not referenced in your project. To fix this, go to Tools > References in the VBA editor and check the box for 'Microsoft ActiveX Data Objects x.x Library' (choose the highest version available).

When exactly should I use the 'Set' keyword in VBA?

The 'Set' keyword must be used only when assigning an object reference to a variable. Examples of objects include a Worksheet, Workbook, Range, or ADODB.Connection. You should never use 'Set' for assigning primitive data types like Strings, Integers, or Booleans.

Can I use ADODB connections in WPS Spreadsheets?

Yes, the advanced versions of WPS Spreadsheets fully support VBA macros, including ADODB connections to query external databases like MS Access and SQL Server, provided the correct OLEDB providers are installed on your Windows system.

Excel & Data Problems