VBA reference setting not working as expected on Excel 2003 on XP Pro

Microsoft OFFICE

    Sponsored Links

    Next

  • 1. Need to identify the last page in a word document.
    Hi Guys, I am new to this group and MS Word programming. I do lots of programming in MS Access and VBA. Need your expertise here. I am trying to identify last page in a word document and put a message called "LAST PAGE" on the header of the page. Have you guys got any idea? Thanks Dave
  • 2. Write values into existing Excel File with Outlook VBA: Workbook is hidden!
    Hello world, I am writing directly into an existing Excel file with Outlook VBA, using the following subroutine (example only). The routine terminates correctly. When I reopen the file manually, Excel displays it hidden! But why? There is no code to hide it in my subroutine... Can anyone help? '-------------------------------------- Sub test() Dim xlApp As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Set xlApp = New Excel.Application Set wb = GetObject("C:\Temp\t1.xls") Set ws = wb.Worksheets("myWorksheet") ws.Cells(2, 1).Formula = "TEST" wb.Close Savechanges:=True xlApp.Quit Set ws = Nothing Set wb = Nothing Set xlApp = Nothing End Sub '--------------------------------------
  • 3. Access 2003 and Outlook 2003
    Working with Access 2003 and Outlook 2003, I have a database where there are customer records. I would like to link an individual customer record with 1 or more emails that are stored in an inbox. The outlook would be connected to an Exchange database..The idea is that it would when you select a customer, you would be able to see any email correspondance that you have had with that customer...Question, how do I link the customer to an email....All help greatfully received Thanks

VBA reference setting not working as expected on Excel 2003 on XP Pro

Postby anonymous » Sun, 13 Jun 2004 13:30:17 GMT

I have found what appears to be an error in the VBA 
environment running a macro under Excel 2003 on Windows 
XP Professional. The same macro runs fine under Excel 
2000 on Windows 2000 which I think does indicate that 
this is a genuine error in the VBA environment.

I'm using the code below to check if there is a reference 
to the Solver addin. If there isn't a reference, the code 
adds the reference. This works fine the first time, but 
gives an "already defined" error if run again. I would 
expect the code to correctly determine that the reference 
already exists and exit the subroutine without attempting 
to define the already existing reference - and indeed 
this is the case on Excel 2000 on Windows 2000 - but it 
is failing on the PC running Excel 2003 and Windows XP 
Professional.

Interestingly - and I think this is relevant - the 
ActiveVBProject and VBProject are both shown as "VBA 
Project" the first time the code is run but the 
ActiveVBProject changes to "SOLVER" for subsequent calls 
to the code when the error message appears. 

So to produce the error on the PC running Excel 2003 and 
XP Pro I uncheck the reference to Solver in the VBA 
editor, quit Excel completely, start Excel by double 
clicking the spreadsheet in Explorer and click the button 
that calls the code. The code works fine the first time 
but gives the error on subsequent calls.

Does anyone know how I go about trying to get MS to fix 
this problem? I've rung local support who say that for 
$50 I can speak to an MS professional who I guess woudl 
verify what I'm saying and start the bug report process. 
Does anyone know if this process is likely to be 
fruitful? Or do MS employees monitor these newsgroups?
(And maybe I can save my $50 :))

Cheers

David


Code:

Sub Button2_Click()
  On Error GoTo ErrorHandler    ' Enable error-handling 
routine.

  MsgBox Prompt:="VBProject name is " + 
ThisWorkbook.VBProject.Name, Buttons:=vbCritical

  MsgBox Prompt:="ActiveVBProject name is " + 
Application.VBE.ActiveVBProject.Name, Buttons:=vbCritical

  i = 1
  Do Until ((i = AddIns.Count) Or _
            (StrConv(Left(AddIns(i).Name, 6), 
vbUpperCase) = "SOLVER"))
    i = i + 1
  Loop
    
  If (StrConv(Left(AddIns(i).Name, 6), vbUpperCase) 
= "SOLVER") Then
      AddIns(i).Installed = True
    j = 1
    Do Until (j = 
Application.VBE.ActiveVBProject.References.Count Or _
              (StrConv(Left
(Application.VBE.ActiveVBProject.References(j).Name, 6), 
vbUpperCase) = "SOLVER"))
             j = j + 1
         Loop
         If (StrConv(Left
(Application.VBE.ActiveVBProject.References(j).Name, 6), 
vbUpperCase) <> "SOLVER") Then
            
