1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
Public Sub TestBloombergExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ExcelLink = New Excel.Application
ExcelLink = New Excel.Application
ExcelFileName = "C:\PMS\FunctionLoader.xls"
ExcelSheet = "LoaderSheet"
ExcelLink.Visible = True
'ExcelLink.Visible = False
ExcelWorkBook = ExcelLink.Workbooks.Open(ExcelFileName)
ExcelWorkBook.Sheets(ExcelSheet).activate()
End Sub
Public Sub BtnExec_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExec.Click
Dim Fx_value As String
Dim Result As VariantType
Dim ResultNumeric As Double
Dim ResulText As String
Dim ResultError As Boolean
Dim ExcelSymbolError As String
Fx_value = CType(Formula_text.Text, String)
ResultNumeric = 0
ResulText = ""
Result_Text.Text = ""
ResultError = False
ExcelSymbolError = ""
Fx_value = FormulaAddLineNumber(Fx_value, 1, True)
With ExcelLink
.Range("A1:Z200").ClearContents()
.Cells(1, 1).value = "A"
.Cells(1, 2).value = "B"
.Cells(1, 3).value = "C"
.Cells(1, 4).value = "D"
.Cells(1, 5).value = "E"
.Cells(1, 6).value = "F"
.Cells(1, 7).value = "Formula"
.Cells(2, 1).value = ColA_text.Text
.Cells(2, 2).value = ColB_text.Text
.Cells(2, 3).value = ColC_text.Text
.Cells(2, 4).value = ColD_text.Text
.Cells(2, 5).value = ColE_text.Text
.Cells(2, 6).value = ColF_text.Text
.Cells(2, 7).formula = Fx_value
If .iserror(.Cells(2, 7)) Then
ResultError = True
Else
ResultError = False
End If
Result = .Cells(2, 7).value
If IsNumeric(Result) Then
ResultNumeric = .Cells(2, 7).value
Else
ResulText = .Cells(2, 7).value
End If
End With
If ResultError Then
MsgBox("Your Excel formula contains errors !")
ExcelSymbolError = "Unknow"
If Result = -2146826281 Then ExcelSymbolError = "#DIV/0!"
If Result = -2146826273 Then ExcelSymbolError = "#VALUE!"
If Result = -2146826246 Then ExcelSymbolError = "#N/A"
If Result = -2146826259 Then ExcelSymbolError = "#NAME?"
Check_Text.Text = ExcelSymbolError
Else
Check_Text.Text = ""
If IsNumeric(Result) Then
Result_Text.TextAlign = HorizontalAlignment.Right
Result_Text.Text = ResultNumeric
Else
Result_Text.TextAlign = HorizontalAlignment.Left
Result_Text.Text = ResulText
End If
End If
ExcelLink.ActiveWorkbook.Save()
End Sub
Private Sub Formula_text_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Formula_text.Leave
If Mid(Formula_text.Text, 1, 1) <> "=" Then
MsgBox("First character of a formula must be '=' !")
Formula_text.Select()
End If
End Sub
Public Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnClose.Click
ExcelLink.Workbooks.Close()
System.Threading.Thread.Sleep(1)
ExcelLink.Quit()
Close()
End Sub |
