Runtime Error '9' - HELP!

[Nicole.Q]

Hi All,

I'm new to the forum, this is my first post (hope in the right place)... It's my first week at a job, and already I've encountered a serious problem with a macro tied to a number of spreadsheets in an investment company. The previous programmer has left without any notes or documentation - and I'm no good at programming! I'm loosing hair. Here's the issue i'm getting with the code...

Public Sub Auto_Open()
wrkbkName = Application.ActiveWorkbook.Name

'this little snippet of code determine which branch the user is at
'based on the ip address

'if ip is like 192.168.1.xxx then we on welthsrvr
ipadrr = GetIPAddresses(True)

If Left(ipadrr, 9) = "192.168.1" Then

addin_server = "\\Welthsrvr"

ElseIf Left(ipadrr, 9) = "192.168.2" Then

addin_server = "\\Welthsrvr-h"
Else
MsgBox "not at either branch"
End If

addin_full_path = addin_server & addin_path

'I've added a direct path but still no resolution

'AddIns.Add Filename:=addin_full_path, CopyFile:=False
AddIns.Add ("\\Welthsrvr-h\INDIVIDUAL NON PROPRIETORY ACCOUNTS ON Senior Financial Analyst\Client Accounts\Client Optimizations\client_UDFs.xla"), CopyFile:=False


Pub
AddIns("client_UDFs").Installed = True

Application.Run ("client_UDFs.xla!OpenFiles")

Application.Run "client_UDFs.xla!Shares_Remaining_MarketPrice", wrkbkName
'Application.Run ("client_UDFs.xla!Summary_TimePeriod")
Application.Run ("client_UDFs.xla!Update_ProjectionSheet")
Application.Run ("client_UDFs.xla!Chk4Pref")
Application.Run ("client_UDFs.xla!ChkInflation")
Application.Run ("client_UDFs.xla!ChkIndex")
Application.Run ("client_UDFs.xla!chk6MnthROI")
Application.Run ("client_UDFs.xla!UpdateSummarySheet")
Application.Run ("client_UDFs.xla!CloseFiles")

MsgBox "Update Complete", vbInformation



End Sub

The line of code that results the error:

Sub Auto_close()
AddIns("client_UDFs").Installed = False

End Sub

Thanks for any help,

NICOLE

[Hack]

Welcome to the forums.