Application.VBE.ActiveVBProject.References.AddFromFile 
AddIns(i).FullName
         End If
    Else
        MsgBox Prompt:="Solver not found.  This workbook 
will not WORK", Buttons:=vbCritical
    End If
    MsgBox "finished"
    
      Exit Sub    ' Exit Sub to avoid error handler.

ErrorHandler:        ' Error-handling routine.
    MsgBox Prompt:="Error in Button2_Click() = " + Str
(Err.Number) + Error(Err.Number) + " Please report to 
Analytical Engines", Buttons:=vbCritical

    Resume Next    ' Resume execution at line after error

End Sub


re:VBA reference setting not working as expected on Excel 2003 on XP Pro

Postby Hmmm » Sun, 20 Jun 2004 10:42:38 GMT

i,

Your code is quite complex and you are probably using
features "borrowed from other applications or other
versions of excel.

To ensure code works for nearly allversions of excel, ie
97 plus, keep it simple.

example:

MsgBox "Hello",vbOkOnly + vbInformation,"Message from
Above"

I do not know where the reference to Buttons:=vbCritical
or MsgBox Prompt: comes from, maybe excel4 or Lotus.


Excel 2003 does not like that stuff. It cannot cope with
all the jargon of yesteryear. Keep it simple and it will
work. Also, you will have to put in the extensions of your
objects where yesteryear allows you not to have them.
Example: txtTextbox1.Text

Also with objects, use the instance name. Example
txtTextbox not textbox1, lblName not label1 etc

regards

Hmmm


Re: VBA reference setting not working as expected on Excel 2003 on XP Pro

Postby Stephen Bullen » Tue, 22 Jun 2004 18:53:22 GMT

Hi David

This isn't a bug; you're just seeing that the ActiveVBProject is the 
project in the VBIDE that was last clicked by the user, and has *no* 
relationship to the workbook active selected in the Excel window.  It 
never has and the fact that it worked in previous versions was nothing 
more than coincidence.  Perhaps in Excel 97, you only had the Solver 
addin installed, while in Excel 2003 you have more installed?

If you want the VBProject for a specific workbook, you should *always* 
use Workbook.VBProject (or ThisWorkbook.VBProject) and not rely on the 
VBE's ActiveVBProject property.

FWIW, the ActiveVBProject is very useful when writing addins for the 
VBE, giving you the VB project that the user is currently working on, 
which is often different to Excel's ActiveWorkbook.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie



Similar Threads:

1.Excel 2003 VB referencing #N/A not working but works in Excel 2007

I have a very simple macro that works in Excel 2007 but hangs up constantly 
in Excel 2003.  Does anyone have a work around?
Problem:
When referencing cells that have a #N/A value, Excel 2003 craps out even if 
I use the ISNA application function before it. The following 2 statements 
will cause Excel 2003 VB to stop regardless of order if the cell that is 
referenced comes up with #N/A.
            Series = Cells(RowNumStart + X - 1, SeriesCol).Value
            If WorksheetFunction.IsNA(Cells(RowNumStart + X - 1, ColNum)) Then

Further background.... I am actually wanting to find #N/A values to turn 
"off" the data labels in a stacked bar chart if they are #N/A.  In Excel 
2007, this is not even required because it won't put #N/A values on the 
chart, but Excel 2003 will continue to show them on the chart as 0 value 
which clutters up the chart.  My macro just checks the value and if a value 
turns on the data label and if #N/A turns off the data label.  Again, macro 
works perfectly in Excel 2007 but I need to give to users who still have 
Excel 2003.  

Thanks for help

2.INDIRECT not working as expected - referencing multiple dynamic ranges

Hi all

I have some dynamic ranges that are subsets of a master dynamic
ranges.  The criteria for the start/end of each subset range are in
A1:A4

dynMaster = OFFSET($A$40, 0, 0, NumRows)
dynSub1 = INDEX(dynMaster, MATCH($A$1, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$2, dynMaster, 0)-1)
dynSub2 = INDEX(dynMaster, MATCH($A$2, dynMaster, 0)):INDEX(dynMaster,
MATCH($A$3, dynMaster, 0)-1)
etc

These work fine.  But now I want to be able to use a formula that
refers to the different subset data sources according to which column
the formula is used in.

DataSource = INDIRECT("dynSub" & COLUMN())

