Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Tif & Jpg Image Open in Excel VBA

Dear Experts,

I want to solution for opening tif, jpg & pdf images via excel using vba macro. Please help me to get solution for this request.

I here with enclosed sample format excel with this post. Kindly review and let me know if you have any clarifications and questions in this regard.

Thanks & Regards,
Ganesh


Post your answer or comment

comments powered by Disqus
Hi all,

I'm way out of my depth with Excel and VBA but learning as I go and slowly making progress. My latest problem I can't find any information about.

I have Excel opening a .chm file, the chm file plays .avi videos when opened normally, but simply shows a blank box when opened in Excel. It's almost like when Excel opens the chm, it looses the directory links back to the .avi files.

I've even tried to get Excel to play the files but even that is a mission. Mostly I've sourced code and when played absolutely nothing happens.Any ideas?

Thanks.

I am trying to execute a stored proc in a VBA macro and get the following result at running time:
'user-defined type not defined'

Does this mean that it is not possible to run a Stored proc in excel VBA or that I am not using the right syntax?

Thanks All of your help.
Cheers,
Cecile
Sub Auto_Open()
Dim cn
Set cn = New adodb.Connection

sConnect = "Data source=TMS;User Id=ETMS; Password=xyz;"
With cn
.ConnectionString = sConnect
.Open
.Execute = "exec ff;"
End With
MsgBox "hello!", vbInformation, "the connection is "

End Sub

how do you tell in VBA if there is workbook open in excel. That is, when I
launch my xla, I want to check to see if there is at least one workbook open.
If not, I will prompt user to open workbook and exit the xla.

Thanks

Dear all,

Is it possible for ADODB.Recordset to read xml in excel??
I have followed the code below but it prompt error when recordset open.
Error Message"無法建立資料錄集 來源XML未完成或無效".
Do you know why???
http://www.ozgrid.com/forum/showthread.php?t=27818

Moreover, Is it possible to use xmltextreader in excel vba??
All I want to do is speed up searching a record in xml file.
Now I have done according to below. But it need iterate for each record.
http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Any Ideas in excel vba???

Hello,

I am trying to create an Outlook appointment in Excel VBA. I'm trying to use late-binding code, because I'm not sure which versions my users will have. Here's what I have so far:

Sub appointment()
  
Dim OutlookApp
Dim AP
Set OutlookApp = GetObject(, "Outlook.Application")
OutlookApp.GetNamespace("MAPI").Logon
Set AP = OutlookApp.CreateItem(olAppointmentItem)
With AP
.Display (True)
End With
    
End Sub
However, it keeps opening an email item (instead of an appointment). Please help! Thank you!

Dear all,
Are there any heuristics or algorithms such as shortest path, minimum cost flow, minimum spanning tree have been implemented in excel VBA such that those problems can be solved>?
I am doing a project similar to that so I wanna to have a look.
Thx much

I have two fields in a file. I want to generate results field that has one of the following four values

i) one-to-one
i) one-to-many
i) many-to-one
i) many-to-many

for each record one of these values is generated depending upon the relationship between the original two fields. Here is an example of what I am aiming to achieve...

Field-1 Field-2 results
A 1 one-one
B 2 one-many
B 2 one-many
C 2 many-many
C 3 many-one
C 3 many-one
D 4 one-many
E 4 one-many
F 5 one-one
F 5 one-one

I hope the example makes it clear. Basically I want to check the correspondence relationship between the two fields. Is there an easy way of doing this in Excel-VBA?

Thanks in anticipation

Any one here know how to create drill down in excel vba ?
excel has a function fo pivot table, know anyone knows how to create that type of menu using vba ?

I'm trying to write an SQL query in excel vba to pull in the last 20 service records for each customer in an access database. I'm having some difficulty with the query and hoped someone here could help.

My tables are basically:
Service table with fields: customer number, service date
Customer table with fields: customer number, customer name

