VBA in XLA not working as expected....

Hey Guys, I am a newbie here (and to macs as well!) But I have developed an xla that seems to crash my excel (I am using excel 2004). However, if I compile first then run everything seems to be ok OR if I run the code directly from the VB editor it seems to run just fine. The SQL code is definitely right as I used it many times. I have much more complicated codes written that work well so I am sure there is an error in my code that I am overlooking and I am pulling my hair out trying to find it. Could you please take a look at the code and feel free to make any changes...... :dunno:help

[VBA]Public Sub Convert()
Application.EnableEvents = False
Application.ScreenUpdating = False

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

Range("D1").Select
Range(Selection, Selection.End(xlDown)).Select
sql_filter = SQL_WHERE(Selection)
StopCounter = Selection.Rows.Count

If StopCounter = 65536 Then

SQL1 = "xxxxxxx"
connstring = "ODBC;DSN=xxxx;UID=xxxx;pwd=xxxx;"


With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=SQL1)
.Refresh
End With

Else

SQL1 = "xxxxxxx"
connstring = "ODBC;DSN=xxxx;UID=xxxx;pwd=xxxx;"

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=SQL1)
.Refresh
End With

End If

Range("C1").Select
ActiveCell.Formula = "=VLOOKUP(D1,A:A:B:B,2,0)"

If StopCounter = 65536 Then
Else
Selection.AutoFill Destination:=Range("C1:C" & StopCounter)
Range("C1:C" & StopCounter).Select
End If

Columns("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Columns("A:B").Select
Range("B1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub[/VBA]
[uvba].[/uvba]
~Oorang

I dont know if I can be of much help, but some questions to help narrow things down:

you say the xla crashes excel; I'm assuming it is when you run this sub? Is this the only code in the xla?

the SQL_WHERE gets flagged when compiling for me, 'sub or function not defined'. I'm using 2004 as well, so I'm not sure how you are getting it to compile. That variable (sql_filter) does not appear to be used anyway, right?

FYI, you can format your VBA code in your post by highlighting it and clicking the green VBA button when posting/editing.

Good luck, tim

Welcome to the board:)
I can't really provide anything substantive to you without know what the error message is, when do you encounter it, etc. I looked through your code for obvious danger signs and didn't really see any, I refactored your code in process (posted below if you want it.)
Also when you are posting back the error message, make sure you also post the procedure: SQL_WHERE
[vba]Option Explicit

Public Sub Convert()
Dim qt As Excel.QueryTable
Dim SQL1 As String, connstring As String
Dim ws As Excel.Worksheet
Set ws = Excel.ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False
ws.Columns("A:C").Insert Shift:=xlToRight
sql_filter = SQL_WHERE(ws.Range("D1:" & ws.Range("D1").End(xlDown).Address)) 'what is this doing?
SQL1 = "xxxxxxx"
connstring = "ODBC;DSN=xxxx;UID=xxxx;pwd=xxxx;"
Set qt = ws.QueryTables.Add(Connection:=connstring, Destination:=ws.Range("A1"), Sql:=SQL1)
qt.Refresh
With Excel.Intersect(ws.UsedRange, ws.Range("C:C"))
.Formula = "=VLOOKUP(D1,A:A:B:B,2,0)"
.Value = .Value 'This is the same as copy/paste-special:values
End With
ws.Columns("A:B").Delete Shift:=xlToLeft
qt.Delete
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub[/vba]

Hey Guys, thanks for the replies. This is not the only coding I have for the xla. The xla itself adds a new option in the menu bar of excel (File, Edit, etc.). If you choose one of the listed options (there are about 9 options - some with forms and all they all seem to be tempermental), I do not receive an error message but excel shuts down completely! ( I created an xla for a pc similar to this but never had this issue!). Would "bad" vba in a different module affect this module? As for the SQL_WHERE, that just formats values in column A with single quotes and a comma so that they can be used in my sql statement (ex: 'xxxx', 'xxxx', etc.). I have posted the code below. Again, if you need more info just let me know! Thanks so much for the help!

[vba]Public Function SQL_WHERE(ByRef ViewRange As Excel.Range, _
Optional ByRef StringIdentifier As String = "'", _
Optional ByRef DateIdentifier As String = "'") As String


Dim r As Excel.Range, i As Integer
ReDim a_values(0) As Variant 'To store the values of the non_empty cells in the range
Dim sBuffer As String 'To store the text of the "in clause"
Dim bAllNumeric As Boolean
Dim sTypeChar As String

bAllNumeric = True

For Each r In ViewRange
If r.Value <> "" Then 'ignore blanks
ReDim Preserve a_values(UBound(a_values) + 1)
a_values(UBound(a_values)) = r.Value
If Not IsNumeric(r.Value) Then bAllNumeric = False
End If
Next

For i = 1 To UBound(a_values)
Select Case True
Case IsNumeric(a_values(i)): sTypeChar = ""
Case IsDate(a_values(i)): sTypeChar = DateIdentifier
Case Else: sTypeChar = StringIdentifier
End Select
sBuffer = sBuffer & sTypeChar & a_values(i) & sTypeChar
If Not i = UBound(a_values) Then sBuffer = sBuffer & ", "
Next i

SQL_WHERE = " IN (" & sBuffer & ")"

Erase a_values

End Function[/vba]

it crashes Excel, so no error message...that is frustrating! and, to be clear, it's not just this function, but any function? While posting the workbook will not help people run the procedures, it will help them see all of the code. It's a chore, I know.

Also, to be clear, are you compiling on a mac when it does not cause problems? I forget why, but I have a habit of developing and compiling an xla on a PC before using on a mac. Probably has more to do with the fact that I dislike working with the VBIDE on the mac than anything else.

If compiling keeps problems from happening, the easy way out is to just compile and save before distributing. does not solve the problem or answer the question, but from what I understand it will cease the excel crashes, right?

Oorang,

I just tried your variation of the script and it works beautifully! I am not sure what did it but it works. But I have another kicker, I just changed my xla to follow your methodology and like I said, it works beautifully... for me that is.... I tried the xla on a coworkers computer and they got an error that said "module not found". I compiled the xla on my computer then saved and tried it again on their computer to no avail! Any idea of what gives? FRUSTRATING!!! (I miss my PC) :banghead:

File corruption?
Export everything from the project, create a new xla and import it all back in.

You Guys Rock! Thanks so much! :beerchug: