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 |
Partager