This doesn't work at all and I'm getting a REF error.  I've noticed,
however, that if I hardcode the reference, ie INDIRECT("dynSub1"), it
works only when it's in the same row as dynSub1, but not where I need
to use the formula.  Any suggestions are greatly appreciated.

TIA

Paul Martin
Melbourne, Australia

3.Setting Focus not working as expected

I am verifiying data by VBA. When it traps an error, I am trying to go back
to the control that caused the error. It's not working for me.

Code:
        Select Case intLoan_Spread 'as entered in user form
            Case Is > intMaxLoan_Spread 'max is 1000
                msgstring3 = "Maximum loan spread is " & intMaxLoan_Spread &
". " & msgstring3
                MsgBox (msgstring3)
                Me.TXBBasisPoints = intMaxLoan_Spread
                Cancel = True
                Me.TXBBasisPoints.SetFocus
            Case Is < intMinLoan_Spread 'min is 100
                msgstring3 = "Minimum loan spread is " & intMinLoan_Spread &
". " & msgstring3
                MsgBox (msgstring3)
                Me.TXBBasisPoints = intMinLoan_Spread
                Cancel = True
                Me.TXBBasisPoints.SetFocus
            Case Else
                'passes validation


4.Visual basic 6.0 app not working correctly on win xp pro machi

 Do you know where there is an example out there that uses parameters so I 
can change this.  From what I understand that is suppose to be the better way 
to do this.  

"Darrell Wesley" wrote:

> Since you have several error traps in your code I would assume that you are 
> getting some type of error message. If its a timeout error you may have to 
> set timeout paraters on either the Connection object or the Recordset object.
> 
> If it's at the recordset level you will have to convert that portion of the 
> code to use a adoCommand object so that you can set the timeout. (The 
> Recordset object does not have a timeout parameter).
> 
> 
> 
> "mshytech" wrote:
> 
> > I created an app that pulls data from a sql server 2000 machine. If this 
> > program is open for a while and not active on a xp machine and then it tries 
> > to pull new data it can't connect. Below is one of the data access portion of 
> > the app. Have to restart the machine for it to work
> > 
> > 
> > Option Explicit
> > Private Function GetConnection(strserver As String) As String
> >     On Error GoTo ErrHandler
> >     GetConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist 
> > Security Info=false;" & _
> >                                 "Initial Catalog=hytech;Data Source=" & 
> > strserver
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > Public Function ListAll(iLocIndex As Integer) As ADODB.Recordset
> >     Dim oADOConnection As ADODB.Connection
> >     Dim oADORecordset As ADODB.Recordset
> >     Dim sSQL As String
> >     On Error GoTo ErrHandler
> >     sSQL = "ATGListAll " & iLocIndex
> >     Set oADOConnection = New ADODB.Connection
> >     With oADOConnection
> >         .ConnectionString = GetConnection(g_strServer)
> >         .Open
> >     End With
> >     Set oADORecordset = New ADODB.Recordset
> >     With oADORecordset
> >         .ActiveConnection = oADOConnection
> >         .Source = sSQL
> >         .CursorType = adOpenKeyset
> >         .CursorLocation = adUseClient
> >         .LockType = adLockBatchOptimistic
> >         .Open
> >     End With
> >     Set ListAll = oADORecordset
> >     Set oADORecordset = Nothing
> >     Set oADOConnection = Nothing
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > Public Function List(iLocIndex As Integer) As ADODB.Recordset
> >     Dim oADOConnection As ADODB.Connection
> >     Dim oADORecordset As ADODB.Recordset
> >     Dim sSQL As String
> >     On Error GoTo ErrHandler
> >     sSQL = "ATGList " & iLocIndex
> >     Set oADOConnection = New ADODB.Connection
> >     With oADOConnection
> >         .ConnectionString = GetConnection(g_strServer)
> >         .Open
> >     End With
> >     Set oADORecordset = New ADODB.Recordset
> >     With oADORecordset
> >         .ActiveConnection = oADOConnection
> >         .Source = sSQL
> >         .CursorType = adOpenKeyset
> >         .CursorLocation = adUseClient
> >         .LockType = adLockBatchOptimistic
> >         .Open
> >     End With
> >     Set List = oADORecordset
> >     Set oADORecordset = Nothing
> >     Set oADOConnection = Nothing
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > Public Function Delete(intATGIndex As Integer)
> >     Dim oADOConnection As ADODB.Connection
> >     Dim oADORecordset As ADODB.Recordset
> >     Dim sSQL As String
> >     On Error GoTo ErrHandler
> >     sSQL = "delATG " & intATGIndex
> >     Set oADOConnection = New ADODB.Connection
> >     With oADOConnection
> >         .CursorLocation = adUseClient
> >         .ConnectionString = GetConnection(g_strServer)
> >         .Open
> >     End With
> >     Set oADORecordset = New ADODB.Recordset
> >     With oADORecordset
> >         .CursorLocation = adUseClient
> >         .Open sSQL, oADOConnection, adOpenKeyset, adLockBatchOptimistic, 
> > adCmdText
> >     End With
> >     Set oADORecordset = Nothing
> >     Set oADOConnection = Nothing
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > Public Function load(iATGIndex As Integer, bExpanded As Boolean) As 
> > ADODB.Recordset
> >     Dim oADOConnection As ADODB.Connection
> >     Dim oADORecordset As ADODB.Recordset
> >     Dim sSQL As String
> >     On Error GoTo ErrHandler
> >     If Not bExpanded Then
> >         sSQL = "loadATG " & iATGIndex
> >     Else
> >         sSQL = "loadATGExp " & iATGIndex
> >     End If
> >     Set oADOConnection = New ADODB.Connection
> >     With oADOConnection
> >         .ConnectionString = GetConnection(g_strServer)
> >         .Open
> >     End With
> >     Set oADORecordset = New ADODB.Recordset
> >     With oADORecordset
> >         .ActiveConnection = oADOConnection
> >         .Source = sSQL
> >         .CursorType = adOpenKeyset
> >         .CursorLocation = adUseClient
> >         .LockType = adLockBatchOptimistic
> >         .Open
> >     End With
> >     Set load = oADORecordset
> >     Set oADORecordset = Nothing
> >     Set oADOConnection = Nothing
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > Public Function save(oADORecordset As ADODB.Recordset) As Variant
> >     On Error GoTo ErrHandler
> >     oADORecordset.UpdateBatch
> >     save oADORecordset!ATGIndex
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > Public Function CheckWarranty(iLocIndex As Integer, dToday As Date) As Boolean
> >     Dim oADOConnection As ADODB.Connection
> >     Dim oADORecordset As ADODB.Recordset
> >     Dim sSQL As String
> >     Dim bCheckWarranty As Boolean
> >     Dim dtToday As Date
> >     dtToday = Format(dToday, "MM/dd/yyyy")
> >     
> >     On Error GoTo ErrHandler
> >     sSQL = "CheckATGWarr " & iLocIndex & ",'" & dtToday & "'"
> >     Set oADOConnection = New ADODB.Connection
> >     With oADOConnection
> >         .ConnectionString = GetConnection(g_strServer)
> >         .Open
> >     End With
> >     Set oADORecordset = New ADODB.Recordset
> >     With oADORecordset
> >         .ActiveConnection = oADOConnection
> >         .Source = sSQL
> >         .CursorType = adOpenKeyset
> >         .CursorLocation = adUseClient
> >         .LockType = adLockBatchOptimistic
> >         .Open
> >     End With
> >     If oADORecordset.RecordCount = 0 Then
> >         CheckWarranty = False
> >     Else
> >         CheckWarranty = True
> >     End If
> >     Set oADORecordset = Nothing
> >     Set oADOConnection = Nothing
> >     Exit Function
> > ErrHandler:
> >     ErrorLog Err.Number & Err.Description
> >     Resume Next
> > End Function
> > 
> > Any help would be appreciated.
> > 
> > 
> > 
> > -- 
> > mshytech

5.Visual basic 6.0 app not working correctly on win xp pro machine

I created an app that pulls data from a sql server 2000 machine. If this 
program is open for a while and not active on a xp machine and then it tries 
to pull new data it can't connect. Below is one of the data access portion of 
the app. Have to restart the machine for it to work


Option Explicit
Private Function GetConnection(strserver As String) As String
    On Error GoTo ErrHandler
    GetConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist 
Security Info=false;" & _
                                "Initial Catalog=hytech;Data Source=" & 
