Setting Default Values in Range When User Enters Blank Value

[MachAngle]

I have a Cell in my workbook named "Test" Lets say I have VBA module that
involves that cell to do some calculations..as an example Increment that cell
by 1.

Sub Increment()
Test = Test + 1
End Sub

When a user doesn't know what value to input into that cell, I want to allow
him to input a blank in that cell to signal my software to create a default
responce based on other cell values. My first task is to trap the user
inputing a "" value into the cell of interest ("Test"). I did this by using
the Worksheet_Change event:

Sub Worksheet_Change(ByVal Target As Range)
If Target.Value2 = "" Then
On Error Resume Next
m_Target.Name.Name = False 'm_Test in this case sets flag for
properties below (does not work)
End If
End Sub

Elsewhere in the workbook I have the following code to interface with my VBA
code:

Dim m_Test As Boolean
Public Property Get Test() As Double
If m_Test = False Then
Test = 12 'Default value for this cell
Else
Test = Range("Test").Value2
End If
End Property
Public Property Let Test(Value As Double)
If Value = vbNull Then
m_Test = False
Range("Test").Value2 = 12
Else
m_Test = True
Range("Test").Value2 = Value
End If
End Property

Does anybody out there have a better approach or know how to make this one
work?

 

[Chip Pearson]

I'm rather confused by what you are attempting to do. First of all,
what is m_Target and where is it declared and given a value? Second,
why are you using the Name property of m_Target? In your Test Let
property procedure, you have two mistakes. You are confusing "vbNull"
and "Null". vbNull is a Long type constant that when used with the
VarType function indicates that another variable has a Null value.
vbNull is a numeric constant with a value of 1.

E.g.,

Dim X As Variant
X = Null
Debug.Print "VarType(X) = " & VarType(X) & _
" (1 = vbNull)." & _
" vbNull is type: " & TypeName(vbNull) & _
" value " & CStr(vbNull)

The output of this is

VarType(X) = 1 (1 = vbNull). vbNull is type: Long value 1

This shows that X is Null, but vbNull is not Null -- it is a Long
having a value of 1. To test for a Null value, you use either

If VarType(X) = vbNull Then
which is the same as
If VarType(X) = 1 Then

or you can use the IsNull function:

Dim Y As Variant
Y = 1
Debug.Print IsNull(Y), Y = vbNull

This displays
False True

indicating that Y in not null (IsNull returns False) and that Y is
equal to the constant vbNull (both have a value of 1).

All this is academic because in the Let property you have declared
Value as a Double. As such, it can never have a Null value. That is,
IsNull(Value) will always return False, as will

If VarType(Value) = vbNull

If you attempt to assign a Null value to anything but a Variant type
variable, you'll get an "Invalid Use Of Null" error. E.g., the
following code will fail.

Dim D As Double
D = Null '<<<< Invalid use of null

Or, if you assign Null to a Variant and pass that to your Let
procedure,

Dim V As Variant
V = Null

Test = V

You'll get a "ByRef argument type mismatch" error on the line of code
that calls the Let procedure.

I think you can get rid of your Property procedures and do everything
within the _Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$5" Then
If Len(Target.Text) = 0 Then
Application.EnableEvents = False
Target.Value = 12
Application.EnableEvents = True
End If
End If
End Sub

This tests if Target is cell A5, and if so, then if it has a value in
it, the code leaves that value in place. If the cell is empty, it gets
a value of 12.

As a general rule, you should avoid module- or project- scoped
variables if at all possible. Thus, your declaration

Dim m_Test As Boolean

should be avoided, and the code adapted such that it doesn't require
an external variable. Perhaps a Static variable might work.

I think you are making things more complicated that they need be.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[MachAngle]

Chip et al..
Thank you for the helpful comments. In tring to keep things simple and
focused on the problem I am having, I failed to mention the motivation behind
why I did some of the things you are seeing.

A little background...I am writing an engineering application (which is why
most of my variables in the Get/Let Properties are Double). There are about
3000 Get/Let property combinations simular to the example "Test" property
containing input data, output data, and controls for a complex system of
equations contained in the underlying VBA code (modules and classes) that I
want to hook into the Excel Workbook. I don't want to use references to
cells expressed like Range("Test").value2 because it obscures the meaning of
the equations in what is already a very complex system of equations.
Genarally, the cells in the Excel Workbook need to be global in scope to the
equations in the VBA code. In my case they describe the geometry of an
airplane being analized by my VBA code.

1.)What is m_Target and where is it declared and given a value? Why are you
using the Name property of m_Target?
The purpose of the m_Target.Name.Name statement in the Worksheet_Change
event hander was to identify the Name of the cell the user wanted to generate
a default value for and set itscorresponding properties "default flag" to
False (meaning user requests a default...see m_Test in Properties) When the
underlying VBA code asks for any of the 3000 variables exposed to Excel, a
"m_Test" set to "False" causes the default value to be returned, otherwise
the Value2 property of the named cell is "get".

The "Let" Property enables the VBA code to change the value2 property of
the named cell.

3.)In your Test Let property procedure, you have two mistakes. You are
confusing "vbNull" and "Null".
Thanks for the comparision between vbNull and Null..being self taught, I am
weak in my understanding of this. Sounds like I should be using Null or ""

4.) All this is academic because in the Let property you have declared Value
as a Double. As such, it can never have a Null value.
I am troubled by your statement that Doubles never being a Null... I was
counting on Range("Test").Value2 = "" to be the signal to my software that
the user wants to use the system generated default for this cell.

5.)As a general rule, you should avoid module- or project- scoped variables
if at all possible.... Perhaps a Static variable might work.
I can see my variables being static for one cycle through Excel's
Calculation Engine, but I don't see how being static would help me identify
user selected default values or provide the logical link to the underlying
VBA code.

6.) I think you are making things more complicated that they need be.
My mode of operation generally does tend to bring complex solutions to
simplier problems...I am open to simpler solution that meet my needs.

Thank You for your good comments. I hope the explanation helps better
define my problem.

MachAngle