Calling Function From Add-in Causes Crash
May 19, 2009
I have an add-in that has one function in it. We plan on adding more, but not until we figure out what's happening. The one function is a Select- Case statement...send the function an alpha code and it returns a numeric account number based on the alpha. About 20 "cases" in total. The add-in loads fine and the function works fine from a spreadsheet cell. However, we're getting differing results when we try to access the function from VBA code. I've been able to duplicate this several times on three different computers on our network.
The VBA code will work fine, several times and then on the 4th or 5th attempt it will cause Excel to crash ==> "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience." If I " recover" the crashed file, comment out the line to call the function, run the program again it works. Then I can un-comment the line, re-run the program and it works fine again for 4 or 5 more attempts. I've tried running the Repair tool already. I've also copied the code out of the spreadsheet and started a new file. The same errors occur...works 4 or 5 times and then crashes the program. The add-in and the spreadsheet exists on networked drives.
View 3 Replies
ADVERTISEMENT
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
May 1, 2007
I guess I just don't understand the basics of calling a function. I have the following function in a standard module:
Function CleanUp(ws As Worksheets)
ws. Range("A2:P100").ClearContents
End Function
I have this code in a worksheet module:
Sub Trying_to_call_a_function()
CleanUp (ThisWorkbook.Worksheets("Month End"))
End Sub
But when I run the routine that calls the "CleanUp" function, I get the message: "Object doesn't support this property or method." I have tried changing the Function to this: CleanUp (ws as Worksheet) [instead of (ws as WorksheetS)]. Yet, this does not change anything.
View 3 Replies
View Related
Feb 2, 2010
I need to extract the filename “My Excel File” from Worksheets(“Sheet1”).Range(“A1”), whose value = C:Documents and Settingsuser1DesktopMy Excel File
I found on Chip Pearson’s site a Function TrimToChar which, using SearchFromRight and a TrimChar of “” will trim OFF the filename “My Excel File”, leaving the Path.
But, I think I could use his function to tell me the number of characters in the Path and then use that to extract the remaining characters from the total character length (Mid Function)
Problem is, I don’t have a clue how to call a Function in VBA to work on Worksheets(“Sheet1”).Range(“A1”). How do you set the InputText , TrimChar, & SearchFromRight?????
(eventually, this will be in a loop, where I extract the filename from a Dynamic Named Range (list) in Column A)
View 3 Replies
View Related
Sep 9, 2009
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.
Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)
CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography
End Function
View 9 Replies
View Related
Sep 26, 2007
I have a User Defined Function (for finding the next minimum value in a range) that is not working for nonconcurrent ranges when called from within a worksheet. It returns #VALUE!
This works: =nextmin(A11:A17)
This does not work: =nextmin(D13,E13,F13,F16)
However calling the function from within vba works:
View 9 Replies
View Related
Jul 23, 2014
how to properly write this kind of commands!
v_1 = Sheets("WB_Input").Cells(i, 2) 'It's gonna be a numeric value like 10
v_2 = "E"
v_3 = Sheets("WB_Input").Cells(i, j) 'It's gonna be a numeric value like 12
Sheets("WB_Output").Cells(i, j) = "=CONCATENATE(" & v_1 & "," & v_2 & "," & v_3 & ")"
Thing is once I look at the WB_Output Sheet what I see is an error and the cell shows this: =Concatenate(10,E,12)
So logically the problem is that I need to make it show up as =Concatenate("10","E","12")
Btw, I can't just put v_1 & v_2 & v_3, cause Excel understands "E" as exponential!
View 1 Replies
View Related
Jun 9, 2014
the macro works fine until it executes the paste values. At that point, the macro jumps to the "CountThem" function which is located in another workbook. The data that I am copy/pasting is in no way connected to any cells that are using that function. Although, other values in the workbook are passed down from data that uses that function.
I am still in the dark ages using Excel 2000.
This is the code for my macro.
Code:
Sub Current_to_Raw()
'
' Current_to_Raw Macro
' Macro recorded 2/12/2014 by
'
'
Range("N14").Select
[code]....
View 2 Replies
View Related
Apr 30, 2007
Is it possible to use the Worksheet_Change command to monitor 2 different cells in the same worksheet and call a different procedure depending on which cell is changed. i.e. if cell A1 is changed do this, if cell a2 is changed do that
View 9 Replies
View Related
Oct 9, 2007
I have a file with about 30 sheets and a size of 14 meg. When the file is saved by using the save button it seems to save. When the file is closed the Microsoft crash message appears. The changes seem to save and if you choose not to have Excel attempt to repair the file everything seems fine.
The other odd behavior the file has that is if you run a for loop on any tab it takes about 3-4 minutes to run. If you copy the tab to another book and run the same code it runs in 3-4 seconds. Any ideas?
View 6 Replies
View Related
Nov 16, 2009
I have a multi-tabbed spreadsheet that keeps crashing when I try to set up a reference from one tab to another tab in the same spreadsheet (e.g., =Sheet2!B1) . The spreadsheet does NOT have any macros in it although my Personal.xls DOES have numerous macros that I have been using for over 6 years with no problems. In any ase, when I start Excel without loading personal.xls, so that NO macros are loaded, the spreadsheet still causes Excel to crash when I try to set up the reference to another tab.
View 9 Replies
View Related
Apr 14, 2014
The situation is a Workbook_Activate macro is conflicting with a worksheet module which is a worksheet_calculate that also runs a macro when there is a query refresh from another book. How do I get the workbook activate macro to run before the worksheet one activates? Right now everything crashes.
View 1 Replies
View Related
Mar 11, 2009
This file contains the printing options of a larger userform. But I am having problems with the print preview crashing.
I'd click one of the printing options on the userform, this would bring up the print preview of that sheet.
And when I try to "test" its stability by going crazy on the view next/ previous page on preview, it'd crash. This is especially when I've got the screenupdating set to false.
Just wondering if anyone else can varify the crash/ suggest what maybe its cause?
View 9 Replies
View Related
Jun 3, 2012
I have problems with my excel crashing all the time when I try to run my code. It seems to happen in a loop where I open a .txt file and pull data from it, then close it, and open the next one...
There are no error code, just a message that Excel has stopped working and it will be closed, and then it is forced to restart. Just a mess!
This is my loop code:
Code:
Option Explicit
Dim Desktop As String
Sub Open60only()
Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
Dim which_folder As String, which_file As String, which_string As String, tick As String
Dim lastrow As Long
Dim wb1 As Workbook, wb2 As Workbook
[code].....
View 9 Replies
View Related
Apr 15, 2014
I have a piece of code in one of my Macro's, if I run the macro and don't let this piece of code run everything works fine, when I let this piece of code execute the excel file keeps running until I go to save, at which point I get a message saying "Microsoft office excel has encountered a problem and needs to close. We are sorry for any inconvenience".
Code is as follows:
If MsgBox("Copy and hardcode this scenario?", vbYesNo) = vbYes Then
Calculate
Application.DisplayAlerts = False
[Code]....
View 3 Replies
View Related
Feb 7, 2007
what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.
The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.
Here's the formula I am using.
=(SUMPRODUCT(($I$2:$I$65536>=$D$2)*($I$2:$I$65536
View 9 Replies
View Related
Nov 24, 2008
I have an add-in procedure that creates a new workbook and adds code to the new workbook's ThisWorkbook object (a Workbook_Open event handler). However, the .AddFromString and .InsertLines methods are causing the accursed "Excel has encountered a problem and needs to close" error. I've stripped the code down to rule out possible syntax errors in the generated code, and it still crashes.
If Not wbTemplate Is Nothing Then
Set objVBProj = wbTemplate.VBProject
If Not objVBProj Is Nothing Then
strCodeText = "Private Sub Workbook_Open()" & vbCr
strCodeText = strCodeText & "MsgBox " & Chr(34) & "test" & Chr(34) & vbCr
strCodeText = strCodeText & "End Sub ' Workbook_Open"
With objVBProj.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString strCodeText
End With
End If ' access vbproject
End If ' workbook exists
Set objVBProj = Nothing
View 9 Replies
View Related
Jan 8, 2007
I'm working with Excel 2000 on Windows XP machines. I have the following
Private Sub soInputBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = vbKeyReturn Then
PushScreenUpdateState False
clearAllData
loadAllData
Worksheets("Main Screen").Activate
PopScreenUpdateState
End If
End Sub
Private Sub loadAllData()
soInputBox.Enabled = False
End Sub
The idea is this: the user can either type the value into the text box (soInputBox) and then click a button, or type the value into the box and then hit enter. They will both do the same thing; set in motion all the functions that load a bunch of data, etc.The code for the event handler for hitting enter is shown above. Inside of the loadAllData function, I disable the text box. What seems to be happening is that after the soInputBox_KeyDown function exits, Excel crashes. If I comment out the line to disable the text box, Excel doesn't crash. My question is this: is this a known problem, and is there a known workaround? I would like it so that the text box is disabled so that the value that was input cannot be changed accidentally during the rest of the programs execution, so if there's a way to change the control to be unmodifiable instead of disabling it that would be a reasonable alternative.
View 4 Replies
View Related
May 13, 2007
Examples:
This is the userform with the 'No Answer' boxes added into the user interface but not added to the code - this one doesn't crash excel: [url]
and this is the userform with the 'No Answer' boxes added into the code - this one will crash excel (but only when you open it directly from excel, it doesn't crash it if you test it out from vb): [url]
View 5 Replies
View Related
Jun 19, 2007
I am having a strange error occur. I am trying to debug a function in an add-in I developed, but whenever I try to hover over a value or add a watch, the whole application crashes. It runs without crashing when I don't try to debug it.
View 3 Replies
View Related
Feb 4, 2008
I have a fairly big excel spreadsheet with about 2 userforms at the moment. One Userform (the main one) has a multipage control with about 12 pages. Now here is the weird part. I open up the excel document and this main user form loads up simply with this code
Sub Auto_Open()
Load Userform1
Userform1.show
End Sub
This works fine the form loads. Now on this form there is a multipage control with about 12 pages as mentioned above. Now everything works the way I want it to eg the text boxes, combo boxes, command buttons etc all work and link with the worksheets fine just the way they should...But if I reference or try to use a couple of the controls in any way excel crashes. It doesn't give any errors or anything it just crashes and then I get a prompt saying do you want to send the error report blah blah. I have narrowed it down to 3 command buttons and 1 combo box so far that this happens on. If I do the most basic of commands on any of these controls such as something as simple as msgbox problemcombobox.visible...
View 2 Replies
View Related
Apr 9, 2008
I have having alot of frustation with a big group of Macro's I have written,
all the macros work and run fine most of the time but they crash Excel on occasion and the only way I have been able to fix it is to go into Visual Basic and opening the project up (it is password protected) but not making any changes, after doing this it seems to magically stop crashing, for a while, then doing it again.
I can't work out how or why that would make a difference,
I dont expect you to try and work out a problem in the code or anything like that cause it is too big and would be unreasonable of me to ask,
What I would like to know is if anyone else is aware of this sort of issue, why it happens and if there is any good cure for it?
I am running excel 2003 if that also makes any difference.
View 5 Replies
View Related
Feb 22, 2014
Have a code to clear contents on one of my worksheets and it continually causes excel to freeze and forces me to exit the program. When the button is selected it takes approx. 30-45 seconds to clear the data and then excel freezes up. The code is as follows:
Sub()
Sheets("Sheet1").Range("B2:D16000").ClearContents
End Sub
The data in the range B2:D16000 comes from another macro that copies and pastes values to this sheet. I am also working on the mac 2011 version.
View 5 Replies
View Related
Feb 10, 2006
I'm just wondering if any of you can lend some help on a bug i'm encountering. When I press a button on my worksheet to call a macro that brings up a userform, the excel will crash, and give that "do you want to report this error", close down excel, and reopen on a blank worksheet. Strange thing is, this error is avoided if I save the workbook upon opening it, and then call that macro.
The workbook does not read data from external sources, so i dont think is that problem.
Has anyone encountered this?
View 9 Replies
View Related
Jun 29, 2007
I'm in the process of writing/editing a macro, but after I run the macro once, the Expressions in the Locals Watch window disappear, and if I try to run it again it produces an "Excel has encountered an error and needs to close". The macro probably doesn't make much sense out-of-context, but perhaps there's a glaring error that's causing this. A guess - possibly related to the Error Handling in the vba? (1st time I've used this in a macro).
Option Explicit
Sub Compare()
Dim strNaspK As String
Dim strCtryK As String
Dim intCtryRev As Integer
Do Until ActiveCell. Offset(1, 0) = ""
ActiveCell.Offset(1, 0).Select
strNaspK = ActiveCell
strCtryK = ActiveCell.Offset(0, 2)
On Error Goto ErrJump:........................
Basically, I've got two sets of data on different sheets, with NASP ID and country being variables. Just trying to import the data from the 'Comparison' sheet, if both the NASP id and country matches that on the first sheet, established by the variables.
View 2 Replies
View Related
Jul 10, 2012
I am trying to sort a bunch of data by a custom list from left to right and after running the macro successfully excel crashes upon saving. I have tried this on different computers, resaved, started from scratch and it always crashes so there must be something with the macro that is causing this.
here is my macro:
Edit: It seems that the last line of code is causing my problem. I have started from scratch and gone though all the code one at a time and saved after each run. everything is fine until the "Application.DeleteCustomList Application.CustomListCount" is run. after that excel crashes when saved.
VB:
Sub Macro3()
'
' Macro3 Macro
'
[Code]....
View 1 Replies
View Related
Jul 14, 2014
I am receiving the following error:
-2147417848 automation error the object invoked has disconnected from its clients
Unfortunately, I can't tell where the error is occurring because everytime it happens excel crashes or at least stops responding. I tried waiting it out (about 20 minutes) but no luck. the only thing I can do is force the program to shut down through the task manager.
The program is designed to pull information from finviz.com (a financial site), filter the data. pull the next round of data and run the filters again. there are a total of 8 queries and i think it gets hung up during the 6th or 7th run through.
Stepping through the code does not seem practical since each query might pull as many as 300 rows of data that is evaluated in a for/next loop (thats a lot of F8s).
View 5 Replies
View Related
Aug 23, 2009
activesheet.printpreview , Excel crashes if there are no printers installed on the machine. How can i check for printers before calling the printpreview, or else stop the program from crashing?
View 2 Replies
View Related
Mar 13, 2012
I have been using Erik's fantastic "Force Enable Macros" code (exactly as it appears)...
force enable macros & ASK to save changes (SOLUTION)
...with the desired results (and comfort) under Excel 2003.
The code works under Excel 2007. However, if there is another workbook open at the sametime (regardless of whether Workbook two has macros or not), I get an Excel "crash";
Code:
Microsoft Office Excel has encountered a problem and needs to close.
[ ] Recover my work and restart Microsoft Excel There is also a Event ID 7001 in the Event Viewer. I have run Excel diagnostics and there are no issues.
View 8 Replies
View Related
Aug 21, 2008
I am having problems with an Excel workbook I am working on. I have a sheet that has a number of controls added to the top of it. Two of these controls are listboxes. My problem is, if I am in the listbox navigating through it, and I accidently move the Mouse Wheel, Excel crashes and I get the usual, "Send Error Report" form.
I have seen that there are other people having this problem and that there are a number of solutions out there but my problem is a bit different. I have a few forms with listboxes on them, and while mouse wheel scrolling doesn't work, it certainly doesn't crash Excel. It only seems to happen when I have a control permanently added to the sheet. Is there any event handler for mouse wheel scrolls or anyway to disable them through VB?
View 2 Replies
View Related
