Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My addin function wont run twice.
Hi
Im reposting this because i still havent fixed the problem and I have new code to demonstrate I have an addin application that takes a selection and strips out all strings are not numbers, then it takes out strings which are too short of too long and then displays the removed items in one listbox and the remaining items in another listbox. The code below seems to work ok but when it gets to the line where it calls WorksheetFunction.IsText() it goes all the way back to the AddinInstance_OnConnection method of the Connect.dsr. Why would it do this? Another problem i am haveing with this code is that the first time i click the button it works ok, the second time i click the button it acts as though the selection is 'Nothing'. I dont know if it is important to know in the context of this post but the OnAction property of the button is set to the ProgID of the AddInInst passed to the AddinInstance_OnConnection method. Code is listed below. Thanks Ben Friend Sub buttSendSMS_Click(ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) On Error GoTo err_ranges Dim rngTotal As Excel.Range Dim cell As Excel.Range 'set range to selected cells Set rngTotal = oXL.Selection On Error GoTo err_listboxes 'remove strings and short/long numbers 'everything else goes is assumed to be an ok phone number For Each cell In rngTotal.Cells If WorksheetFunction.IsText(cell.Value) Then frmInvalidNumbers.lstText.AddItem cell.Value ElseIf Len(cell.Value) < 8 Or Len(cell.Value) 15 Then frmInvalidNumbers.lstText.AddItem cell.Value Else frmSendSMS.lstNumbers.AddItem cell.Value End If Next cell If frmInvalidNumbers.lstText.ListCount 0 Then frmInvalidNumbers.Show Else frmSendSMS.Show End If rngTotal.Select Exit Sub err_ranges: If Err.Number = 1004 Then Resume Next Else MsgBox Err.Number & " " & Err.Description & ": occured when setting ranges in clsAddIn.buttSendSMS_Click" Resume Next End If err_listboxes: MsgBox Err.Number & " " & Err.Description & ": occured adding when spliting valid from in-invalid numbers" Resume Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My addin function wont run twice.
Ben..
The worksheetfunction is a method of Excel's application object. when i normally code it i ALWAYS precede it with the application qualifier as for instance xl97 wont accept if without) By not specifying it in your code it may just inadvertently create another application instance.. so rewrite your line to assuming you've defined and set oXL as application object then for REAL worksheetfunctions rewrite like: oXL.worksheetfunction. HOWEVER do you really need Excel's ISTEXT function? why not use : if Not isnumeric(cell.value) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Ben Crinion" wrote: Hi Im reposting this because i still havent fixed the problem and I have new code to demonstrate I have an addin application that takes a selection and strips out all strings are not numbers, then it takes out strings which are too short of too long and then displays the removed items in one listbox and the remaining items in another listbox. The code below seems to work ok but when it gets to the line where it calls WorksheetFunction.IsText() it goes all the way back to the AddinInstance_OnConnection method of the Connect.dsr. Why would it do this? Another problem i am haveing with this code is that the first time i click the button it works ok, the second time i click the button it acts as though the selection is 'Nothing'. I dont know if it is important to know in the context of this post but the OnAction property of the button is set to the ProgID of the AddInInst passed to the AddinInstance_OnConnection method. Code is listed below. Thanks Ben Friend Sub buttSendSMS_Click(ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) On Error GoTo err_ranges Dim rngTotal As Excel.Range Dim cell As Excel.Range 'set range to selected cells Set rngTotal = oXL.Selection On Error GoTo err_listboxes 'remove strings and short/long numbers 'everything else goes is assumed to be an ok phone number For Each cell In rngTotal.Cells If WorksheetFunction.IsText(cell.Value) Then frmInvalidNumbers.lstText.AddItem cell.Value ElseIf Len(cell.Value) < 8 Or Len(cell.Value) 15 Then frmInvalidNumbers.lstText.AddItem cell.Value Else frmSendSMS.lstNumbers.AddItem cell.Value End If Next cell If frmInvalidNumbers.lstText.ListCount 0 Then frmInvalidNumbers.Show Else frmSendSMS.Show End If rngTotal.Select Exit Sub err_ranges: If Err.Number = 1004 Then Resume Next Else MsgBox Err.Number & " " & Err.Description & ": occured when setting ranges in clsAddIn.buttSendSMS_Click" Resume Next End If err_listboxes: MsgBox Err.Number & " " & Err.Description & ": occured adding when spliting valid from in-invalid numbers" Resume Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My addin function wont run twice.
Thanks
I knew there would be a VB function somewhere that did that but i found the worksheet function first. You dont know about my other problem do you? "keepITcool" wrote in message ... Ben.. The worksheetfunction is a method of Excel's application object. when i normally code it i ALWAYS precede it with the application qualifier as for instance xl97 wont accept if without) By not specifying it in your code it may just inadvertently create another application instance.. so rewrite your line to assuming you've defined and set oXL as application object then for REAL worksheetfunctions rewrite like: oXL.worksheetfunction. HOWEVER do you really need Excel's ISTEXT function? why not use : if Not isnumeric(cell.value) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Ben Crinion" wrote: Hi Im reposting this because i still havent fixed the problem and I have new code to demonstrate I have an addin application that takes a selection and strips out all strings are not numbers, then it takes out strings which are too short of too long and then displays the removed items in one listbox and the remaining items in another listbox. The code below seems to work ok but when it gets to the line where it calls WorksheetFunction.IsText() it goes all the way back to the AddinInstance_OnConnection method of the Connect.dsr. Why would it do this? Another problem i am haveing with this code is that the first time i click the button it works ok, the second time i click the button it acts as though the selection is 'Nothing'. I dont know if it is important to know in the context of this post but the OnAction property of the button is set to the ProgID of the AddInInst passed to the AddinInstance_OnConnection method. Code is listed below. Thanks Ben Friend Sub buttSendSMS_Click(ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) On Error GoTo err_ranges Dim rngTotal As Excel.Range Dim cell As Excel.Range 'set range to selected cells Set rngTotal = oXL.Selection On Error GoTo err_listboxes 'remove strings and short/long numbers 'everything else goes is assumed to be an ok phone number For Each cell In rngTotal.Cells If WorksheetFunction.IsText(cell.Value) Then frmInvalidNumbers.lstText.AddItem cell.Value ElseIf Len(cell.Value) < 8 Or Len(cell.Value) 15 Then frmInvalidNumbers.lstText.AddItem cell.Value Else frmSendSMS.lstNumbers.AddItem cell.Value End If Next cell If frmInvalidNumbers.lstText.ListCount 0 Then frmInvalidNumbers.Show Else frmSendSMS.Show End If rngTotal.Select Exit Sub err_ranges: If Err.Number = 1004 Then Resume Next Else MsgBox Err.Number & " " & Err.Description & ": occured when setting ranges in clsAddIn.buttSendSMS_Click" Resume Next End If err_listboxes: MsgBox Err.Number & " " & Err.Description & ": occured adding when spliting valid from in-invalid numbers" Resume Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My addin function wont run twice.
Actually the one fix solved both problems.
For anyone whos is interesed in the future calling the worksheetfunction without preceeding the application instance before it does create another instance of the addin. This in turn changed my oXL object and made it refer to some undefined thing, so when i accessed oXL.selection it was set to nothing. Thaks "keepITcool" wrote in message ... Ben.. The worksheetfunction is a method of Excel's application object. when i normally code it i ALWAYS precede it with the application qualifier as for instance xl97 wont accept if without) By not specifying it in your code it may just inadvertently create another application instance.. so rewrite your line to assuming you've defined and set oXL as application object then for REAL worksheetfunctions rewrite like: oXL.worksheetfunction. HOWEVER do you really need Excel's ISTEXT function? why not use : if Not isnumeric(cell.value) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Ben Crinion" wrote: Hi Im reposting this because i still havent fixed the problem and I have new code to demonstrate I have an addin application that takes a selection and strips out all strings are not numbers, then it takes out strings which are too short of too long and then displays the removed items in one listbox and the remaining items in another listbox. The code below seems to work ok but when it gets to the line where it calls WorksheetFunction.IsText() it goes all the way back to the AddinInstance_OnConnection method of the Connect.dsr. Why would it do this? Another problem i am haveing with this code is that the first time i click the button it works ok, the second time i click the button it acts as though the selection is 'Nothing'. I dont know if it is important to know in the context of this post but the OnAction property of the button is set to the ProgID of the AddInInst passed to the AddinInstance_OnConnection method. Code is listed below. Thanks Ben Friend Sub buttSendSMS_Click(ByVal Ctrl As Office.CommandBarButton, _ CancelDefault As Boolean) On Error GoTo err_ranges Dim rngTotal As Excel.Range Dim cell As Excel.Range 'set range to selected cells Set rngTotal = oXL.Selection On Error GoTo err_listboxes 'remove strings and short/long numbers 'everything else goes is assumed to be an ok phone number For Each cell In rngTotal.Cells If WorksheetFunction.IsText(cell.Value) Then frmInvalidNumbers.lstText.AddItem cell.Value ElseIf Len(cell.Value) < 8 Or Len(cell.Value) 15 Then frmInvalidNumbers.lstText.AddItem cell.Value Else frmSendSMS.lstNumbers.AddItem cell.Value End If Next cell If frmInvalidNumbers.lstText.ListCount 0 Then frmInvalidNumbers.Show Else frmSendSMS.Show End If rngTotal.Select Exit Sub err_ranges: If Err.Number = 1004 Then Resume Next Else MsgBox Err.Number & " " & Err.Description & ": occured when setting ranges in clsAddIn.buttSendSMS_Click" Resume Next End If err_listboxes: MsgBox Err.Number & " " & Err.Description & ": occured adding when spliting valid from in-invalid numbers" Resume Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An AddIn for a custom function | Excel Discussion (Misc queries) | |||
Date format from excel to CVS file wont. Change in CVS wont stay. | Excel Worksheet Functions | |||
Selection is set to Nothing on second run of a AddIn function | Excel Programming | |||
Mousewheel wont function | Excel Programming | |||
using a function in an addin in my vba code | Excel Programming |