I need to pull in the last 20 dates each customer was serviced like so:

customer number customer name service date
1 John 04/21/2005
2 Mary 04/15/2005
1 John 03/14/2005
2 Mary 03/01/2005
etc. until we have the last 20 dates per customer for each customer in the database.

The best thing I have so far is:

SELECT top 20 (s.scheduledate), c.customernumber
FROM Customer as C INNER JOIN Service as S ON C.CUSTOMERNUMBER = S.CUSTOMERNUMBER
GROUP BY s.scheduledate, C.CUSTOMERNUMBER
order by scheduledate DESC

But this doesn't work as I expected.

Any ideas or suggestions would be greatly appreciated.

Working in Excel VBA, I'm getting a runtime error '13' at the noted line (set frm2 = new frmEditExpenseData). I'm not sure why I'm getting this. I think I've seen this error on the calling sub, when the problem was actually in the userform, but how do I find where? I'm pretty new to VBA, so any help is much appreciated.

Here's the code of the calling sub:

	VB:
	
 AddExpenseButton() 
     
    Dim frm2 As frmEditExpenseData 
     
     'MsgBox "load start"
    Call LoadTemplateInfo 
     'MsgBox "load end"
     
    Set frm2 = New frmEditExpenseData '#### JCL  I'm getting a runtime error '13' type mismatch on this line? I don't get it
     
     'show display window, making default existing costcenters, modify existing data.
    frm2.Show 
    If frm2.Tag = False Then 
         'user cancelled expense update
    End If 
    Unload frm2 
    Set frm2 = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And here's the code associated with the userform:

	VB:
	
 
Option Base 1 
 
 
Private Sub btnCancel_Click() 
     'cancel w/o clearing existing data
    Me.Tag = False 'expenses not updated
End Sub 
 
