|
Combining Text and a date in the same cell
="Date: "&TEXT(F1,"dd/mm/yy")
=(Load*interest)/(1-(1+interest)^-(periods))
Https//
= Load Items:
Using the Frequency Function
http://www.youtube.com/watch?v=jbaXnT5CX18
Public Type Scape
Sugar As Single
Spice As Integer
End Type
Public Grid(1 To 30, 1 To 30) As Scape
Public id As Integer
Public AllAgents As New Collection 'collection of objects object
Public Agn As Agent 'used within the class, hence public
Public Const gridsize = 30
Sub MainCycle()
Columns("A:A").Select
Selection.ClearContents
Range("A1").Select
Set Agn = New Agent ' creates the first agent
AllAgents.Add Agn
Agn.Name = "Bob"
Agn.x = 25
Agn.y = 25
Agn.Sugar = 100
Agn.Spice = 100
Agn.Generation = 1
'AgnNew.AAge = 1
n = 1
For i = 1 To 20000 'number of iterations
Harvest 'calls harvest procedure
DisplayGrid
id = 1
For Each Agn In AllAgents
Agn.LookAndMove
Agn.Eat
Agn.Appear
Agn.Breed
id = Agn.Die(id)
'this is an example of the function in use. Note it is defeined as a 'function' and the reference to die = something. Note when agent dies, function returns id -1, hence when deleted from the AllAgents collection the next agent in the list will have the die function called, and not skipped over.
id = id + 1
Next Agn
Worksheets("Sheet2").Cells(n, 1).Value = AllAgents.Count ' collections object build in procedure count
n = n + 1
Worksheets("Sheet2").Cells(1, 1).Activate ' refreshed the graph sheet
Next
' Although the following two lines aren't
' necessary, they're a good idea.
Set Agn = Nothing
Set AllAgents = Nothing
End Sub
Sub DisplayGrid()
For i = 1 To gridsize
For j = 1 To gridsize
If Grid(i, j).Sugar > 0 And Grid(i, j).Spice > 0 Then
Worksheets("Sheet1").Cells(i, j).Interior.ColorIndex = 38 ' spice & sugar
End If
If Grid(i, j).Sugar = 0 And Grid(i, j).Spice = 0 Then
Worksheets("Sheet1").Cells(i, j).Interior.ColorIndex = xlNone 'nothing
End If
If Grid(i, j).Sugar = 0 And Grid(i, j).Spice > 0 Then
Worksheets("Sheet1").Cells(i, j).Interior.ColorIndex = 40 ' spice
End If
If Grid(i, j).Sugar > 0 And Grid(i, j).Spice = 0 Then
Worksheets("Sheet1").Cells(i, j).Interior.ColorIndex = 34 'sugar
End If
Next
Next
End Sub
Sub Harvest()
For i = 1 To gridsize
For j = 1 To gridsize
If Int(Rnd * 20) + 1 = 1 Then
Grid(i, j).Sugar = Grid(i, j).Sugar + 7
Grid(i, j).Spice = Grid(i, j).Spice + 7
End If
Next
Next
End Sub
Public Property Get Name() As String
Name = AName
End Property
Public Property Let Name(Value As String)
AName = Value
End Property
Public Property Get x() As Integer
x = Ax
End Property
Public Property Let x(Value As Integer)
Ax = Value
End Property
Public Property Get y() As Integer
y = Ay
End Property
Public Property Let y(Value As Integer)
Ay = Value
End Property
Public Property Get Sugar() As Single
Sugar = ASugar
End Property
Public Property Let Sugar(Value As Single)
ASugar = Value
End Property
Public Property Get Spice() As Single
Spice = ASpice
End Property
Public Property Let Spice(Value As Single)
ASpice = Value
End Property
Public Property Get Generation() As Integer
Generation = AGeneration
End Property
Public Property Let Generation(Value As Integer)
AGeneration = Value
End Property
Public Sub LookAndMove()
Dim h As Integer
Dim direction As String
h = 0
If x = 1 Then x = 2
If x = gridsize Then x = gridsize - 1
If y = 1 Then y = 2
If y = gridsize Then y = gridsize - 1
If Grid(x - 1, y).Sugar + Grid(x - 1, y).Sugar > h Then
h = Grid(x - 1, y).Sugar + Grid(x - 1, y).Sugar
direction = "Right"
End If
If Grid(x + 1, y).Sugar + Grid(x + 1, y).Sugar > h Then
h = Grid(x + 1, y).Sugar + Grid(x + 1, y).Sugar
direction = "Left"
End If
If Grid(x, y - 1).Sugar + Grid(x, y - 1).Sugar > h Then
h = Grid(x, y - 1).Sugar + Grid(x, y - 1).Sugar
direction = "Down"
End If
If Grid(x, y + 1).Sugar + Grid(x, y + 1).Sugar > h Then
h = Grid(x, y + 1).Sugar + Grid(x, y + 1).Sugar
direction = "Up"
End If
Select Case direction
Case Is = "Right"
x = x - 1
Case Is = "Left"
x = x + 1
Case Is = "Down"
y = y - 1
Case Is = "Up"
y = y + 1
Case Else
x = x + Int(Rnd * 3) - 1
y = y + Int(Rnd * 3) - 1
End Select
End Sub
Public Sub Appear()
Worksheets("Sheet1").Cells(x, y).Interior.ColorIndex = 5
End Sub
Public Sub Eat()
Dim a As String
ASugar = Grid(x, y).Sugar + ASugar
Grid(x, y).Sugar = 0
ASpice = Grid(x, y).Spice + ASpice
Grid(x, y).Spice = 0
ASpice = ASpice - 5
ASugar = ASugar - 5
End Sub
Public Sub Breed()
If ASugar > 100 And ASpice > 100 Then
Dim AgnNew As Agent
Set AgnNew = New Agent ' creates new agent
AllAgents.Add AgnNew ' procedure adds AgnNew to the AllAgents collection
AgnNew.Name = "Bob." & AGeneration + 1
AgnNew.x = Ax
AgnNew.y = Ay
AgnNew.Sugar = Int(ASugar / 2)
AgnNew.Spice = Int(ASpice / 2)
AgnNew.Generation = AGeneration + 1
ASugar = Int(ASugar / 2)
ASpice = Int(ASpice / 2)
End If
End Sub
Public Function Die(id As Integer) ' Note Function returens the id - 1 back to where it's called
If ASpice < 0 Or ASugar < 0 Then
AllAgents.Remove (id) 'deletes id-th from the AllAgents List
Die = id - 1
Else
Die = id
End If
End Function
Definitions
Dim x as Integer 'define local variable
Public p as Single 'define global variable
Public Type Scape 'define type
Sugar As Single
Spice As Integer
End Type
Scape.Sugar = 0.5 'can apply to a
Const Maximum = 100 'creates a constant that can be refered to during runtime
Code Structure
If p >= 5 then
' Statement - p is over 5
Else If p < 5 and p > 2
'Statement - p is inbetween 5 and 2
Else If p <= 2 and p > 1
'Statement - p is inbetween 2 and 1
Else If p <= 2 and p > 1
'Statement - p is less than 1
Else
'Statement all other cases
End If
p = 1 ' standard do until loop structure
Do Until p >10
p = p + 1
Loop
For x = 1 To 10 Step 2 ' standard for structure with (optional) step 2
p = x + p
Next
Select Case direction '(better use If, Else If, Else If.... Else, End If)
Case 100 to 200, "dog", 37
x = x - 1
Case Is = "Left"
x = x + 1
Case Is = "Down"
y = y - 1
Case Is = "Up"
y = y + 1
Case Else
x = x + Int(Rnd * 3) - 1
y = y + Int(Rnd * 3) - 1
End Select
Arrays
Public Grid(1 to 50, 1 to 50 ) as integer 'define array, can be n dimensional
UBound(code) ' function returns the upper size of the array UBound(array,[dimension])
for i = 0 to UBound(Arr)
'Statements
Next
ReDim Preserve array(10, 10, 15) 'allows you to chage the dimesnions of a existing array
Worksheets("info").CheckBox1.Value
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
If Worksheets("Matrix").CheckBoxes("Check Box 7") = xlOn Then 'if cost
Set w = Worksheets("info")
Worksheets("Sheet2").Cells(y, x).Value = 0
Worksheets("Sheet1").Cells(y, x).Interior.ColorIndex = xlNone 'numbers
Int(Rnd * 20) + 1 ' Returns random number between 1 and 20
On Error GoTo Error1:
'Statemetns
Error1:
me. 'references the object the code is being written in, sheets, classes etc
For Each Workbook In Worksheets 'cycles workbooks in the worksheets object collection
Next
Worksheets("TimeSeries").Cells(y, x).FormulaR1C1 = "=SUM(R[" & n & "]C:R[-1]C)"
'Syntex for inserting variables inside written statement in cell formula
Goto Lastline ' jumps to last part of programme
Lastline:
ActiveChart.SeriesCollection(1).XValues = "=ChartData!R1C3:R1C" & GraphED & ""
'inserting variable in output formula.
ActiveChart.SeriesCollection(3).XValues = "=ChartData!R1C" & GraphDD & ":R1C" & GraphED & ""
'Inserting varibles inside
SlippageValue = Replace(SlippageValue, " d", "") 'Removes letter d
Exit For ' jumps out of the loop (save time)
Exit Do
Exit Sub
'Example of looping through text field and splitting
Des = Worksheets("Main").Cells(r, RDescription).Value
MainDes = ""
SubDes = ""
Tag = False
For i = 1 To Len(Des)
c = Right(Left(Des, i), 1)
If c = "." Then
Tag = True
End If
If Tag = False Then
MainDes = MainDes + c
Else
SubDes = SubDes + c
End If
Next
Public Function IsHolorWknd(ByVal d As Date) As Boolean ' Note Function returens the id - 1 back to where it's called
'note the use of ByVal as opposed to the defail ByVal, ByRef is the variable itself (hence the 'reference' to the data, ByVal is a copy of the variable
Passing Argument by Value or by Reference
y = 2
Do Until Worksheets("Hols").Cells(y, 1).Value = Empty
IsHolorWknd = False
If Worksheets("Hols").Cells(y, 1).Value = d Then 'loops through hols sheets
IsHolorWknd = True
GoTo Found
End If
y = y + 1
Loop
Found:
End Function
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to continue ?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic, ' context. ' display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
Else ' User chose No.
MyString = "No" ' Perform some action.
End If
'Trys error
On Error Resume Next
N = 1 / 0 ' cause an error
If Err.Number <> 0 Then
N = 1
End If
Sub main()
temp = complex(1, 1)
End Sub
Function complex(a, b As Single) As Single
at = a
bt = b
For t = 1 To 30
Re1 = a * at
Im1 = a * bt
Im2 = b * at
Re2 = -b * bt
at = Re1 + Re2
bt = Im1 + Im2
Next
complex = Sqr(at ^ 2 + bt ^ 2)
End Function
Sub AttractExample()
y1 = 1
x1 = 1
y2 = 100
x2 = 100
Call AttractGen(y1, x1, y2, x2)
End Sub
'--------------------------------------------------------
'attracts co-ordinates
Function AttractGen(ByRef y1, x1, y2, x2) ' note no variable declaration
dy = y1 - y2
dx = x1 - x2
If dx + Abs(dx) = 0 Then neg = -1 Else neg = 1
d = Sqr(dx ^ 2 + dy ^ 2)
a = -(d ^ 0.5)
If dx <> 0 Then
Theta = Atn(dy / dx)
End If
ay = neg * a * Sin(Theta)
ax = neg * a * Cos(Theta)
y1 = y1 + ay
x1 = x1 + ax
y2 = y2 - ay
x2 = x2 - ax
End Function
x = y = z = 0 # Zero x, y and z
Complext Numbers
(1+2j)/(1+1j)
a.real
a.imag
abs(a) # sqrt(a.real**2 + a.imag**2), (2**2 = 4)
Affect "to influence," - "The rain affected Amy's hairdo."
Effect ”a result" - "The rain had no effect on Amy's hairdo."
“Gems that sparkle often elicit forgiveness”. The words that sparkle restrict the kind of gems you're talking about. So you it’s a that
There was an earthquake in China, which is bad news.
Can throw out the “whiches”
