Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 3rd, 2006, 05: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!
Reply With Quote
  #2 (permalink)  
Old November 13th, 2006, 05:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 07: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 01:45 PM



All times are GMT -4. The time now is 11:33 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.