Private Sub btnOK_Click() 
     
    Dim CurrentExpenseData(1 To 5, 1 To 13) As String 
     
    Dim ExpenseTopLeftCorner As String 
    Dim ExpenseDescType(1 To 5, 1 To 3) As String 
    Dim ExpenseByMonth(1 To 5, 1 To 12) As Double 
    Dim MonthSelected(1 To 5) As Integer 
    Dim i As Integer 
    Dim j As Integer 
    Dim counter(1 To 12) As Boolean 
    Dim LineError(1 To 5) As Boolean 
    Dim TestSum As Double 
    Dim ErrMsgText As String 
    Dim ErrMsg As Integer 
     
     'first check to make sure all fields are there and nothing is missing
    For i = 1 To 5 
        For j = 1 To 12 
            ExpenseByMonth(i, j) = 0 'initialize array
        Next j 
        MonthSelected(i) = 0 
        ExpenseDescType(i, 1) = "" 
        ExpenseDescType(i, 2) = "" 
        ExpenseDescType(i, 2) = "" 
        LineError(i) = False 
    Next i 
     
     'import form data
     'EXPENSE 1 --------
    ExpenseDescType(1, 1) = TextBox1.Value 
    If ListBox1.ListIndex = 0 Then 
        ExpenseDescType(1, 2) = "Material" 
    Else 
        ExpenseDescType(1, 2) = "Outside Service" 
    End If 
     
    ExpenseDescType(1, 3) = TextBox6.Value 
     
    If Frame1.ActiveControl.Value Then 
        MonthSelected(1) = Frame1.ActiveControl.Caption 
        MsgBox MonthSelected(1) 
    Else 
         'none selected
        MonthSelected(1) = -1 
    End If 
     
    For j = 1 To 12 
        If MonthSelected(1) = j Then ExpenseByMonth(1, j) = TextBox6.Value 
    Next j 
     
     'EXPENSE 2 --------
    ExpenseDescType(2, 1) = TextBox2.Value 
    If ListBox2.ListIndex = 0 Then 
        ExpenseDescType(2, 2) = "Material" 
    Else 
        ExpenseDescType(2, 2) = "Outside Service" 
    End If 
     
    ExpenseDescType(2, 3) = TextBox7.Value 
     
    If Frame2.ActiveControl.Value Then 
        MonthSelected(2) = Frame2.ActiveControl.Caption 
        MsgBox MonthSelected(2) 
    Else 
         'none selected
        MonthSelected(2) = -1 
    End If 
     
    For j = 1 To 12 
        If MonthSelected(2) = j Then ExpenseByMonth(2, j) = TextBox7.Value 
    Next j 
     
     'EXPENSE 3 --------
    ExpenseDescType(3, 1) = TextBox3.Value 
    If ListBox3.ListIndex = 0 Then 
        ExpenseDescType(3, 2) = "Material" 
    Else 
        ExpenseDescType(3, 2) = "Outside Service" 
    End If 
     
    ExpenseDescType(3, 3) = TextBox8.Value 
     
    If Frame3.ActiveControl.Value Then 
        MonthSelected(3) = Frame3.ActiveControl.Caption 
        MsgBox MonthSelected(3) 
    Else 
         'none selected
        MonthSelected(3) = -1 
    End If 
     
    For j = 1 To 12 
        If MonthSelected(3) = j Then ExpenseByMonth(3, j) = TextBox8.Value 
    Next j 
     
     'EXPENSE 4 --------
    ExpenseDescType(4, 1) = TextBox4.Value 
    If ListBox4.ListIndex = 0 Then 
        ExpenseDescType(4, 2) = "Material" 
    Else 
        ExpenseDescType(4, 2) = "Outside Service" 
    End If 
     
    ExpenseDescType(4, 3) = TextBox9.Value 
     
    If Frame4.ActiveControl.Value Then 
        MonthSelected(4) = Frame4.ActiveControl.Caption 
        MsgBox MonthSelected(4) 
    Else 
         'none selected
        MonthSelected(4) = -1 
    End If 
     
    For j = 1 To 12 
        If MonthSelected(4) = j Then ExpenseByMonth(4, j) = TextBox9.Value 
    Next j 
     
     'EXPENSE 5 --------
    ExpenseDescType(5, 1) = TextBox5.Value 
    If ListBox5.ListIndex = 0 Then 
        ExpenseDescType(5, 2) = "Material" 
    Else 
        ExpenseDescType(5, 2) = "Outside Service" 
    End If 
     
    ExpenseDescType(5, 3) = TextBox10.Value 
     
    If Frame5.ActiveControl.Value Then 
        MonthSelected(5) = Frame5.ActiveControl.Caption 
        MsgBox MonthSelected(5) 
    Else 
         'none selected
        MonthSelected(5) = -1 
    End If 
     
     
    For j = 1 To 12 
        If MonthSelected(5) = j Then ExpenseByMonth(5, j) = TextBox10.Value 
    Next j 
     
    TestSum = 0 
     'check for empty fields
    ErrMsgText = "" 
    ErrMsg = 0 
     'if all fields are blank ignore
    For i = 1 To 5 
        If ExpenseDescType(i, 1) = "" And ExpenseDescType(i, 3) = "" And MonthSelected(i) = -1 Then 
             'line is blank, no line error
        ElseIf ExpenseDescType(i, 1)  "" And ExpenseDescType(i, 2)  "" And ExpenseDescType(i, 3)  "" And MonthSelected(i)  -1
Then 
             'line is full completely, no line error
        Else 
             'something is empty somewhere
            LineError(i) = True 
        End If 
         
        If LineError(i) = True Then 
            ErrMsgText = ErrMsgText & i & ", " 
            ErrMsg = ErrMsg + 1 
        End If 
    Next i 
     
     'display error msg
    If ErrMsg > 0 Then 
        ErrMsgText = " " & Left$(ErrMsgText, Len(ErrMsgText) - 2) 'remove extra comma and space at end of string
        If ErrMsg > 1 Then 
            ErrMsgText = "s" & Left$(ErrMsgText, Len(ErrMsgText) - 3) & " & " & Right$(ErrMsgText, 1) ' add ampersand if more
