J
Jeff Ciaccio
I am trying to set up a simulation that will calculate the position and
velocity of an object after the user sets the initial conditions, but I am
running into snags.
Snags: (I have put * in the code for each snag)
1) Workbook(1).Activate I need to lock all of the cells except a few
that the user may change to set the initial conditions. Is this not the
proper syntax to activate the whole workbook?
2) The values did not show up in the cells (time, position, vel, and acc). I
used range("D"& row), where row is an integer set in the code. Will this
work, or do I need to first change the integer to a string? (Does VBA
automatically convert data types when necessary?)
3) Is there a preferred method to lock all of the cells? I have chosen to
activate the whole workbook and then set the property of the active
selection.
' Sub Worksheet_Change(ByVal Target As Range) ' this will trigger if any
part of the sheet is changed
Sub One_D_Motion()
Dim time1 As Single
Dim vInit1 As Single
Dim acc1 As Single
Dim xInit1 As Integer
Dim acc2 As Single
Dim x As Single
Dim vel As Single
Dim row As Integer
' This will find the position and velcoity for the first 20 seconds
' The user must set the initial conditions and the time period for the
accelerations
' Written 6/12/2008 by Jeff Ciaccio
'
' Check to see if the values are in range. If not, prompt the user for
another number.
*1) Workbook(1).Activate 'Lock all of the cells to protect them
from chages
*3) Selection.Locked = True
*3) Selection.FormulaHidden = False
Range("B1:B4,B6").Select 'Unlock only the cells the user may change
Range("B6").Activate
Selection.Locked = False
Selection.FormulaHidden = False
'Protect the sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("B1").Select ' Check initial position
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="-5000", Formula2:="5000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Position"
.ErrorTitle = ""
.InputMessage = "Enter a whole number between -5000 and +5000. "
.ErrorMessage = "Too Far!!"
.ShowInput = True
.ShowError = True
End With
Range("B2").Select ' Check initial velocity
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="-100", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Velocity"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With
Range("B3").Select ' Check initial acceleration
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="-20", Formula2:="20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Acceleration"
.ErrorTitle = "Too Much Force!!"
.InputMessage = "Put in a number between -20 and 20"
.ErrorMessage = "Put in a number between -20 and 20"
.ShowInput = True
.ShowError = True
End With
Range("B4").Select ' Check initial time period
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="0.1", Formula2:="19.9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "First time period"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With
' Record the values the user has chosen
time1 = Range("b4").Value
vInit1 = Range("b2").Value
acc1 = Range("b3").Value
xInit1 = Range("b2").Value
acc2 = Range("b6").Value
' Set the first row cells
Time = 0
row = 2
Range("D2").Value = 0
Range("E2").Value = vInit1
Range("f2").Value = acc1
Range("d3").Select
Do Until Time > time1
Time = Time + 0.1
row = row + 1
x = xInit1 + vInit1 * Time + 1 / 2 * acc1 * Time ^ 2
vel = vInit1 + vInit1 * acc1
*2) Range("d" & row).Value = Time
*2) Range("e" & row).Value = vel
*2) Range("f" & row).Value = acc1
Loop
End Sub
velocity of an object after the user sets the initial conditions, but I am
running into snags.
Snags: (I have put * in the code for each snag)
1) Workbook(1).Activate I need to lock all of the cells except a few
that the user may change to set the initial conditions. Is this not the
proper syntax to activate the whole workbook?
2) The values did not show up in the cells (time, position, vel, and acc). I
used range("D"& row), where row is an integer set in the code. Will this
work, or do I need to first change the integer to a string? (Does VBA
automatically convert data types when necessary?)
3) Is there a preferred method to lock all of the cells? I have chosen to
activate the whole workbook and then set the property of the active
selection.
' Sub Worksheet_Change(ByVal Target As Range) ' this will trigger if any
part of the sheet is changed
Sub One_D_Motion()
Dim time1 As Single
Dim vInit1 As Single
Dim acc1 As Single
Dim xInit1 As Integer
Dim acc2 As Single
Dim x As Single
Dim vel As Single
Dim row As Integer
' This will find the position and velcoity for the first 20 seconds
' The user must set the initial conditions and the time period for the
accelerations
' Written 6/12/2008 by Jeff Ciaccio
'
' Check to see if the values are in range. If not, prompt the user for
another number.
*1) Workbook(1).Activate 'Lock all of the cells to protect them
from chages
*3) Selection.Locked = True
*3) Selection.FormulaHidden = False
Range("B1:B4,B6").Select 'Unlock only the cells the user may change
Range("B6").Activate
Selection.Locked = False
Selection.FormulaHidden = False
'Protect the sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("B1").Select ' Check initial position
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="-5000", Formula2:="5000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Position"
.ErrorTitle = ""
.InputMessage = "Enter a whole number between -5000 and +5000. "
.ErrorMessage = "Too Far!!"
.ShowInput = True
.ShowError = True
End With
Range("B2").Select ' Check initial velocity
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="-100", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Velocity"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With
Range("B3").Select ' Check initial acceleration
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="-20", Formula2:="20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Acceleration"
.ErrorTitle = "Too Much Force!!"
.InputMessage = "Put in a number between -20 and 20"
.ErrorMessage = "Put in a number between -20 and 20"
.ShowInput = True
.ShowError = True
End With
Range("B4").Select ' Check initial time period
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="0.1", Formula2:="19.9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "First time period"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With
' Record the values the user has chosen
time1 = Range("b4").Value
vInit1 = Range("b2").Value
acc1 = Range("b3").Value
xInit1 = Range("b2").Value
acc2 = Range("b6").Value
' Set the first row cells
Time = 0
row = 2
Range("D2").Value = 0
Range("E2").Value = vInit1
Range("f2").Value = acc1
Range("d3").Select
Do Until Time > time1
Time = Time + 0.1
row = row + 1
x = xInit1 + vInit1 * Time + 1 / 2 * acc1 * Time ^ 2
vel = vInit1 + vInit1 * acc1
*2) Range("d" & row).Value = Time
*2) Range("e" & row).Value = vel
*2) Range("f" & row).Value = acc1
Loop
End Sub