Error 9 is "Subscript Out Of Range" which seems to be what Excel throws at you for a wide variety of reasons (personnally, I think it gets thrown when Excel doesn't know what else to throw )

Lets start with: what is "AddIns"? Is it the name of a workbook?

[Nicole.Q]

Hi Hack,

Thanks for the swift reply and info

Addins seems to be the part of a workbook (or not) from the add in ribbon menu:

Global wrkbkName As String
Public addin_full_path As String
Public Const addin_path = "\INDIVIDUAL NON PROPRIETORY ACCOUNTS ON Senior Financial Analyst\Client Accounts\Client Optimizations\client_UDFs.xla"

Please correct me if I seem wrong.

[westconn1]

addins is the addin collection
from msdn

A collection of AddIn objects that represents all the add-ins available to Microsoft Excel, regardless of whether they’re installed. This list corresponds to the list of add-ins displayed in the Add-Ins dialog box (Tools menu).

to reference the addin by name seems to generate the error, you need to try using its title, which may be the same or different, also if the addin has previously been removed from the collection, you will also get the same error
to test if the addin is in the collection, and installed, you can run a code like this

For Each a In AddIns
    If a.Name = "test.xla" Then
    MsgBox a.Title & " installed = " & a.Installed
    End If
Next

[Nicole.Q]

thanks westconn - i will have to try this in the morning as i am not allowed to carry the files home...

i will let you know what comes of it, but thanks again!

[Nicole.Q]

I seem to have sorted out this problem - the 'client_UDFs' xla file was not installed as an addin. after installing it through the add in tool, things seem to look good...

oh but not yet...

i'm having a problem with a related spreadsheet. i get the same error except this time the debugger highlights this LOC.

Private Function GetCol4IndexSymbol(ind)
Dim last_row, col, Index
    'get last row for symbols
    last_row = Workbooks("LOCAL STOCKS.xls").Sheets("Sheet4").UsedRange.Rows.Count
    'get security from selected item
    Index = Search4Cell(ind, "Sheet4", "E", "1", last_row)
    col = Sheets("Sheet4").Range(Index).Offset(0, 1)
    GetCol4IndexSymbol = col
End Function

[Ecniv]

To slip in -> if it is the same error or type of error then its trying to reference something that is not there.

so is the work book it is using open? (LOCAL STOCKS.xls)
Is there sheet 4 in that workbook?

Can you recod the whole thing with docs to make it work better heheh

Hope you can get it sorted

[Nicole.Q]

Hi Ecniv

there is a workbook called LOCAL STOCKS, and yes it contains a sheet 4 within it... so it's seems as though i'm pulled into a circle.

Thanks for the good wishes

[Hack]

There is no path information in the call. Is LOCAL STOCKS.xls in the same folder as the excel file trying to call it?

[Nicole.Q]

no it isn't - but it has been mapped within the previous statement i.e

AddIns.Add ("\\Welthsrvr-h\INDIVIDUAL NON PROPRIETORY ACCOUNTS ON Senior Financial Analyst\Client Accounts\Client Optimizations\client_UDFs.xla"), CopyFile:=False

[westconn1]

don't need path for open workbook name within collection

you don't specify what version of excel your clients are using, is it possible that they have upgraded to 2007?

for testing purposes you can add code to loop through all open workbooks to return the name, then work with that workbook object

dim wb as workbook
for each wb in workbooks
  if wb.name = "Local Stocks" then 
    last_row = wb.Sheets("Sheet4").UsedRange.Rows.Count
  end if
next

you can do similar for the sheets, this at least can help you track the problem

[Nicole.Q]

Thanks westconn, i will try that

the employees switched from 2003 to 2007, but apparently some are still using 2003 but saving it to a 2003 format.

I've made light of some errors - some of the functions within the workbook are fine - but others with the same pathname 'Clients_UDFs.xla' don't seem to function - here's the code in that module:

 AddIns.Add ("\\Welthsrvr-h\INDIVIDUAL NON PROPRIETORY ACCOUNTS ON Senior Financial Analyst\Client Accounts\Client Optimizations\client_UDFs.xla"), CopyFile:=False
    
    

'Pub
    AddIns("client_UDFs").Installed = True
    
    Application.Run ("client_UDFs.xla!OpenFiles")


    Application.Run "client_UDFs.xla!Shares_Remaining_MarketPrice", wrkbkName
    'Application.Run ("client_UDFs.xla!Summary_TimePeriod")
    Application.Run ("client_UDFs.xla!Update_ProjectionSheet")

    Application.Run ("client_UDFs.xla!Chk4Pref")
    Application.Run ("client_UDFs.xla!ChkInflation")
    Application.Run ("client_UDFs.xla!ChkIndex")
    Application.Run ("client_UDFs.xla!chk6MnthROI")

    Application.Run ("client_UDFs.xla!UpdateSummarySheet")

    Application.Run ("client_UDFs.xla!CloseFiles")
    
    MsgBox "Update Complete", vbInformation
    
   
    
End Sub

Public Sub GAM()
    Application.Run ("client_UDFs.xla!Shares_Remaining_MarketPrice")
End Sub

Sub Auto_close()

    AddIns("client_UDFs").Installed = False
    
End Sub

Public Sub UpdateSharesRemaining_AfterPurchase()
    Application.Run ("client_UDFs.xla!UpdateSharesRemaining_AfterPurchase")
End Sub

Public Sub UpdateSharesRemaining_AfterSold()
    Application.Run ("client_UDFs.xla!UpdateSharesRemaining_AfterSold")
End Sub

Public Sub Calculate_Risk_Profile()
    Application.Run ("client_UDFs.xla!Calculate_Risk_Profile")
End Sub

Public Sub Populate_RiskProfile()
    Application.Run ("client_UDFs.xla!Populate_RiskProfile")
End Sub


Public Sub UpdateFile()

    Application.Run ("client_UDFs.xla!Shares_Remaining_MarketPrice")
    Application.Run ("client_UDFs.xla!Summary_TimePeriod")
    Application.Run ("client_UDFs.xla!Update_ProjectionSheet")
    
End Sub
Public Sub UpdateDividendSchedule()
    Application.Run ("client_UDFs.xla!UpdateDividendScedule")
    Application.Run ("client_UDFs.xla!UpdateDividendDates")
    Application.Run ("client_UDFs.xla!UpdateDividendPrices_Purchased")
    Application.Run ("client_UDFs.xla!UpdateDividendPrices_Sold")
    Application.Run ("client_UDFs.xla!UpdateTotals")
    
    MsgBox "Completed"
    
End Sub

the line highlighted in red seems to cause my problem error....

hope i'm not being too much of a headache here...

Nicole

[westconn1]

there have been several threads here that workbooks or sheets or other collectios do not always work correctly in 2007, when trying to return members by name, i have not seen any specific solutions given to this problem and as i do not have office 2007 i can not test any of the problems, and i do not understand why it can work sometimes and not others

the only solution i can offer, but don't really like is to write a function to return the index of the xla, then use the index, similar to the codes i posted above

Function addingetindex(sname As String)
Dim a As AddIn
  For i = 1 To AddIns.Count
     If AddIns(i).Name = sname Then addingetindex = i: Exit For
     ' note with addins use may need to use title instead of name
 Next
End Function
Sub aa()
x = False
x = AddIns(addingetindex("test.xla")).Installed
End Sub

you can use similar code to return the index from any collection