than one line
        End If 
         
        ErrMsg = MsgBox("There is incomplete data at line" & ErrMsgText & ". Please verify information and try again.",
vbOKOnly, "INCOMPLETE DATA!") 
        Goto Retry 
    End If 
     
     'now clear the existing worksheet data
     'ActiveSheet.Unprotect Password:=Class3TemplatePassword
     
    ActiveSheet.Range("ExpenseData").Value = "" 
     
     'now export new data to worksheet
     
    ExpenseTopLeftCorner = ActiveSheet.Range("ExCorner").Value 
     
    For i = 1 To 5 
        For j = 1 To 2 
            ActiveSheet.Range("expensetopleftcorner").Offset(i - 1, j - 1) = ExpenseDescType(i, j) 
        Next j 
         
        For j = 1 To 12 
            ActiveSheet.Range("expensetopleftcorner").Offset(i - 1, j + 1) = ExpenseByMonth(i, j) 
        Next j 
    Next i 
     
    Me.Tag = True 'expenses updated
    Me.Hide 
     'ActiveSheet.Protect Password:=Class3TemplatePassword
     
Retry: 
     
End Sub 
 
Private Sub UserForm_Initialize() 
     
    Dim SelectType(1 To 2) As String 
     
    Dim ExpenseTopLeftCorner As String 
    Dim ExpenseDescType(1 To 5, 1 To 2) As String 
    Dim ExpenseByMonth(1 To 5, 1 To 13) As Double 
    Dim i As Integer 
    Dim j As Integer 
    Dim counter(1 To 12) As Boolean 
     
     'put in labels for list box
    SelectType(1) = "Material" 
    SelectType(2) = "Outside Service" 
     
    ListBox1.ColumnCount = 1 
     'ListBox1.ColumnWidths = "50"
    ListBox1.List() = SelectType 
     
    ListBox2.ColumnCount = 1 
     'ListBox2.ColumnWidths = "50"
    ListBox2.List() = SelectType 
     
    ListBox3.ColumnCount = 1 
     'ListBox3.ColumnWidths = "50"
    ListBox3.List() = SelectType 
     
    ListBox4.ColumnCount = 1 
     'ListBox4.ColumnWidths = "50"
    ListBox4.List() = SelectType 
     
    ListBox5.ColumnCount = 1 
     'ListBox5.ColumnWidths = "50"
    ListBox5.List() = SelectType 
     
     'i need to read out the current expense data
    ExpenseTopLeftCorner = ActiveSheet.Range("ExCorner").Value 
    For i = 1 To 5 'for each expense row
        For j = 1 To 2 'Desc + Type
            ExpenseDescType(i, j) = ActiveSheet.Range(ExpenseTopLeftCorner).Offset(i - 1, j - 1).Value 
        Next j 
         
        For j = 1 To 13 'for each month, 13 is total
            ExpenseByMonth(i, j) = ActiveSheet.Range(ExpenseTopLeftCorner).Offset(i - 1, j + 1).Value 
        Next j 
    Next i 
     
     'now I need to display this information in the userform
     'EXPENSE 1 =======
    If ExpenseDescType(1, 1)  "" Then 'expense line #1 not empty
        TextBox1.Text = ExpenseDescType(1, 1) 
        If ExpenseDescType(1, 2) = "Material" Then 
            ListBox1.ListIndex = 0 
        Else 'Outside Service
            ListBox1.ListIndex = 1 
        End If 
        TextBox6.Text = ExpenseByMonth(1, 13) 'output total expense
         
        For i = 1 To 12 'for each month
            counter(i) = False 
            If ExpenseByMonth(1, i) > 0 Then 
                counter(i) = True 
            End If 
        Next i 
         
        If counter(1) = True Then OptionButton1.Value = True 
        If counter(2) = True Then OptionButton2.Value = True 
        If counter(3) = True Then OptionButton3.Value = True 
        If counter(4) = True Then OptionButton4.Value = True 
        If counter(5) = True Then OptionButton5.Value = True 
        If counter(6) = True Then OptionButton6.Value = True 
        If counter(7) = True Then OptionButton7.Value = True 
        If counter(8) = True Then OptionButton8.Value = True 
        If counter(9) = True Then OptionButton9.Value = True 
        If counter(10) = True Then OptionButton10.Value = True 
        If counter(11) = True Then OptionButton11.Value = True 
        If counter(12) = True Then OptionButton12.Value = True 
    End If 
     
     'EXPENSE 2 =======
    If ExpenseDescType(2, 1)  "" Then 'expense line #2 not empty
        TextBox2.Text = ExpenseDescType(2, 1) 
        If ExpenseDescType(2, 2) = "Material" Then 
            ListBox2.ListIndex = 0 
        Else 'Outside Service
            ListBox2.ListIndex = 1 
        End If 
        TextBox7.Text = ExpenseByMonth(2, 13) 'output total expense
         
        For i = 1 To 12 'for each month
            counter(i) = False 'initialize
            If ExpenseByMonth(2, i) > 0 Then 
                counter(i) = True 
            End If 
        Next i 
         
        If counter(1) = True Then OptionButton13.Value = True 
        If counter(2) = True Then OptionButton14.Value = True 
        If counter(3) = True Then OptionButton15.Value = True 
        If counter(4) = True Then OptionButton16.Value = True 
        If counter(5) = True Then OptionButton17.Value = True 
        If counter(6) = True Then OptionButton18.Value = True 
        If counter(7) = True Then OptionButton19.Value = True 
        If counter(8) = True Then OptionButton20.Value = True 
        If counter(9) = True Then OptionButton21.Value = True 
        If counter(10) = True Then OptionButton22.Value = True 
        If counter(11) = True Then OptionButton23.Value = True 
        If counter(12) = True Then OptionButton24.Value = True 
    End If 
     
     'EXPENSE 3 =======
    If ExpenseDescType(3, 1)  "" Then 'expense line #3 not empty
        TextBox3.Text = ExpenseDescType(3, 1) 
        If ExpenseDescType(3, 2) = "Material" Then 
            ListBox3.ListIndex = 0 
        Else 'Outside Service
            ListBox3.ListIndex = 1 
        End If 
        TextBox8.Text = ExpenseByMonth(3, 13) 'output total expense
         
        For i = 1 To 12 'for each month
            counter(i) = False 'initialize
            If ExpenseByMonth(3, i) > 0 Then 
                counter(i) = True 
            End If 
        Next i 
         
        If counter(1) = True Then OptionButton25.Value = True 
        If counter(2) = True Then OptionButton26.Value = True 
        If counter(3) = True Then OptionButton27.Value = True 
        If counter(4) = True Then OptionButton28.Value = True 
        If counter(5) = True Then OptionButton29.Value = True 
        If counter(6) = True Then OptionButton30.Value = True 
        If counter(7) = True Then OptionButton31.Value = True 
        If counter(8) = True Then OptionButton32.Value = True 
        If counter(9) = True Then OptionButton33.Value = True 
        If counter(10) = True Then OptionButton34.Value = True 
        If counter(11) = True Then OptionButton35.Value = True 
        If counter(12) = True Then OptionButton36.Value = True 
    End If 
     
     'EXPENSE 4 =======
    If ExpenseDescType(4, 1)  "" Then 'expense line #4 not empty
        TextBox4.Text = ExpenseDescType(4, 1) 
        If ExpenseDescType(4, 2) = "Material" Then 
            ListBox4.ListIndex = 0 
        Else 'Outside Service
            ListBox4.ListIndex = 1 
        End If 
        TextBox9.Text = ExpenseByMonth(4, 13) 'output total expense
         
        For i = 1 To 12 'for each month
            counter(i) = False 'initialize
            If ExpenseByMonth(4, i) > 0 Then 
                counter(i) = True 
            End If 
        Next i 
         
        If counter(1) = True Then OptionButton37.Value = True 
        If counter(2) = True Then OptionButton38.Value = True 
        If counter(3) = True Then OptionButton39.Value = True 
        If counter(4) = True Then OptionButton40.Value = True 
        If counter(5) = True Then OptionButton41.Value = True 
        If counter(6) = True Then OptionButton42.Value = True 
        If counter(7) = True Then OptionButton43.Value = True 
        If counter(8) = True Then OptionButton44.Value = True 
        If counter(9) = True Then OptionButton45.Value = True 
        If counter(10) = True Then OptionButton46.Value = True 
        If counter(11) = True Then OptionButton47.Value = True 
        If counter(12) = True Then OptionButton48.Value = True 
    End If 
     
     'EXPENSE 5 =======
    If ExpenseDescType(5, 1)  "" Then 'expense line #5 not empty
        TextBox5.Text = ExpenseDescType(5, 1) 
        If ExpenseDescType(5, 2) = "Material" Then 
            ListBox5.ListIndex = 0 
        Else 'Outside Service
            ListBox5.ListIndex = 1 
        End If 
        TextBox10.Text = ExpenseByMonth(5, 13) 'output total expense
         
        For i = 1 To 12 'for each month
            counter(i) = False 'initialize
            If ExpenseByMonth(5, i) > 0 Then 
                counter(i) = True 
            End If 
        Next i 
         
        If counter(1) = True Then OptionButton49.Value = True 
        If counter(2) = True Then OptionButton50.Value = True 
        If counter(3) = True Then OptionButton51.Value = True 
        If counter(4) = True Then OptionButton52.Value = True 
        If counter(5) = True Then OptionButton53.Value = True 
        If counter(6) = True Then OptionButton54.Value = True 
        If counter(7) = True Then OptionButton55.Value = True 
        If counter(8) = True Then OptionButton56.Value = True 
        If counter(9) = True Then OptionButton57.Value = True 
        If counter(10) = True Then OptionButton58.Value = True 
        If counter(11) = True Then OptionButton59.Value = True 
        If counter(12) = True Then OptionButton60.Value = True 
    End If 
     
