Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 3rd, 2006, 06:30 PM
Registered User
 
Join Date: Nov 2006
Location: , , Belgium.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 2 problems: reference to scrrun.dll / worksheets

Hi all,

I am developing an Excel VBA add-in, but I need some expert help.

I'll describe what my add-in is supposed to do:

It is basicly an interpolation program, that recalculates numerical graph data.

1) import data from an external text file, this file is read using a Textstream from a FileSystemObject.
2) Add new sheets to make interpolations from the imported data.

At home, everything is working fine, but on other computers the import function does not work. VBA complains about not finding a library for the FileSystemObject. When I try to add a reference to Microsoft Scripting Library (scrrun.dll) in the VBE (using tools>references), the reference disappears after closing and re-opening Excel. Btw, adding the reference and not closing Excel does not solve the problem either... How can I make sure that this library is always loaded? Load it when the pluging is opened? How do I do this in my VBA code?

The second problem is a little more tricky. When the calculations are done, new sheets are added to the workbook. However, the algorithm assumes the input data to be in a strict format, and the output sheets do not apply this format. Is there a way that when data is imported, I can assign some 'label' to this sheet, so that I can easily see if this worksheet contains data imported by the add-in?

Many thanks for your help,
I'm looking forward to hear from you all!
  #2 (permalink)  
Old November 13th, 2006, 06:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hi fifijr,

I notice its been a while since you posted this question so I hope the advice is still current.

With regard to your referencing problem you appear to be describing an early binding solution. Early binding is great for development and use on a single development / operating machine but can fall over (i.e. lose the reference) when switching between machines. If you are going to distribute a bit of code I would always advise that you develop the code with early binding but then convert the code to late binding for distribution onwards. Late binding does not rely on the project having a specified file path to a given reference but rather lets VBA go and find the correct project library at run-time (note that this will make your code run slightly slower). To illustrate how to use early and late binding and the differences between them I will show a couple of very simple bits of code that do the same thing with the two methods.

Code:
Sub EarlyBinding()

' Declare objects
Dim scrFSO As Scripting.FileSystemObject
Dim scrTS As Scripting.TextStream

    ' Initiate FileSystemObject - NB can also be done in declaration line
    Set scrFSO = New Scripting.FileSystemObject

    ' Create a new text file
    Set scrTS = scrFSO.CreateTextFile("C:\Test1.txt")
    scrTS.WriteLine Text:="Hello World"
    scrTS.Close

    ' Dereference variables - memory management
    Set scrTS = Nothing
    Set scrFSO = Nothing

End Sub

Sub LateBinding()

' Declare objects
Dim scrFSO As Object
Dim scrTS As Object

    ' Initiate FileSystemObject
    Set scrFSO = CreateObject("Scripting.FileSystemObject")

    ' Create a new text file
    Set scrTS = scrFSO.CreateTextFile("C:\Test2.txt")
    scrTS.WriteLine Text:="Hello World"
    scrTS.Close

    ' Dereference variables - memory management
    Set scrTS = Nothing
    Set scrFSO = Nothing

End Sub
The key things to note here are that:
1) Under late binding you do not need any project references
2) Under late binding you declare all late bound variable as Object variable type
3) To initiate a variable under late binding you use the CreateObject function. You pass to the CreateObject function the library and the object name delimited by a full stop as a string.
4) All other coding remains as per early binding
5) You will not get any intelli-sense drop down boxes when writing late bound code as VBA only hooks into the library at run-time (hence the recommendation to code as early bound).

As to your second point this sounds like a project structuring issue and therefore altogether harder to answer. If you just want to mark out imported data sheets by the sheet name then you can easily change the name of the sheet using something along the lines of:

Code:
dim sh as Worksheet

    ' Other code...

    Set sh = ThisWorkbook.Sheets("Sheet1")
    ' or whatever else you may be using in your code...

    ' Other code...

    sh.Name = "INPUT - " & sh.Name
However I guess that I'm missing some of the subtlety of your problem as this is a trivial solution for someone who is automating external libraries.

HTH,
Maccas



Similar Threads
Thread Thread Starter Forum Replies Last Post
Install problems - VS 6.0 on XP SCRRUN.DLL PDM.DLL jeff4444 Visual C++ 0 December 6th, 2006 08:48 PM
Reference to a DLL costaaa C# 0 August 5th, 2006 03:07 PM
How to reference a .dll file? ohChuck C# 4 April 20th, 2006 10:32 AM
DLL & Excel - keep having to reset DLL reference! James Diamond Pro VB 6 2 May 25th, 2004 03:37 AM
SOLVED - beginners problem - scrrun.dll ? Olive Oyl Classic ASP Basics 1 January 15th, 2004 02:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.