Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Default Values in Range When User Enters Blank Value
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Default Values in Range When User Enters Blank Value
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) On Sun, 26 Apr 2009 07:52:02 -0700, MachAngle wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Default Values in Range When User Enters Blank Value
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 "Chip Pearson" wrote: 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) On Sun, 26 Apr 2009 07:52:02 -0700, MachAngle wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting default values in a drop down list in excel? | Excel Discussion (Misc queries) | |||
Setting a default sort for a range | New Users to Excel | |||
Copy cell properties from master when user enters a new line. | Excel Programming | |||
Setting up a Custom type, User-defined, Default Chart | Charts and Charting in Excel | |||
User enters data in popup box - its entered into desired cell | Excel Programming |