End Sub 
 
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) 
    If CloseMode = vbFormControlMenu Then Cancel = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for your help!

Hi..

I'm trying to run this query in excel vba.. although all my other queries worked fine this one doesn't return anything. This query is a little more complex than the other queries i have but i dunno if it's causing this. I ran the same query in Microsoft Query in excel and it worked fine but when i put this in vba it doesn't seem to work...Any help would be great..

Here is my vba code along with the sql:

Code:
SQLExecQuery Ers, _
    "SELECT Count(ERSS.ID)  " & _
    "FROM ers2006.dbo.ERSS ERSS  " & _
    "WHERE (ERSS.ID In (SELECT ERSS.ID  " & _
    "FROM ers2006.dbo.ERSS ERSS  " & _
    "WHERE (ERSS.ACAD_PLAN In ('" & AcadPlan & "','" & AcadPlan2 & "')) AND (ERSS.STD_LEV In ('1','2','3','4')) AND
(ERSS.ENR_STATUS = '4') AND (ERSS.PERIOD Like '%4') AND (ERSS.YEAR = '" & Year & "'))) AND (ERSS.PERIOD = '20064')  "

SQLRetrieve Ers, Cells(6, 2 + j), , , False


Hello all,

I've been busy trying to figure out how to edit MP3 tags in Excel/VBA.
I've found a method which makes use of a DLL-file called cddbcontrol.dll.
Here's the code which is working for me :
Code:
Sub MP3TagChange()
Sheets("MP3tags").Select
Dim id3 As New CddbID3Tag
id3.LoadFromFile Range("A2").Value, False
id3.Album = Range("B2").Value
id3.Title = Range("E2").Value
id3.LeadArtist = Range("F2").Value
id3.Year = Range("G2").Value
id3.Genre = Range("H2").Value
id3.TrackPosition = Range("I2").Value
id3.SaveToFile Range("A2").Value
End Sub
In A2 I have the full path to a MP3-file.
This code is working and the changes are being made to that file.