strserver
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function
Public Function ListAll(iLocIndex As Integer) As ADODB.Recordset
    Dim oADOConnection As ADODB.Connection
    Dim oADORecordset As ADODB.Recordset
    Dim sSQL As String
    On Error GoTo ErrHandler
    sSQL = "ATGListAll " & iLocIndex
    Set oADOConnection = New ADODB.Connection
    With oADOConnection
        .ConnectionString = GetConnection(g_strServer)
        .Open
    End With
    Set oADORecordset = New ADODB.Recordset
    With oADORecordset
        .ActiveConnection = oADOConnection
        .Source = sSQL
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open
    End With
    Set ListAll = oADORecordset
    Set oADORecordset = Nothing
    Set oADOConnection = Nothing
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function
Public Function List(iLocIndex As Integer) As ADODB.Recordset
    Dim oADOConnection As ADODB.Connection
    Dim oADORecordset As ADODB.Recordset
    Dim sSQL As String
    On Error GoTo ErrHandler
    sSQL = "ATGList " & iLocIndex
    Set oADOConnection = New ADODB.Connection
    With oADOConnection
        .ConnectionString = GetConnection(g_strServer)
        .Open
    End With
    Set oADORecordset = New ADODB.Recordset
    With oADORecordset
        .ActiveConnection = oADOConnection
        .Source = sSQL
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open
    End With
    Set List = oADORecordset
    Set oADORecordset = Nothing
    Set oADOConnection = Nothing
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function
Public Function Delete(intATGIndex As Integer)
    Dim oADOConnection As ADODB.Connection
    Dim oADORecordset As ADODB.Recordset
    Dim sSQL As String
    On Error GoTo ErrHandler
    sSQL = "delATG " & intATGIndex
    Set oADOConnection = New ADODB.Connection
    With oADOConnection
        .CursorLocation = adUseClient
        .ConnectionString = GetConnection(g_strServer)
        .Open
    End With
    Set oADORecordset = New ADODB.Recordset
    With oADORecordset
        .CursorLocation = adUseClient
        .Open sSQL, oADOConnection, adOpenKeyset, adLockBatchOptimistic, 
adCmdText
    End With
    Set oADORecordset = Nothing
    Set oADOConnection = Nothing
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function
Public Function load(iATGIndex As Integer, bExpanded As Boolean) As 
ADODB.Recordset
    Dim oADOConnection As ADODB.Connection
    Dim oADORecordset As ADODB.Recordset
    Dim sSQL As String
    On Error GoTo ErrHandler
    If Not bExpanded Then
        sSQL = "loadATG " & iATGIndex
    Else
        sSQL = "loadATGExp " & iATGIndex
    End If
    Set oADOConnection = New ADODB.Connection
    With oADOConnection
        .ConnectionString = GetConnection(g_strServer)
        .Open
    End With
    Set oADORecordset = New ADODB.Recordset
    With oADORecordset
        .ActiveConnection = oADOConnection
        .Source = sSQL
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open
    End With
    Set load = oADORecordset
    Set oADORecordset = Nothing
    Set oADOConnection = Nothing
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function
Public Function save(oADORecordset As ADODB.Recordset) As Variant
    On Error GoTo ErrHandler
    oADORecordset.UpdateBatch
    save oADORecordset!ATGIndex
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function
Public Function CheckWarranty(iLocIndex As Integer, dToday As Date) As Boolean
    Dim oADOConnection As ADODB.Connection
    Dim oADORecordset As ADODB.Recordset
    Dim sSQL As String
    Dim bCheckWarranty As Boolean
    Dim dtToday As Date
    dtToday = Format(dToday, "MM/dd/yyyy")
    
    On Error GoTo ErrHandler
    sSQL = "CheckATGWarr " & iLocIndex & ",'" & dtToday & "'"
    Set oADOConnection = New ADODB.Connection
    With oADOConnection
        .ConnectionString = GetConnection(g_strServer)
        .Open
    End With
    Set oADORecordset = New ADODB.Recordset
    With oADORecordset
        .ActiveConnection = oADOConnection
        .Source = sSQL
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open
    End With
    If oADORecordset.RecordCount = 0 Then
        CheckWarranty = False
    Else
        CheckWarranty = True
    End If
    Set oADORecordset = Nothing
    Set oADOConnection = Nothing
    Exit Function
ErrHandler:
    ErrorLog Err.Number & Err.Description
    Resume Next
End Function

Any help would be appreciated.



-- 
mshytech

6. excel 2003 vba date function not working on all pc's

7. Excel 2003 VBA Help not working when automating via VB 6

8. Excel 2003/7 VBA (Un) Protection not working



Return to Microsoft OFFICE

 

Who is online

Users browsing this forum: No registered users and 70 guest