VBA IntelliSence Not Appearing / Working, How Do I Get It To Work?

Question

I am trying to wrote VBA code in Excel and realize that InetlliSense is not working.

I noticed / tested this by typing "docmd." and noting that when tying the "." that:

i) A list of options did not appear;

ii) Pressing CTRL + J resulted in a beeping sound; and

iii) Pressing CTRL + Spacebar resulted in beeping sound.

I then went into a block of I was writing which included:

      Din FoundCell As Range

     Set FoundCell = ws.Columns("B").

and once again the IntelliSense is not working (i.e., the list of methods did not appear, pressing CTRL + J resulted in a beeping sound, and pressing CTRL + Spacebar resulted in beeping sound).

I then -- as the final test -- went on to type " Set FoundCell = ws.Columns("B").Find(" and again InetlliSense is not working (i.e., the list of methods did not appear, pressing CTRL + J resulted in a beeping sound, and pressing CTRL + Spacebar resulted in beeping sound).

I should also note that I have the following references selected:

It is hard enough to code let alone code without IntelliSense.

THE ASK: Please explain how to get IntelliSense to work..

Thank you.

Answer 1

HansV, excellent and thank you! Mystery solved.

Question: Is it therefore best practice to have the scope of variables be the entire module instead of the sub-procedure as I had done to avoid this issue?

Thank you.

Answer 2

If you use a variable of the same name and type in multiple procedures/functions, but assign (set) it in each procedure/function separately, I'd declare it in each procedure/function.

But if you want to be able to assign it in one procedure and then use the assigned value in another one, you should declare it at the top of the module.

Remark: a declaration at the top of a module is private (local) to that module by default. That is, it will be visible to all procedures/functions in the same module, but invisible to procedures/functions in other modules.

If you want a variable to be visible to all procedures/modules in the workbook, regardless of which module they are in, declare the variable as Public.

For example:

Dim ws As Worksheet

makes the variable ws available to all procedures/functions in the same module.

Public ws As Worksheet

makes the variable available in all modules in the workbook.

Answer 3

HansV, appreciated and thank you.

Your answer males perfect sense and very much appreciate you both solving my problem and teaching me something new.

Best regards,

Joel