What I'd like to do is to change this code, so that it changes the tags for all the Mp3's which are listed in Col A.
I hope someone can help me out on this one..

Thanks in advance,
Mike

I want to display the coordinates of the currently 'selected' range of cells in a MSGBOX in Excel/VBA. Is this possible ?

Thanks...

I have a problem about using find method in Excel VBA.
Here is my program:-
__________________________________________________ _______________

Private Sub CommandButton2_Click()

Dim MyProduct As Range
Product = Range("F8").Value
Set MyProduct = ThisWorkbook.Sheets("Sheet2").Columns("E").Find(wh at:=Client, LookAt:=xlWhole)

ActiveSheet.Cells(8, 8).Value = MyProduct.Columns("G").Value

End Sub
__________________________________________________ ___________________

> how can I pull data at Columns(G) to Cells(8,8)
>the program I wrote ' ActiveSheet.Cells(8, 8).Value = MyClient.Columns("G").Value ' , give me error... Run-time error '91' Object variable or With block variable not set
>What does it mean?
> Please someone help me to solve this problem....

Dear all,

I hope to ask 2 questions on the Class Modules in Excel VBA.

1) It seems that only 2 instancing property are available for Excel
VBA: Private and PublicNotCreateable.

May I ask whether there will be some ways to declare the class module
as MultiUse or SingleUse?

