VBA: Specifying a range using a string constant

Question

I am trying to make a simple function that counts the number of pipelines listed in a sheet codenamed LINES. The range in the sheet LINES where the lines will be listed is $A$2:$A$1048576 and I have named the range "LineList". I have made a module containing all my global constants that are hardcoded, so that I can alter them easily in the future. The name of the LineList range is one of those, defined as such:

Public Const LINELIST_RNG As String = "LineList"

My function is the following. For some reason this command is failing, due to the LINELIST_RNG being empty:

Function numLines() As Integer
    numLines = WorksheetFunction.CountA(LINES.Range(LINELIST_RNG))
End Function

However, if I hard-code the range name into this function, it works fine:

numLines = WorksheetFunction.CountA(LINES.Range("LineList"))

I am unsure why the .Range object cannot take LINELIST_RNG as an argument but will take "LineList".

Double check spelling? I can't see how that can happen, have you set a breakpoint verified the const's value ? — Alex K.
– Alex K., Commented Mar 27, 2014 at 15:34
I do what you're proposing ALL THE TIME... Off the top of my head, I don't think that's why you're getting the error... — John Bustos
– John Bustos, Commented Mar 27, 2014 at 15:34
Also as that's a large range as long may be better as an integer overflows beyond +32767 — Alex K.
– Alex K., Commented Mar 27, 2014 at 15:35
I would not have thought so, at runtime the semantics of the value being in a string const should be exactly the same as when its a string literal — Alex K.
– Alex K., Commented Mar 27, 2014 at 15:39

Aidan · Accepted Answer · 2014-03-27 17:08:26Z

2

The following code works fine:

Option Explicit

Public Const LINELIST_RNG As String = "LineList"

Public Sub test()

    Dim r1 As Range
    Dim r2 As Range

    Set r1 = Sheets(1).Range("LineList")
    Set r2 = Sheets(1).Range(LINELIST_RNG)

    MsgBox r1.Cells(1, 1).Value
    MsgBox r2.Cells(1, 1).Value

End Sub

so there must be something else going on in your code. Turn on option explicit and it may point you in the right direction

answered Mar 27, 2014 at 17:08

Aidan

1,8101 gold badge16 silver badges23 bronze badges

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

1 Comment

teepee Over a year ago

I figured out that it had to do with the order of declarations, as I explained in my answer below, but I don't understand why really. Why is it that you cannot declare a constant below the declarations of other functions, even when it is not used by the functions above it, rather only the functions declared below it?

teepee · Accepted Answer · 2014-03-27 17:08:34Z

2

It appears that the problem arose from the order of declarations in my module. To give context, the declarations of

Public Const LINELIST_RNG As String = "LineList"
Function numLines() As Integer

were done after a list of Public Const and Function declarations and so they weren't working.

My module looked like:

Public Const ...
Public Const ...
Function blabla1 ...
Function blabla2 ...
Public Const LINELIST_RNG ...
Function numLines() As Integer ...

Now that I've moved all Public Const declarations to the beginning of the module, everything functions properly.

answered Mar 27, 2014 at 17:08

teepee

2,7243 gold badges28 silver badges53 bronze badges

Collectives™ on Stack Overflow

VBA: Specifying a range using a string constant

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related