User-defined Function in VBA Excel is Not Accessible?

Question

I have this function in a module in my VBE, but when I try to access it in my userforms, I get an error because it cannot be detected by VBE:

This is the Col_Letter function:

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

I also tried adding Public:

Public Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Your module is named Col_Letter as well. Change the name of the module and it would work. — Vityata
– Vityata, Commented Jun 26, 2018 at 9:26
I always prefix module names with 'm', class modules with 'cls'. Now I know why 8-) — Patrick Honorez
– Patrick Honorez, Commented Jun 26, 2018 at 9:33

Vityata · Accepted Answer · 2018-06-26 09:27:26Z

3

Naming a module with the same name as a public funciton is quite a bad idea. Change the name of the Col_Letter module or the name of the function.

answered Jun 26, 2018 at 9:27

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Pherdindy Over a year ago

Right. I've been really confused as to why it's not working. Thanks

Vityata Over a year ago

@MarcSantos - you are welcome. Making the function public was a good first step.

Chronocidal Over a year ago

@MarcSantos Something that I find helps is to use a type-declaration prefix to objects and variable (e.g. lng_Users for a Long variable, frm_Users for a UserForm, mod_Users for a Module, etc) - it also makes it much easier when you review code at a later date to work out what everything is/does

Vityata Over a year ago

@Chronocidal - this is a great article by the creator of StackOverflow, which goes 100% against this practice. Read it, it may change your coding style forever - joelonsoftware.com/2005/05/11/making-wrong-code-look-wrong

Collectives™ on Stack Overflow

User-defined Function in VBA Excel is Not Accessible?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related