Newsgroups

You can get lots of useful information about Excel spreadsheets and macro programming from newsgroups. Just remember two things:

Read the articles in a newsgroup for 2 - 4 weeks before you contribute with any of your own questions.
Read the newsgroup FAQ before you post your own questions. The FAQ contains answers the most common questions made to the newsgroup. An updated FAQ is usually posted to the newsgroup every 2 - 4 weeks.

These newsgroups can be used to get information about spreadsheets and macro programming:
comp.apps.spreadsheets: all types of spreadsheets.
microsoft.public.excel.programming: macro programming in Excel.
microsoft.public.excel.worksheet.functions: worksheet functions in Excel.
You can connect directly to Microsoft's newsserver: msnews.microsoft.com.

If you are in a hurry to get an answer to a specific problem, you can use the Google Usenet Archive (former Deja News) or another search engine that indexes the newsgroup discussions.

You can find the latest copy of the FAQ for the news-group comp.apps.spreadsheets at this website: http://www.faqs.org/faqs/spreadsheets/faq/.
I recommend that you also read Hints and tips for new posters by Chip Pearson.

All Microsoft public newsgroups are available on the web: http://msdn.microsoft.com/newsgroups/

Document last updated 2008-02-25 21:49:33 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-05-05 13:23:04 CET):

Re: How to Set reference in MS EXCEL through VBA coding
You can e.g. set a reference to the ADO library programatically like this:
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll"

Ashokraja from Bangalore, India wrote (2006-05-05 12:06:57 CET):

How to Set reference in MS EXCEL through VBA coding
How to Set reference in MS EXCEL through VBA coding.

Thanks in advance.
ashokrajas@rediffmail.com

Ole P. from Norway wrote (2006-04-29 09:18:15 CET):

Re: Type Mismatch
Type mismatch errors usually occurs when you try to store an invalid value into the defined variable type, e.g. a text string into a numeric variable.
You can free memory used by an array variable like this:
Erase ArrayVariableName

Kengwui from United States wrote (2006-04-24 14:57:23 CET):

Type Mismatch
My macro consists of 3D and 2D arrays as follows:
Dim StringType (2 to 1000, 2 to 7, 2 to 10) as String
Dim ChoiceType (2 to 7, 2 to 10) as String

When I first ran my macro, it works. When I ran again, I received "Type Mismatch" error 13. If I reduce my StringType array from "2 to 1000" to "2 to 500", it runs for the first time, and then it fails on the second run. This is further rectified by reducing the number till "2 to 3", and I can't run my macro anymore. I think it has to do with Memory not being unloaded. Can you help me? What should I do?

Ole P. from Norway wrote (2005-06-22 21:16:43 CET):

Re: Adding value to dynamic created labels or textboxes
This example might solve your problem, it will create 5 labels and textboxes in an empty user form when it is opened:

Private Sub UserForm_Initialize()
Dim i As Integer, lbl As Object, tb As Object
For i = 1 To 5
Set lbl = Me.Controls.Add("Forms.Label.1", "lblLabel" & i, True)
With lbl
.Top = 10 + (i - 1) * .Height + 2
.Left = 5
.Caption = "Label " & i
End With
Set lbl = Nothing

Set tb = Me.Controls.Add("Forms.TextBox.1", "tbTextBox" & i, True)
With tb
.Top = 10 + (i - 1) * .Height + 2
.Left = 150
End With
Set tb = Nothing
Next i
End Sub

Bundi from Dublin, Ireland wrote (2005-06-21 10:00:06 CET):

Adding value to dynamic created labels or textboxes
First of all, thanks Ole for providing all these "gold nuggets" regarding VBA, I have had good use for them over the years!

I haven't though been able to find any solution to the thing I am working on now. I have a userform where I am creating labels and textboxes dynamically. I am naming them using a loop so that they are named Textbox1,Textbox2 and so on, stepping up the numbers 1 at the time.
I have tried using several methods for "calling" these dynamically created labels and textboxes.
But it seems like they aren't recognized at all, I just get object is required?

This is the method I am using for creating the textboxes:

With Controls.Add("Forms.textbox.1")
.Width = 15
.Left = l
.Top = 100

The number of them is controlled by a the caption of a label.

Would be most thankful for some input!

Kind regards, Bundi

Ole P. from Norway wrote (2005-04-05 18:16:24 CET):

Re: User forms
You can store the data from your user form in a hidden worksheet like this:
Private Sub btnOK_Click()
With Worksheets("MyHiddenSheet")
.Range("A1").Formula = Me.TextBox1.Text
.Range("A2").Formula = Me.TextBox2.Text
.Range("A3").Formula = Me.TextBox3.Text
End With
End Sub

You can hide and unhide worksheets from the menu Format, Sheet, Hide/Unhide, or you can do it using the properties window in the VBE.

Komal from Mumbai, India wrote (2005-04-05 10:18:22 CET):

User forms
I want to create a user form for data entry that will update the data in the workshheet without giving worksheet acess to the user so that no data manipulation is possible.

Ole P. from Norway wrote (2005-03-08 16:15:10 CET):

Re: Changing directorie
You might want to use the command ChDrive "X" (where X is the drive letter) before using ChDir "X:\Foldername" to change to a folder on another drive (e.g. a network drive).

Jan Liekens from Belgium wrote (2005-03-08 10:46:47 CET):

Changing directorie
I've made a program in Excel where I have to upload data from an other file in an other directorie then this where my program is made. Then I've to make calculations with the uploaded files in my directorie. I became an error because the program can't find the calculation program which is in my directorie. How can I change from directorie. (chDir doesn't work because when I check with Activeworkbook.path I'am in my directorie.)
Thanks
My email j.liekens@pandora.be