Consulting

Results 1 to 8 of 8

Thread: VBA in XLA not working as expected....

  1. #1
    VBAX Regular
    Joined
    Mar 2009
    Location
    PA
    Posts
    6
    Location

    Question 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......

    [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]
    FYI - if you enclose your code in [vba][/vba] tags then it will be formatted as per the VBIDE to improve readability.
    ~Oorang

  2. #2
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    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

  3. #3
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    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]
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  4. #4
    VBAX Regular
    Joined
    Mar 2009
    Location
    PA
    Posts
    6
    Location
    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]

  5. #5
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    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?

  6. #6
    VBAX Regular
    Joined
    Mar 2009
    Location
    PA
    Posts
    6
    Location
    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)

  7. #7
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    File corruption?
    Export everything from the project, create a new xla and import it all back in.
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  8. #8
    VBAX Regular
    Joined
    Mar 2009
    Location
    PA
    Posts
    6
    Location
    You Guys Rock! Thanks so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •