Free Microsoft Excel 2013 Quick Reference

Using dll in vba Results

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

Hello,

I would like to write a small piece of VBA code that could make any UDF within any DLL available as a worksheet function (if this piece of code is available to this worksheet).

Let's assume that "C:myDll.dll" contains a function called "myNewUdf".
One possible signature for the VBA function I would like to write would be this:

Code:
where:

- dll is the path of the dll
- fun is the name of the UDF function
- args are the arguments of the UDF function

On the spreadsheet, I would then use this function as follows:

Code:
Of course, if the DLL is registered, then it would be much better to use an application name and class name instead of the
path of the dll. Also, the code would probably be simpler to write!

Who could write this "testAnyUdf" function?
Thanks to it a try!

Ive been looking at sharepoint and importing a list via VBA,

I have got as far as

1) getting the UID of the feed and using owssvr.dll to get the XML ie something like

http://xxxxxx/_vti_bin/owssvr.dll?XMLDATA=1&List={DF6233A4-xxxx-45F9-xxxxx-214B79596495}

2) referencing microsfot XML , V5

3) creating a new domdocument

4) load XML and pass the string

and now im stuck

If i use the text property i do get the full file;
I can also pull out First and Lastchild names which returns (surprisE!) xml

I think its the schema being in the file that is throwing me a bit,

If i try use documentelelemnt.childnodes , i cant seem to get down to the nexdt node

if i try use selecting singlenode , again i cant seem to move.

does anyone have any experience of working with sharepoing in VBA ?

I have a new client company using a model whose core macros were developed about seven years ago and have been used successfully by well over 1,000 users. (Not to brag - just to illustrate that I don't think it's anything in the VBA code that is the problem.)

Several users at this new company are getting Compile Errors when they attempt to use the first macro. Other users are not having the problem at all. The new company is very small, has no IT dept to speak of, and is very primitive in their Excel use. (Don't know if this is related, but the users who have the problem are both on Excel 2003, and had their macro security settings set to High. I had them reset to 'Medium' and restart Excel.)

I've seen this problem before, so I sent a copy with the VBA code unprotected to one of the users who was experiencing the problems, and asked him to go to the Tools, References so he could check-mark the missing files. But for him 'Tools, References' is grayed out.

So I had him close my model, open a blank one, and go into VBA to check Tools, References. It wasn't grayed out then, and there are a few DLL files I have checked on my PC that weren't even included on his list of options (Microsoft Forms 2.0 Object Library and Ref Edit Control). So I had him use the Browse feature to locate these two files, add them to his list and check them. Then reopen my model --

Same problem, and the Tools, References are still grayed out so I couldn't tell if the 'fix' took or not. then we closed my model, opened a new blank one, and rechecked Tools References -- but the newly-added DLL references weren't there.

Can anyone explain what's going on and what I can do to fix it?

I have a VBA script that goes down a list of hyperlinks and prints off the relevant document. All the documents come off at A4 size where the actual paper sizes should vary. I want column A to have the files and hyperlinks and column B to have the papersizes ie A0-A4. How can this be done?

My script follows :

Code:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As
String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Sub URLOpen()
   Dim URL As String
   Dim hlnk As Hyperlink
For Each hlnk In Columns("A").Hyperlinks
URL = hlnk.Address
   
   Call ShellExecute(0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus)
   PrintURL = ShellExecute(0&, "print", URL, vbNullString, vbNullString, vbNormalFocus)
Next
End Sub


Edit: This is used mainly for PDF's DWF's and DWG files

Hi All.

We have an excel addin here *.xla that has some hugely useful functions in for us such as complex number processing.

This capability has been removed in VB.NET therefore I want to take a module I have in the xla and somehow compile that into a dll so that we can publish these functions into sonething we can hook into using vb.NET.

My question therfore is...

How do you take a module in VBA, and compile it into a library. Secondary I guess is how do you make the functions public so they can be called by another application?

Cheers

Chris

Hi,

A question for those of you who have experimented with this.
I want to implement some heavy duty mathematical processing which is
accessible from an excel front end.

As I understand it my options a

1. Pure VBA functions (very slow but compatible with all versions of
excel from excel 97 upwards)
2. Pure C/C++ dll with wrapper functions in VBA (much faster but still
relatively slow for large worksheets with many cell formulae - again
available in all versions of excel 97 upwards)
3. A COM dll with wrapper functions in VBA (same as 2 above except
allows use in other projects such as a VB project - eughhh - I know -
I don;t see that as a plus either)
4. A COM dll which can be seen straight from worksheet level (I think
excel 2002 supports this? - need guidance on this though...)
5. Good old fashioned xlls in pure C/C++

I've sort of discounted options 1 and 2. What I'm really left with is
a core set of DLLs with an interface of an XLL or a COM DLL.

What I really want to know is do people find COM slower in terms of
real speed - especially if anyone has experimented with option 4
compared to 5.

And are Xlls deprecated in favour of COM?

(My gut preference is XLLs - I just don't trust COM for speed; Xll
coding is a bit dirty but not in a way that seems to kill speed)

(and btw has anyone had problems with compiling their Xlls with Visual
Studio .NET 2003 and its treatment of string literals?)

Any suggestions/guidelines/criticism would be much appreciated.

Thanks in advance,

M.

Well, I got something that did work. I changed

Set ref = Application.VBE.ActiveVBProject.References
to
Set ref = Application.VBE.VBProjects("MyProj").References

Where I renamed the default project name of VBAProject to
MyProj.

Now I can see my dll class, and I was able to remove it.
Whatever works, heh? I am still open for suggestions if
anyone has a better (or more correct) way to do this.

A J

>-----Original Message-----
>Hi All,
>
>I have an Excel project where I make a reference to an
>ActiveX dll class I wrote in VB6 on my workstation, but
>that file is not installed on other workstations which
>will use this Excel file. So I need to programmatically
>remove the reference before shipping the Excel file off.
>The purpose of the dll class is to collect data from an
MS
>Access Application and pass it to this Excel file. This
>works fine. But I need to remove the reference to my dll
>class in the Excle File. Even though the dll class
passes
>the data correctly to the Excel file, I can't detect it
>with the following loop, and thus cannot remove it.
>
>Dim v As Variant
>For Each V in Application.VBE.ActiveVBProject.References
>Debug.print v.Description
>Next
>
>This will correctly print out all the standard references
>and even the reference to VBA Extensibility, but it won't
>detect my ActiveX dll. In Access I can remove the
>reference successfully as follows:
>
>Dim ref As Reference
>Set ref = References!MyRef
>References.Remove MyRef
>
>But this does not work in Excel. If anyone knows how I
>can do this in Excel, I would be grateful if you could
>share.
>
>Thansk,
>A J
>
>.
>

Hi,

I've been fighting for a while trying to figure out how to get round a crash
in Excel, which seems to be related to my adding VBA code to a code module
through the VBProject.VBComponent.CodeModule.InsertLines() (using
..AddFromString() or similar methods makes no difference, Excel still blows up
in my face).

I'm running a standard XLA add-in, which acts as a glue layer between Excel
and the real functionality that resides in a number of VB6 DLL:s.

One of my VB6 DLL:s adds code to a worksheet -- and please note that this
worksheet is not part of the XLA, but belongs to an arbitrary workbook,
loaded by the user during run-time. (Info about how to insert code on the fly
can be found e.g. on Chip Pearson's site, at
http://www.cpearson.com/excel/vbe.htm)

It seems like there is no problem the first time I add the code. However,
if/when I try to re-acquire a pointer to the VBProject object I immediately
trigger a COM Automation exception in VBE. If I hold on to the objects I've
got I can still access the VBProject even after the code has been inserted.

The inserted code does not contain syntax errors or other (obvious) bugs,
and it does indeed work most of the time, because, strangely enough, not all
workbooks suffer from the problem -- there are only a few ones that do not
work. And when scrutinising these particular problematic workbooks I do not
notice any anomalies, not even a common pattern.

Excel security settings do not affect the outcome of the tests. The "Trust
VBProject access" checkbox is ticked, and my XLA is properly signed and
installed. Nevertheless, I still have a nagging feeling that this whole issue
is due to recompilation of VBA code during run-time, in combination with
security features present in Office Excel.

Interestingly enough, the Microsoft Office Crash Analysis (MOCA) screen
points me to a web page where MS claims that this crash is in fact a known
issue, and that a fix is available. This would have been really good news,
but unfortunately the web page says nothing about which patch I'm supposed to
apply, and by following the links I end up at the standard (generic) Office
Update page. And yes, I've already got all the recommended patches installed.

I'm currently running Win XP + Off 2003, but I suppose I will be able to
reproduce this on other Office platforms as well; I'm about to run some basic
tests on three or four virtual machines with different configurations.

Now, before I start posting source code and stuff I thought I'd better
submit an initial post to figure out whether any of you guys have run across
this issue before, and might even be able point me in the right direction.
(Indeed, I found quite a few posts on different websites, where developers
had been running into problems while using the
InsertLines/AddFromString/CreateEventProc methods, so even though they didn't
really help me, I'm pretty optimistic :-)

Cheers,
/MP

Is someone able to confirm the following behaviour in relation to the
following two UDFs:

Public Function BadFunction(avVariant() as Variant) as String
BadFunction= TypeName(avVariant)
End Function

Public Function GoodFunction(ParamArray avVariant() as Variant) as
String
GoodFunction= TypeName(avVariant)
End Function

Public Function GetTypeName(vVariant as Variant) as String
GetTypeName= TypeName(avVariant)
End Function

Test Harness:
Call the above three functions from the worksheet level.
=BadFunction(A1:B2)
=GoodFunction(A1:B2)
=GetTypeName(A1:B2)

Behaviour:
BadFunction returns #Value (and the function never gets called)
GoodFunction returns "Variant()" (ie., it works)
GetTypeName returns "Range"
The same is true if the implement these functions in a COM DLL, and
reference this DLL as a COM Addin through Excel XP (ie., you call the
DLL directly from the function bar on the worksheet level.

Interpretation:
Excel is able to turn range references into ParamArray parameter
types, and these end up inside the VBA function as an array of
Variants() (a variant array).
However, Excel is NOT able to turn range references into an array of
Variants() directly (there is a difference, and Excel can only cope
with the first case). BadFunction is never called, probably due to a
parameter type mismatch error.
Even though the ParamArray ends up as TypeName=Variant() inside the
function, the signature for this function is clearly different to a
function with a pure avVariant() parameter.

Why?:
It looks like the black box sitting between the Excel function bar and
the eventual COM call is not able to turn range references in function
calls into Variant() arrays (ie., an array of Variants). It CAN
interact with a ParamArray parameter, but it can't interact with a
parameter expecting and array of Variants. This is a very fine
distinction.

Why do I ca
When you are writing a COM DLL in C#, and exposing this DLL to Excel
using Excel XP "COM Addins" functionality, you might want to take an
array of cells as in an argument to a function:
eg. =MyCSharpExcelCOMAddinFunction(A1, A11)
Turns out you can't (?) do this without using the Primary Interop
Assembly (PIA) for Excel in C#. Firstly, the COM interop layer has no
idea with an Excel Range object reference is, so there is no point
defining a parameter of this type. However, Excel is smart enough to
convert cell/range references into primatives if the target COM
parameter type is a primitive. We also know that Excel can translate
range references into ParamArray-compatible parameter types (which
them. You would hope, therefore, that it could interact with the
DotNet COM Interop layer where the target method expects and array of
variants. But it can't. You can DEFINE and array of Variants in the
C# method signature, but Excel doesn't know how to pass these from the
function bar. And you CAN'T define a parameter in a C# method
signature that corresponds with the PARAMARRAY type in COM speak
(varargs in IDL?), but this is the only thing Excel's formula bar
know's how to speak.

Anyone have any interest in this?

I have made a programme in Excel 2000 using VBA and a number of sheets.
Indata to the programme is at some instances entered from User forms.
The User forms are activated by clicking a Command Buttons. While
trying to activate the User forms in Excel 2003, an error message
occured: "Compile error in hidden module: Sheet 15".
Changing security level to low was not enough.

I became aware of that an ActiveCGM Control was missing. After
installing the file acgm.dll, the programme was running on computers in
my house, but problems still occured on other computers.

Are there any other settings I need to change/add in Excel 2003?
And is this acgm.dll specific for specific versions of Excel?

Beste regards
Thomas

A user's special folders (e.g., "My Documents" or "Desktop") are stored in
the user's "Profile Directory". Using the code on
http://www.cpearson.com/excel/SpecialFolders.htm you get the user's Profile
Directory or a specific folder like "My Documents".

If you're asking about the Excel typelib in particular, you're in luck,
because Excel's typelib is in the exe itself. Thus, you can use the
FindExecutable API function to get the full file name of Excel by passing
the name of an existing XLS file to FindExecutable.

The following code will create a temporary xls file in the user's designated
Temp folder (see http://www.cpearson.com/excel/Workin...AndFolders.htm
for code to work with temporary files and folders), call FindExecutable to
get the path the Excel.exe, kill the temp file, and then use
VBProject.References.AddFromFile to add the Excel typelib to VBProject.

For other typelibs, you may need to do a few lookups in the
HKEY_CLASSES_ROOT registry to get the actual location of the typelib.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

Public Declare Function FindExecutable Lib "shell32.dll" Alias
"FindExecutableA" ( _
ByVal lpFile As String, _
ByVal lpDirectory As String, _
ByVal lpResult As String) As Long

Sub AAA()

Dim TempFileName As String
Dim ExeName As String
Dim Res As Long

' GetTempFile comes from
' http://www.cpearson.com/excel/Workin...AndFolders.htm
TempFileName = GetTempFile(InFolder:=vbNullString, _
FileNamePrefix:=vbNullString, Extension:="xls", CreateFile:=True)

ExeName = String$(260, vbNullChar)
Res = FindExecutable(TempFileName, vbNullString, ExeName)
Kill TempFileName
If Res > 32 Then
ExeName = TrimToNull(ExeName)
Excel.Application.ActiveWorkbook.VBProject.Referen ces.AddFromFile _
FileName:=ExeName
Else
Debug.Print "Find Executable failed"
End If

End Sub

Function TrimToNull(Text As String) As String
''''''''''''''''''''''''''''''''''''''''''''''
' TrimToNull
' Returns the portion of Text to the left
' of the first vbNullChar.
''''''''''''''''''''''''''''''''''''''''''''''
Dim Pos As Integer
Pos = InStr(1, Text, vbNullChar, vbTextCompare)
If Pos Then
TrimToNull = Left(Text, Pos - 1)
Else
TrimToNull = Text
End If
End Function

"Perico" > wrote in message
...
> How do you find a file that may be in different locations / folders on
> different users pc's? Example, let's say I want to use a function to set
> a
> reference or remove a refence to a Type library. (I have such code, but
> that
> code is not the issue.) Say I'm using automation and controlling Excel
> from
> Access vba and I want to set or unset a reference to the Excel Type
> Library
> by code. Different users may have their Excel Type Library installed in
> different places. How do you locate that Type Library file using vba
> code?
>
> I've seen "%", which I think is a placeholder or wildcard of some sort
> (please clarify - I've been curious exactly what it is), used with MSDos
> and
> in Windows but not sure if an operator like that is useful for my
> objective.
>
> Thanks in advance for any help.

I have a large project that uses VBA to open and close files. The project is
password protected. I recently ran into a problem whereby a password prompt
appears when closing Excel and VBA projects remain open after the
corresponding workbooks have been closed.

This error was apparently caused by omitting statements in the style of 'Set
objectName to Nothing' before closing the workbook (a solution documented in
other forums). This has since been corrected. The password prompts no
longer appear and workbooks are closed by the code without problems.

However, the problem I now have is that VBA projects now remain open for any
spreadsheet that is manually opened and closed (regardless of whether they
have any code in them). Re-opening the same file causes a second instance of
the same project to appear. They remain open until Excel is shut down.

I have tried cleaning my code and re-installed Excel but have been unable to
find a fix. I have found references to other people seeing similar symptoms,
but no solutions. My suspicion is that there may be some sort of hangover
issue in the registry or with DLLs, as all workbooks are affected.

I am using Excel 2002 with SP3 (although identical symptoms have been seen
on a colleague's machine which runs Excel 2003).

Any assistance would be greatly appreciated.

Simon.

So, instead, I want to call whatever Windows dll that is used with the Windows 98 SE or XP or whatever version "Find" command, which command does "see" the VBA project name. What library is the dll located in and what are the parametes and return values... (Haven't done any C++ for a while...) Right now my VBA code searches the file text for a string completely different from the VBA project name and which string the user may inadvertently add to the worksheet without realizing that it can cause a problem (I don't want to have to use the help or read me file to tell them not to type the string anywhere in the workbook...).

Hello,

I have been using Tree View control on Excel 2000 VBA under Windows 2000. The tree is succesfully populated with data and displayed as expected. However, in a couple of additional tests (same SW configuration, different PCs) the tree view did not display any of the data: It appears blank to the user. The nodes in the tree are loaded with data however, the problem is that none of them are visualized. I have found some other users reported the same problem; the alternatives I have tested did not lead to any results (adding a delay via timer while populating the tree, populating the tree view control in Load event of the form).

Is there any known incompatiblity in Tree View or any dependency of this control to be aware of (e.g. verify that a particular .dll exists in the target PC, űse a particular .ocx version, etc)? If anyone can kindly provide references to solve this it would be greatly appreciated.

BR,
SK

Question:
How do I break up a large Excel add-in so that it does not have to load every time Excel is opened?

Background:
I have created a rather large Excel add-in (3.7Mb) using VBA. I will be adding features to it that will only increase its size.

I have successully used all of the file size reduction techniques.

I would like to break up the add-in into external files that would only be accessed as needed and have a much smaller add-in that accesses those files.

My initial thought was to use Visual Basic 6 and create DLLs. Based on what I have read, this seems to be old technology. It looks like people are using the .NET platform now. I don't understand the differences much except people say that registering / unregistering DLLs is a snap when they are created with .NET

Any ideas / thoughts / comments on this would be greatly appreciated.

This fairly large Excel VBA module has worked on dozens of PCs - until one installation today. The PC had a clean install of Windows XP SP1 with all critical updates added. Excel 2003 was installed as part of a "full" Office 2003 install.

The code broke during compilation on all instances of the VBA and ADODB libraries. However references to these libraries were set, and all references in Excel and the VBA editor *seemed* identical to other PCs which ran the code perfectly well, whether running Excel 2002 or Excel 2003. We found the only way to get the code to run was to prefix functions from these libraries with the library name, eg VBA.space() and ADODB.Open().

Also, all instances in the code of the useful shortcut range reference of putting the defined name of the range in square brackets [RangeName] also failed compilation and had to be replaced with Range("RangeName") for the code to compile.

What have I missed, or what is this box missing?

Many thanks
Phil

PS - a clue, or irrelevant I do not know, but before the critical updates were run on this box, the same code also fell over due to a couple of API calls to ADVAPI32.DLL being unrecognised, something else I have never seen before. But all the fixing that needed was for the MS updates to be installed. The DLL was present in the SYSTEM32 folder both before and after the updates.

I've asked this question in a couple of other forums, and so far have not been able to find an answer.
http://answers.microsoft.com/en-us/office/forum/office_2010-customize/manipulate-an-add-in/e827e9d7-a64a-e011-8dfc-68b599b31bf5

I have a third party Add-In that I use in a Excel application that I am developing. I would like to find a way to bypass the UI of the Add In if possible.

This Add-In connects to a SQL database and pulls financial data (for our company) from it. It adds a couple of functions to Excel that allow us to get the financial data. It has added a menu to the Add Ins tab which also allows the user to log in, using the same log in information that they use to log into our financial software.
It uses five files that are placed in the Office14 folder, three of which are generic files: csla.dll, extensibility.dll, ICSharpCode.SharpZibLib.dll, CompeatXL.dll and ComeatXL.Library.dll.
I can give more information if needed.
Thanks

Harlan

Hey guys. Thanks in advance for any help that anyone might have!

Background of Project
I am developing a "program" if you will, basically a compilation of VBA macros that will allow the user to enter guest info for their wedding, then take the information and display it in a more user friendly view, give different reports on different aspects such as if invitations have been sent to who, and number of people coming.

Problem
I am trying to implement a mail merge type system to this so people that are less experienced with mail merge would only have to select what paper type they are using, and all the fields will auto populate and be ready for them to print. When working on this on my Windows box, using office 2010, I can use the following line to bring up the Label Options window:


	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
On my Mac box, using office 2011 I get the following error:
Compile error:
Can't find project or library

When looking under Tools >> References I see that it says I am missing Microsoft Office 14 Authorization Control 1.0 Type Library. Thinking that was the issue I tried to find and download that, but all I can find is a .dll file, which I believe to be only windows compatible, and couldn't get to work.

The following are the checked references:
Visual Basic for AApplications
Microsoft Excel 14.0 Object Library
Microsoft Word 14.0 Object Library
OLE Automation
Microsoft Office 14.0 Object Library

The following are the other available references (not checked):
Chart 14 Type Library
Microsoft Graphy 14.0 Object Library
Microsoft PowerPoint 14.0 Library
Microsoft Visual Basic for Applications Extensibility 5.3

I have tried checking all the references and still get the same error on the same line. I am no expert, but it seems like I need a Library reference? I can't seem to find any answers in my searching, so I turn to you to see if anyone knows and wishes to bestow their wisdom to me

Hi guys

Im hoping this will be an easy solution! Basically I have an excel spreadsheet which I would like to communicate with an AS400 machine. What I would like to know is can you write a VBA macro which runs the AS400 macro (for ease say the macro name is AS400Macro.RMC). What I would like to happen is the macro runs in Excel, executes the AS400Macro, then returns to VBA.

I know there are a couple of ways for excel to talk to AS400 using sendkeys and appactivate, or declaring


	VB:
	
 
Declare Function WD_SetCursor Lib "C:Program FilesRumba-52CSYSTEMEhlapi32.DLL" (ByVal hInstance As Long, ByVal Position As
Integer) As Integer 
Declare Function WD_CopyOIA Lib "C:Program FilesRumba-52CSYSTEMEhlapi32.DLL" (ByVal hInstance As Long, ByVal OIAData As
String, ByVal length As Integer) As Integer 
Declare Function WD_SearchPS Lib "C:Program FilesRumba-52CSYSTEMEhlapi32.DLL" (ByVal hInstance As Long, location As Integer,
ByVal Position As Integer, ByVal SearchData As String) As Integer 
 
retval = WD_ConnectPS(100, "A") 
RV = WD_SetCursor(100, 294) 
Do 
    RV = WD_SendKey(100, Ass) 
Loop Until RV = 0 
Do 
    RV = WD_SendKey(100, "@E") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
All I really want is the as400Macro.rmc to run in AS400 before continuing with the rest of the vba

Any clues?

Mike