Defining Strings using Dim or Const

[RyanH]

Are there any advantages or disadvantages (such as speed in running code) to
defining strings as Const vs. Dim. For example,

Sub Macro1()

Dim myString As String 'variable length string

or

Dim myString As String * 14 'fixed length string

or

Const myString as String = "This is a Test"

End Sub

Thanks in Advance

 

[Jim Thomlinson]

Strings are one of the more inefficient variable types. A string is in fact a
null terminated array of characters. By defining the size of the string you
make the string more efficient as there is no overhead to determine the size
of the string but the draw back is that it is exactly the size you indicated
(blank characters at the end and all). That can make your work a bit more
difficult. In practice I don't tend to create fixed length strings.

The value of a constant is that it is constant. No matter what you can
always count on it. Don't underestimate the value of that. There is no
overhead to initialize it with code as you would with a variable. The single
greatest use for constants is to avoid magic numbers in your code. Magic
numbers are things like 60 which could be minutes in and hour or seconds in a
minute. Don't use 60 in your formulas. Use a constant.

 

[RyanH]

So defining strings as constants (if they are constant) is the most
effiecient way to go?

I use 60 all the time in my procedures. So you are saying Macro1 is more
efficient than Macro2? Why?

Sub Macro1()

Dim Answer As Single
Const mins As Byte = 60

Answer = (30 / mins)

MsgBox Answer

End Sub


Sub Macro2()

Dim Answer As Single

Answer = (30 / 60)
MsgBox Answer

End Sub

 

[Jim Thomlinson]

One is not distinctly more efficient than the other. Where you have defined
the constant is much more readable and maintainable. By knowing that the 60
is minutes then you can reasonably assume 30 is also minutes. Otherwise is
the 30 minutes or seconds?

As for making your code maintainable often you need to use offset to move a
defined number of rows or columns away. It is a bit of a silly example but it
demonsatrates my point. By making the offsets a constant all I need to do to
change all of my code is change 1 number in one spot and all is well. If I
had hard coded a 5 then it is much more difficult... Especially when the 5 is
not unique so that I can not just do a find and replace.

dim rng1 as range
const lngOffsetToSales as long = 5 '5 columns to right
const lngOffsetToNextRegion as long = 5 '5 rows down
set rng1 = range("A2")

msgbox rng1.offset(0,lngOffsetToSales )
set rng1 = rng1.offset(lngOffsetToNextRegion , 0)
msgbox rng1.offset(0,lngOffsetToSales )
set rng1 = rng1.offset(lngOffsetToNextRegion , 0)
msgbox rng1.offset(0,lngOffsetToSales )
set rng1 = rng1.offset(lngOffsetToNextRegion , 0)
msgbox rng1.offset(0,lngOffsetToSales )
set rng1 = rng1.offset(lngOffsetToNextRegion , 0)