2) Is there any method to use the classname string to initialize the
object. For example, we could do that by $className->new() in PERL.

Currently I use a switch clause to dispatch the constructor for such
PublicNoCreateable class but it need to modifiy each time a new class
is added.

Tom

where is confirm conversion at open in Excel 2007? ('03 tools opttions
general) I found options hidden under the multicolor office button. This is
what I use it for maybe there is another way now in 2007.
On the Tools menu, click Options, and then click the General tab.
Click Confirm conversion at Open.
At the step in the mail-merge process when you connect to your data file,
after you locate the Excel worksheet you want to connect to, the Confirm Data
Source dialog box opens. Click MS Excel Worksheets via DDE (*.xls), and then
click OK. In the Microsoft Excel dialog box, for Named or cell range, select
the cell range or worksheet that contains the information you want to merge,
and then click OK. The numbers from the Excel spreadsheet will now look
exactly the same in your merged documents as they look in the worksheet cells.

How to count the column value and convert to Row in Excel VBA
A B C 2 3 3 A A B B B C C C

Hello, everyone,
Am I reading this correct that in Excel VBA Useform Option buttons need to be linked to different cells as oppose to Excel controls, where there is one linked cell for the entire group.
It would be much better, to my mind, to have the option buttons linked to the same cell. Why link to different cells if those are not check boxes.
Thanks in advance for teaching me.

Hide/Restore Excel Toolbars in Excel VBA
One of the most exciting parts of Excel is perhaps its ability to be customized to show the end user something quite different. For example, many build their own custom toolbar and distribute them with their Spreadsheet. However, the one question that come up time and time again is 'how can I hide all of Excel's toolbars and show only mine when they are using my spreadsheet?' There are many ways to do this with some being extremely complicated (un-necessarily) through to totally flawed code which leaves the user starting at a blank screen. The golden rule when/if changing any part of Excel is to make sure it all goes back to how it was when they are not in our specific spreadsheet.

Ways To Restrict Loops in Excel VBA
Restricting Excel VBA LoopsEXCEL VBA: Restricting LoopsI would like to show you 2 ways to restrict looping by using the COUNTIF Function with the Find Method. The 1st code uses a whole cell match, while the 2nd uses a part cell match.The key thing to note in both codes is our use of the range variable rFound in the Find Method parameter for After: That is, After:=rFound. By using this we can move down the Column and find all matches. If we didn't use this, we always find the 1st match over and over again.
Sub RestrictLoop1WholeCellMatch()
Dim rFound As Range
Dim lLoop As Long

With Range("A:A")
'Set our range variable to the 1st cell in Column A
Set rFound = .Cells(1, 1)

'Use COUNTIF to restrict our looping
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "Dave")
'Use the Find Method and set each parameter to suit whole cell match
Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With rFound
'Your .With code here
End With
Next lLoop
End With
End Sub

Dim rFound As Range
Dim lLoop As Long

With Range("A:A")
'Set our range variable to the 1st cell in Column A
Set rFound = .Cells(1, 1)

'Use COUNTIF to restrict our looping
For lLoop = 1 To WorksheetFunction.CountIf(.Cells, "*Dave*",)
'Use the Find Method and set each parameter to suit part cell match
Set rFound = .Find(What:="Dave", After:=rFound, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With rFound
'Your .With code here
End With
Next lLoop
End With
End Sub


Add/Subtract Dates In Excel VBA
Add/Subtract Dates In Excel VBAEXCEL VBA: Create A Date AdderHere's how to create relatively easy Date Adder by using a UserForm, 3 TextBox Controls, 5 Label Controls, 1 ComboBox Control and 1 CommandButton Control. DOWNLOAD DATEADDERTextBox1 = Start DateTextBox2 = Positive/Negative AmountComboBox1 = Period. I.e Days, Weeks, Months, YearCommandButton1 = Add/TakeLabel1 = ResultCODE
Dim strPeriod As String

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then
MsgBox "Invalid period", vbCritical
ComboBox1.SetFocus
Exit Sub
End If

With ComboBox1
If .ListIndex = 0 Then strPeriod = "D"
If .ListIndex = 1 Then strPeriod = "WW"
If .ListIndex = 2 Then strPeriod = "M"
If .ListIndex = 3 Then strPeriod = "YYYY"
End With

End Sub

Private Sub CommandButton1_Click()

If Not IsDate(TextBox1) Then
MsgBox "Non valid date", vbCritical
TextBox1 = vbNullString
TextBox1.SetFocus
Exit Sub
End If

If Not IsNumeric(TextBox2) Then
MsgBox "Invalid amount", vbCritical
TextBox2 = vbNullString
TextBox2.SetFocus
Exit Sub
End If

If ComboBox1.ListIndex = -1 Then
MsgBox "Invalid period", vbCritical
ComboBox1.SetFocus
Exit Sub
End If

Label1 = Format(DateAdd(strPeriod, TextBox2, TextBox1), "dddd dd mmm yyyy")
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1 = vbNullString Then Exit Sub
If Not IsDate(TextBox1) Then
MsgBox "Non valid date", vbCritical
TextBox1 = vbNullString
Cancel = True
End If
End Sub

Private Sub UserForm_Initialize()
ComboBox1.List = Split("Day,Week,Month,Year", ",")
End Sub


I can create stand alone modules in Excel vba by clicking File New Module or
some such.

How can I run the code in those stand alone modules from another module or a
Form that has its own code?

Thanks for any help.

tmb

I want to use the excel functions value and search in excel vba code.
How do I do this

Hi

Am under a situation that i should embed other windows like Internet Explorer etc. in Excel 2007 VB forms and use it to the full functionality of it like we use in normal windows.

It should be similar to MDI parent and a MDI child in Excel VBA forms

Please Help me with the code required

Thanks in Advance
Prabu


No luck finding an answer? You could always try Google.