My main goal for my Project was to design a user interface to help create the 'inform' file that can be used for
PHYSICA. For this I am deciding whether to use HTML and JavaScript or create it using Excel. So to test it out I am going to create a test case for each that will cover the following critea:
1. Lists. Some lines of code of physica have different options
2. Expansion. This is for if the user wants to specify for example Materials properties and they had multiple materials, space would be required.
3. Write to file. In each case I need to know how easy it is to write to file
4. Read from file. this is for if the user need to change something. this is more tricky.
Now the hard part my knowledge of both is not extensive so hopefully when I learn something new I'll post it on here (I hope).
Starting on Excel 1st.
Lists. From my research from today there are two ways of creating lists.
No Visual basic required
1st thing 1st you need to specify options for your list. My list is commands for a bit of code called 'Runtime' the options are "ON" or "OFF" this I put in column E. For the user I have put a prompt in Cell A2 simply called 'Runtime'
Now select the cell B2 then go to data tab and click Validate.
Although this is a mac version of Excel the options are similar for Windows users. In the drop down option select "list" and then specify what the options are and then your done.
Visual Basic form of a list.
Actually there are multiple ways of creating lists. One option is using a "Combo Box" but since I want it in the cell I'm not going to cover this.
Sub test()
Dim sList As String
Dim N As Integer
Dim dv As Validation
sList = "On,Off"
Set dv = Range("C2").Validation
dv.Delete
'.delete i think will clear the range it has been assigned
dv.Add xlValidateList, xlValidAlertStop, xlBetween, sList
'expression.Modify(Type, AlertStyle, Operator, Formula1, Formula2)
'below is what may have to use for intergers problem
'i tested the word thing if you enter something than not on the list it give a warning box
'website=http://msdn.microsoft.com/en-us/library/bb210323%28v=office.12%29.aspx
End Sub
if you see ' line its a comment.
To be honest I did adapt it from this
website
There is another way which is good to use for constraints on variables(from this
website).
With Range("e5").Validation
.Add Type:=xlValidateWholeNumber, _
AlertStyle:= xlValidAlertStop, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = "Enter an integer from five to ten"
.ErrorMessage = "You must enter a number from five to ten"
End With
Hope you learned something I know I did.
UPDATE 21/6/2011 09.49AM
Just found out he second bit of code crashes if you run it more than one so it should be:
sub test()
Set dv = Range("B4").Validation
With dv
dv.Delete
.Add Type:=xlValidateDecimal, _
AlertStyle:=xlValidAlertInformation, _
Operator:=xlBetween, Formula1:="0", Formula2:="10"
.InputTitle = "Real"
.ErrorTitle = "Must be Real"
.InputMessage = "Enter an Real from zero to ten"
.ErrorMessage = "You must enter a number from zero to ten"
End With
End Sub
