Creating an Excel file, writing VBA code to it, running a macro, and saving it as a Macro Enabled Workbook – all done using C#

This comes from my answer on Stack Overflow.

Let me show you how to

  • Create an Excel file
  • Write VBA code to VBA Project
  • Run the macro you’ve just written
  • Save your workbook as a Macro Enabled Workbook

You need to allow programmatic access to VBA Project in Excel.

  • (Excel) File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model

enter image description here

In your C# Solution add COM references to

  • Microsoft Visual Basic for Applications Extensibility 5.3
  • Microsoft Excel 14.0 Object Library

Add using directives to your C# code behind file

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Vbe.Interop;
using System.Runtime.InteropServices;

Now follow the code and comments

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

VBProject vbProj = wb.VBProject; // access to VBAProject
VBComponent vbComp = vbProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); // adding a standard coding module
vbComp.CodeModule.DeleteLines(1, vbComp.CodeModule.CountOfLines); //emptying it

// this is VBA code to add to the coding module
string code = "Public Sub HelloWorld() \n" +
" MsgBox \"hello world!\" \n" +
"End Sub";

// code should be added to the module now
vbComp.CodeModule.AddFromString(code);

// location to which you want to save the workbook - desktop in this case
string fullPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\macroWorkbook.xlsm";

// run the macro
xlApp.Run("HelloWorld");

// save as macro enabled workbook
wb.SaveAs(Filename: fullPath, FileFormat: XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
xlApp.Quit();

In the above code;

you create an host Excel application, add a workbook. Access VBA Project Object module, add a new standard coding module to the VBA Project, write a VBA macro to that module. `Application.Run(“HelloWorld”)` simply calls the macro – note you can comment out this section if you don’t want the box to pop-up. Then in the end you save the workbook as a *Macro Enabled Workbook* which to a location specified in `fullPath` variable.

*PS. Please note I haven’t added any error handling.*

Hope this helps :)

Create a shortcut in Excel for a custom operation.

Tags

, , , , , , , , , , , , , ,

This morning, I came across this question on Stack Overflow and after digging around for a bit I actually came up with a solution. I decided to post the solution on my blog because I didn’t come across one within the first page of Google search.

The question is:

I would like to overwrite an excel keyboard short-cut. For instance, ctrl-e must now center the text in a cell.

I have never really worked with shortcuts except to run macros. So I came across this but it didn’t help much

So I kind of knew there was no other way (correct me if I am wrong) to override a shortcut but to assign a macro to it. Well, you’re in a .Net framework, there are no macros in your project – you’re using C#.  What do you do now?

Since you need a macro, let’s make the macro first then we will worry how to glue it all together…

“CTRL+E must centre the text in a cell” – from VBA point of view this really easy. You can record a macro if you don’t know the right syntax for this operation but from “experience”

Sub CentreText()

    activeCell.HorizontalAlignment = xlCenter

End Sub

We have a working macro, now we need to assign a key to it…

Application.OnKey("^e", "CentreText")

It doesn’t get simpler than that…

Ok this works within Excel but how do I get this to work in a VSTO add-in?

We need to have a macro in our workbook in order to use Application.OnKey()

We are simply going to build this programmatically… watch:)

void addModule()
{
    VBProject vbProj = Globals.ThisWorkbook.VBProject as VBProject;
    VBComponent vbComp =
     vbProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);
    vbComp.CodeModule.DeleteLines(1, vbComp.CodeModule.CountOfLines);

    string code = "Public Sub CentreText() \n" +
                  " activeCell.HorizontalAlignment = xlCenter \n" +
                  "End Sub";

    vbComp.CodeModule.AddFromString(code);
}

This inserts a new standard coding module into your Workbook and writes 3 lines of code to it which means now we have a macro available in our workbook. The only thing that is left to do is to tell our Application that OnKey event we want it to run the macro (macro centring text horizontally)

 

addModule();
Worksheet ws = Globals.ThisWorkbook.ActiveSheet as Worksheet;
Workbook wb = ws.Parent as Workbook;
Microsoft.Office.Interop.Excel.Application app =
    wb.Parent as Microsoft.Office.Interop.Excel.Application;
app.OnKey("^e", "CentreText");

 

And there you go :)

Debug /Run your C# solution. Write some text to a cell and hit CTRL+E to centre it horizontally.

 

Add and remove COM references programmatically

Tags

, , , , , , , , , , , , , , , , , , , , , ,

Firstly, let’s see how to obtain currently active COM references.

By default, you should have 4 references attached to your VBA project.

Untitled

Count, Name, Description, GUID, Major, Minor, Fullpath

1. VBA Visual Basic For Applications {000204EF-0000-0000-C000-000000000046} 4 1 C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
2. Excel Microsoft Excel 14.0 Object Library {00020813-0000-0000-C000-000000000046} 1 7 C:\Program Files\Microsoft Office\Office14\EXCEL.EXE
3. stdole OLE Automation {00020430-0000-0000-C000-000000000046} 2 0 C:\Windows\system32\stdole2.tlb
4. Office Microsoft Office 14.0 Object Library {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52} 2 5 C:\Program Files\Common Files\Microsoft Shared\OFFICE14\MSO.DLL

And the code to obtain currently attached references

Sub Grab_References()

Dim i As Long
Dim arr() As String
Dim cnt As Long

cnt = ActiveWorkbook.VBProject.References.Count
ReDim arr(1 To cnt, 1 To 6)

On Error Resume Next
For i = 1 To ActiveWorkbook.VBProject.References.Count
arr(i, 1) = ActiveWorkbook.VBProject.References.Item(i).Name
arr(i, 2) = ActiveWorkbook.VBProject.References.Item(i).Description
arr(i, 3) = ActiveWorkbook.VBProject.References.Item(i).GUID
arr(i, 4) = ActiveWorkbook.VBProject.References.Item(i).Major
arr(i, 5) = ActiveWorkbook.VBProject.References.Item(i).Minor
arr(i, 6) = ActiveWorkbook.VBProject.References.Item(i).fullpath
Next i

For i = LBound(arr) To UBound(arr)
Debug.Print (i & ". " & arr(i, 1) & vbTab & arr(i, 2) & vbTab & arr(i, 3) & vbTab &         arr(i, 4) & vbTab & arr(i, 5) & vbTab & arr(i, 6))
Next i
End Sub

After running check out your Immediate Window (ctrl+G)

Untitled

This shouldn’t give you any trouble understanding how to retrieve active references. I have created a 2D array to store all the information of one reference in one block (line) or array. Simply, for easy access. So the first loop (the one after On Error Resume Next) is collecting the references and copying it to array. The second loop is simply printing it to the Immediate Window ( see Immediate Window print out).

—————————————————–

Adding new References ( from GUID and from files )

GUID – Globally unique identifier.

Where do you get GUID of a COM object. There are a few ways to easily get GUID. You can

  • Find GUID using CMD
  • Add references manually at least once and then run Grab_References() 
  • Scan registry ( if the type libraries are registered on your machine, if you are referencing other VBA projects than you are most likely going to want to use the AddFromFile method)
  • Use a freeware to identify the guid ( along with the path most of the time )

Ok, so you know GUID and you know full path to the *.tbl ( type library ) and .dll ( dynamic link library )

How do you use that information in VBA to add references programmatically? It’s super easy:

Just start typing…

ThisWorkbook.VBProject.References.

after the dot VBE intelli-sense should pop-up and give you a list of possible choices ie.

Untitled

So you can AddFromFile or AddFromGuid (click on each to see MSDN developers reference)

You will quickly find out that to work with the VBA project object model you need to grand access to it

Untitled

So a sample for adding references from GUID could be

Sub AddReference()

 Dim i As Long
 Dim guidArr() As String
 ReDim guidArr(1 To 9, 1 To 3)

 ' reference details
 ' Name: VBA
 ' Description: Visual Basic For Applications
 ' GUID: {000204EF-0000-0000-C000-000000000046}
 ' Major: 4
 ' Minor: 1
 ' FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
 guidArr(1, 1) = "{000204EF-0000-0000-C000-000000000046}"
 guidArr(1, 2) = "4"
 guidArr(1, 3) = "1"

 ' reference details
 ' Name: Excel
 ' Description: Microsoft Excel 14.0 Object Library
 ' GUID: {00020813-0000-0000-C000-000000000046}
 ' Major: 1
 ' Minor: 7
 ' FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
 guidArr(2, 1) = "{00020813-0000-0000-C000-000000000046}"
 guidArr(2, 2) = "1"
 guidArr(2, 3) = "7"

 ' reference details
 ' Name: stdole
 ' Description: OLE Automation
 ' GUID: {00020430-0000-0000-C000-000000000046}
 ' Major: 2
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\stdole2.tlb
 guidArr(3, 1) = "{00020430-0000-0000-C000-000000000046}"
 guidArr(3, 2) = "2"
 guidArr(3, 3) = "0"

 ' reference details
 ' Name: Office
 ' Description: Microsoft Office 14.0 Object Library
 ' GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
 ' Major: 2
 ' Minor: 5
 ' FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
 guidArr(4, 1) = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
 guidArr(4, 2) = "2"
 guidArr(4, 3) = "5"

 ' reference details
 ' Name: MSForms
 ' Description: Microsoft Forms 2.0 Object Library
 ' GUID: {0D452EE1-E08F-101A-852E-02608C4D0BB4}
 ' Major: 2
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\FM20.DLL
 guidArr(5, 1) = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
 guidArr(5, 2) = "2"
 guidArr(5, 3) = "0"

 ' reference details
 ' Name: ADODB
 ' Description: Microsoft ActiveX Data Objects 6.0 Library
 ' GUID: {B691E011-1797-432E-907A-4D8C69339129}
 ' Major: 6
 ' Minor: 0
 ' FullPath: C:\Program Files (x86)\Common Files\System\ado\msado15.dll
 guidArr(6, 1) = "{B691E011-1797-432E-907A-4D8C69339129}"
 guidArr(6, 2) = "6"
 guidArr(6, 3) = "0"

 ' reference details
 ' Name: Scripting
 ' Description: Microsoft Scripting Runtime
 ' GUID: {420B2830-E718-11CF-893D-00A0C9054228}
 ' Major: 1
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\scrrun.dll
 guidArr(7, 1) = "{420B2830-E718-11CF-893D-00A0C9054228}"
 guidArr(7, 2) = "1"
 guidArr(7, 3) = "0"

 ' reference details
 ' Name: Scriptlet
 ' Description: Microsoft Scriptlet Library
 ' GUID: {06290C00-48AA-11D2-8432-006008C3FBFC}
 ' Major: 1
 ' Minor: 0
 ' FullPath: C:\Windows\SysWOW64\scrobj.dll
 guidArr(8, 1) = "{06290C00-48AA-11D2-8432-006008C3FBFC}"
 guidArr(8, 2) = "1"
 guidArr(8, 3) = "0"

 ' reference details
 ' Name: VBIDE
 ' Description: Microsoft Visual Basic for Applications Extensibility 5.3
 ' GUID: {0002E157-0000-0000-C000-000000000046}
 ' Major: 5
 ' Minor: 3
 ' FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
 guidArr(9, 1) = "{0002E157-0000-0000-C000-000000000046}"
 guidArr(9, 2) = "5"
 guidArr(9, 3) = "3"

 On Error Resume Next

 For i = 1 To 9
     ThisWorkbook.VBProject.References.AddFromGuid GUID:=guidArr(i, 1), Major:=guidArr(i, 2), Minor:=guidArr(i, 3)
 Next i

End Sub

Creating an array of the references you want to add and then simply iterating over that array and adding it using AddFromGuid method.

To add references from file it’s as simple as

ThisWorkbook.VbProj.References.AddFromFile "C:\WINDOWS\system32\..."

—————————————————–

Removing references

Sub RemoveReferences()
Dim chkRef As Reference
Set chkRef = ThisWorkbook.VBProject.References.Item(5)
ThisWorkbook.VBProject.References.Remove chkRef
End Sub

Using a .NET ADODB.Connection object in VBA via COM library to be able to use .NET attributes within VBA

Tags

, , , , , , , , , , , , , , , , , , , , ,

Ahh I see VBA connection string doesn’t support the Application Name attribute. It simply isn’t being recognized when used within VBA. The only way I can think of solving this at the moment it’s to return anADODB.Connection object from a COM C# library.

Your own COM library would return an ADODB.Connection object with a predefined connection string which seem to work in .NET. You will be connecting to the database using a VBA ADODB.Connection object but with a substituted object reference. Instead of

Set cn = new ADODB.Connection you will use a GetConection() method exposed by your own library.

Dim cn as ADODB.Connection
Set cn = yourCOMlibrary.GetConnection

or something like that

This may not be too very useful to you in this case but I think maybe some future visitors will find it useful.

here are the steps to reproduce

Download and install Visual Studio Express for Windows (FREE)

Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to MyConnection

enter image description here

In the Solution Explorer, rename Class1.cs to ServerConnection.cs

enter image description here

Right click your MyConnection project in the Solution Explorer and select Add Reference

Type activeX in the search box and tick the Microsoft ActiveX Data Objects 6.1 Library

enter image description here

Copy and paste the below code into the ServerConnection.cs completely replacing whatever is in the file.

using System;
using System.Runtime.InteropServices;
using System.IO;
using ADODB;

namespace MyConnection
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("32A5A235-DA9F-47F0-B02C-9243315F55FD")]
    public interface INetConnection
    {
        Connection GetConnection();
        void Dispose();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("4E7C6DA2-2606-4100-97BB-AB11D85E54A3")]
    public class ServerConnection : INetConnection, IDisposable
    {
        private Connection cn;

        private string cnStr = "Provider=SQLOLEDB; Data Source=SERVER\\DB; Initial Catalog=default_catalog; User ID=username; Password=password;Application Name=MyNetConnection";

        public Connection GetConnection()
        {
            cn = new Connection();
            cn.ConnectionString = cnStr;
            return cn;
        }

        public void Dispose()
        {
            cn = null;
            GC.Collect();
        }
    }
}

Locate the cnStr variable in the code and UPDATE your connection string details.

Note: if you are unsure about the connection string you should use see ALL CONNECTION STRINGS

Click on TOOLs in Visual Studio and CREATE GUID

Replace the GUIDs with your own and remove the curly braces so they are in the same format as the ones you see now from the copied code

enter image description here

Right click MyConnection in the Solution Explorer and select Properties.

Click the Application tab on the left side, then Assembly Info and tick Make Assembly COM-Visible

enter image description here

Click the *Build* from the menu on the left and tick Register For COM Interop

enter image description here

Note: If you are developing for 64-bit Office then make sure you change the Platform Target on theBuild menu to x64! This is mandatory for 64-bit Office COM libraries to avoid any ActiveX related errors.


Right click MyConnection in the Solution Explorer and select Build from the menu.

If everything went OK then your MyConnection.dll and MyConnection.tlb should be successfully generated. Go to this path now

enter image description here

C:\Users\username\desktop\

or wherever you saved them

and you should see your files.

enter image description here


Now open Excel and go to VBE. Click Tools and select References.

Click the Browse button and navigate to the MyConnection.tlb.

Also, add references to Microsoft ActiveX Object 6.1 Library - this is so you can use ADODB library.

enter image description here

Now right click anywhere in the Project Explorer window and Insert a new Module

copy and paste the below code to it

Option Explicit

Sub Main()

    Dim myNetConnection As ServerConnection
    Set myNetConnection = New ServerConnection

    Dim cn As ADODB.Connection
    Set cn = myNetConnection.GetConnection

    cn.Open

    Application.Wait (Now + TimeValue("0:00:10"))

    cn.Close
    Set cn = Nothing

    myNetConnection.Dispose

End Sub

Open SQL Server Management Studio, right click the server and select Activity Monitor

enter image description here

dont close this window


Go back to Excel and hit F5 or hit the green play button on the ribbon.

enter image description here

now switch back to SSMS ( SQL Server Management Studio )

and wait for your custom connection name to appear! :)

Here we go! That was easy, wasn’t it? :)

enter image description here


This is what is happening.

You are returning an ADODB Connection object from you C# COM library by usingmyNetConnection.GetConnection function

Dim myNetConnection As ServerConnection
Set myNetConnection = New ServerConnectionDim cn As ADODB.Connection
Set cn = myNetConnection.GetConnection

It’s almost like saying Set cn = new ADODB.Connection but with predefined connection string which you did in your C# code.

You can use the cn object like a normal ADODB.Connection object within VBA now.

Remember to always .Close() the ADODB.Connection. A good programmers practice is to always close anything you open – streams, connections, etc.

You can rely on the Garbage Collector to clear the free references to objects but I also wrote aDispose() method for you so you can half-manually free the Connection so it does not hang in the SSMS as opened.

Remember to use myNetConnection.Dispose along with the cn.Close and you’ll be fine.

Note:

This is how I would do it if any one thinks this is wrong or needs to be updates (as being unstable orunsafe) please leave a comment.

Extending the functionality of VBA forms by using C# COM libraries. Specific: OpenFileDialog box pointing to My Computer by default.

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

AFAIK there is no VBA solution for this that’s why I decided to write a tutorial about how to achieve the effect of having the OpenFileDialog point to My Computer by default.

You can print all the environmental variables using the Environ$() function. This will not show any variable directly pointing to MyComputer therefore you can’t pass it to the .InitialFileNameproperty.

MyComputer is not a physical location that you can access through cmd. I think of it as an abstract Interface and it’s quite difficult to explain how VBA and .InitialFileName uses a string to access a location.

Well, the only workaround the problem I can think of it’s to use an external library written in for example C# that can access the MyComputer.

It’s easier than it sounds!

Follow the below steps to create your Custom OpenFileDialog.

You need a Visual Studio Express For Desktop - it’s free to download and use.

After installation – run as Administrator! (it’s necessary for the libraries to get registered)

Select File and New Project. Rename it to CustomOFD and and hit the OK.

enter image description here

Right-click the CustomOFD Project in the Solution Explorer and Select Add References

Add references to the System.Windows.Forms as shown in the below img

enter image description here

Right-click Class1.cs in the Solution Explorer and rename it to CustomOFD.cs.

Double click your CustomOFD and replace the code with the one from below

using System;
using System.Runtime.InteropServices;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CustomOpenFileDialog
{
    [InterfaceType(ComInterfaceType.InterfaceIsDual),
    Guid("541EDD34-4CDC-4991-82E9-6FC23F904B5B")]
    public interface ICustomOFD
    {
        DialogResult ShowDialog();
        string FileName();
    }

    [ClassInterface(ClassInterfaceType.None)]
    [Guid("E33102F0-B3C0-441C-8E7A-B9D4155A0D91")]
    public class CustomOFD : ICustomOFD
    {
        private OpenFileDialog box = new OpenFileDialog();

        public CustomOFD()
        {
            box.Multiselect = false;
            box.Title = "Select file";
            box.InitialDirectory = "::{20D04FE0-3AEA-1069-A2D8-08002B30309D}";
        }

        public DialogResult ShowDialog()
        {
            return box.ShowDialog();
        }

        public string FileName()
        {
            return box.FileName;
        }
    }
}

Note: you can generate a new GUID for your own class using the Tools => Create GUID and replace it with your own, if you wanted to…

Right-click the CustomFileOpenDialog in the Solution Explorer and select Properties

enter image description here

In the Properties window go to Application tab and click Assembly Info and tick the Make COM-Visible box

enter image description here

Then go to the Build tab and tick Register for COM interop

enter image description here

Right-click the project and select Build from the menu

Now look in the Output tab as it shows you where the library was compiled to

usually its

c:\users\administrator\documents\visual studio 2012\Projects\CustomOpenFileDialog\CustomOpenFileDialog\bin\Debug\CustomOpenFileDialog.dll

Ok. Now save and close VS.

Open Excel and go into VBE ALT+F11 and insert a standard module

Click Tools on the menu bar and select References

Click the Browse button and navigate to the CustomOpenFileDialog.tlb file and click OK add to the list of references

Copy paste the code for module

Option Explicit

Sub Main()

    Dim ofd As New CustomOFD
    Set ofd = New CustomOFD

    ofd.ShowDialog

    Debug.Print ofd.Filename
End Sub

enter image description here


finally, run the sub and enjoy the computer as the default location for the customized OpenFileDialog box!

enter image description here

VBA classes, interfaces, implements and adequate comparison methods

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

VBA doesn’t support class polymorphism so I recommend to change the way you’re thinking about the Employee and Manager classes.

You can’t have an Employee class as a base class and then a separate Manager class that derivesfrom Employee. They could be 2 separate classes implementing a common interface.

I will talk about it in detail in a bit. Let’s now go through a few examples…


↓ Easy approach ↓


base class (Person) and child classes which derive from the base class. (applies to C#, VB.NET, etc)

but in VBA you have to think of it like this:

A base class which exposes an enum property describing the position.

Something like

enter image description here

enter image description here

This is the easiest way to have a class exposing some properties. It allows you to add your Personobjects into a collection and iterate over using the easy for each loop with Intellisense!

enter image description here

Properties comparison system would be very very easy

enter image description here

note: same applies to enum as its implicitly converted to a number


↓ More complex approach ↓


Two separate classes which both expose public properties. For instance you have an Employee andManager classes which both implement a Person Interface and an additional Comparer class exposing a Compare() method

enter image description here

In your VBA Project you need 4 class modules and a standard module

enter image description here

Person (this is your Interface)

Public Property Get Name() As String
End Property

Public Property Get Age() As Long
End Property

this class is the interface which both the Employee and Manager both need to implement to share some common functions (getters for Names and Ages). Having the interface allows you to do the for each loop using the interface type variable as the enumerator. You will see in a minute.

Employee and Manager are identical. Obviously you may modify them to suit your real life solution.

Implements Person

Private name_ As String
Private age_ As Long

Public Property Get Name() As String
    Name = name_
End Property

Public Property Let Name(ByVal Value As String)
    name_ = Value
End Property

Public Property Get Age() As Long
    Age = age_
End Property

Public Property Let Age(ByVal Value As Long)
    age_ = Value
End Property

Private Property Get Person_Name() As String
    Person_Name = Name
End Property

Private Property Get Person_Age() As Long
    Person_Age = Age
End Property

ComparerCls you will use an instance of this class to compare two objects properties or references. You do not necessarily need to have a class for this but I prefer it that way.

Public Enum ComparisonMethod
    Names = 0 ' default
    Ages = 1
    References = 2
End Enum

' makes names the default comparison method
Public Function Compare(ByRef obj1 As Person, _
                        ByRef obj2 As Person, _
                        Optional method As ComparisonMethod = 0) _
                        As Boolean

    Select Case method
        Case Ages
            Compare = IIf(obj1.Age = obj2.Age, True, False)
        Case References
            Compare = IIf(obj1 Is obj2, True, False)
        Case Else
            Compare = IIf(obj1.Name = obj2.Name, True, False)
    End Select

End Function

And your Module1 code

Option Explicit

Sub Main()

    Dim emp As New Employee
    emp.Name = "person"
    emp.Age = 25

    Dim man As New Manager
    man.Name = "manager"
    man.Age = 25

    Dim People As New Collection
    People.Add emp
    People.Add man

    Dim individual As Person
    For Each individual In People
        Debug.Print TypeName(individual), individual.Name, individual.Age
    Next

End Sub

run the Main() sub and check out the results in the Immediate Window

enter image description here

The best part of the above code is the fact that you creating a reference variable of the Personinterface. It allows you to loop through all items in collection which implement the interface. Also, you can use the Intellisense which is great if you have had many more properties and functions.


Comparison


Take a look again at the code in the ComparerCls class

enter image description here

I hope you see now why I have separated this to be a class. Its purpose is just to take care of the way the objects are being compared. You can specify the Enum order and modify the Compare() method itself to compare differently. Note the Optional parameter which allows you to call the Compare method without the method of comparison.

enter image description here

Now you can play around passing different parameters to the compare function. See what the results are like

try combinations

emp.Name = "name"
man.Name = "name"

Comparer.Compare(emp, name, Names)
Comparer.Compare(emp, name, References)Comparer.Compare(emp, emp, References)

If something is still unclear refer to this answer about the Implements keyword in VBA

Mass update Excel workbooks

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , ,

File Processor
What does it do:

The file processor processes files ;) Yeah, You know that already » but, what does it do to them files while it's processing! That’s a better question and easier to answer for me.
First of all, the processor is looking for files in a specified path. Hey! You are given the power to provide the path to the directory with files! (Module called Main - we will get to it do not worry). It then creates a Collection of all files found which matches your search criteria(file extensions). Once that collectionis up and running the processor iterates/loops through all the files and executes Processes on each of the files! Ok, what are the Processes? The Processes are your own implementations of methods to be executed on files. You can call them methods or subs (i think that’s technically correct anyway - if not correct me in the comments). So, the processor executes some methods on the files then what!? Bum! That’s it what else would you expect the processor to do? It’s done!
Processes - if the above all makes sense, you probably wonder about the processes. They are all located in the Processor Module and they very easily manageable. You can: add your own, modify existing ones, and delete the ones you do not need.
The way the processor is designed it allows you to focus on the implementation of the processes instead of worrying about locating, filtering, handling opening, and closing the files.
If this sounds like something you would like to try out then move to the next step.
How to set it up:

This divides into 2 sections. The first one will be adding modules and classes, then copying andpasting some code! The second section will help you configure the variables.
Section 1
Open VBE (Visual Basic Editor) » ALT+F11. In the Project Explorer add
1 Class file
4 Modules
Rename your files as shown in the screenshot below:
Folder Structure
Now, copy the below code and paste it to each corresponding file!

Constructor module

Option Explicit

Public Function CreateFile(filePath As String, fileName As String) As file
 Dim fileObj As file
 Set fileObj = New file

fileObj.InitiateProperties filePath:=filePath, fileName:=fileName
 Set CreateFile = fileObj
End Function

Sub Initialize(Optional bool As Boolean)
 Application.ScreenUpdating = False
 Application.DisplayAlerts = False
End Sub

Public Sub Terminate(Optional bool As Boolean)
 Application.DisplayAlerts = True
 Application.ScreenUpdating = True
End Sub

FilesCollection module

Public Sub AddFiles(folderPath As String, fileExtension As String, ByRef files As Collection)

    Dim eFile As file
    Set eFile = CreateFile(folderPath, Dir(folderPath & fileExtension))
    files.Add eFile

    Do Until eFile.Name = ""
      Set eFile = CreateFile(folderPath, eFile.Name)
      files.Add eFile
      eFile.Name = Dir()
    Loop
    files.Remove (files.Count)
End Sub

Public Sub PrintFiles(ByRef files As Collection)
        £ "!¬ #Printing Files Details"
    Dim f As file
    For Each f In files
        ' Immediate Window Print out!
        ' CTRL + G  or VIEW >> IMMEDIATE WINDOW
        £ "File Name: " & s & s & s & s & f.Name
        £ "Extension: " & s & s & s & s & f.Extension
        £ "Member of Directory: " & s & f.MemberOf
        £ "Directory Path: " & s & s & f.Path
        £ "Full Path:" & s & s & s & s & f.FullPath
        £ Chr(32)
    Next
End Sub

Public Sub £(s$)
    Debug.Print s
End Sub

Main module

Option Explicit

Public Sub Main()
Initialize

    Dim folderPath As String
    Dim fileExtension As String

    folderPath = "..."   ' always \ at the end
    fileExtension = "xlsm"  ' "*" - all files

    Dim files As Collection
    Set files = New Collection

    AddFiles folderPath, sd & fileExtension, files

    £ "!¬ #Processor " & vbCrLf

    Dim f As file
    For Each f In files
        Process f
    Next

    PrintFiles files ' Immediate Window CTRL + G

Terminate
End Sub

Processor module

Option Explicit
Private book As Workbook ' opened workbook
Private ws As Worksheet
Private Const t As String = vbTab & "- " & vbTab

Public Static Sub Process(ByRef fileObj As file)
    'List of Processes to execute on each fileObj
    fOpen fileObj
    £ "Opened: " & book.Name

    £ t & "Unprotecting"
    'fUnprotect book
    £ t & "Making Changes"
    'fMakeChanges book
    £ t & "Protecting"
    'fProtect book
    £ t & "Closing"
    fClose book
    £ Chr(32)
End Sub

Private Sub fOpen(fileObj As file)
    Dim app As Application ' Excel.Application
    Set app = New Application
    app.Visible = False
    Set book = app.Workbooks.Open(fileObj.FullPath, ReadOnly:=True)
End Sub

Private Sub fUnprotect(wb As Workbook)
    ' your code to unprotect each worksheet goes here
    ' note: you can implement your own method of unprotecting
    '       ie. telling user what file it is and asking for password
    '           or importing it from a database, etc.
    ' below is just a sample

    ' note you have to provide the password as string
    For Each ws In wb.Sheets
        ws.Unprotect ("password_to_unprotect")
    Next
End Sub

Private Sub fMakeChanges(wb As Worksheet)
    '
    ' ###
    '
    ' see this below answer for extra help on implementation of this method
    ' http://stackoverflow.com/questions/16957334/trying-to-open-the-workbook-in-separate-instance/16959325#16959325
    '
    ' it will show you how to run a macro on a specific wb ran in another instance of excel!
    '
End Sub

Private Sub fProtect(wb As Workbook)
    ' same rules apply as fUnprotect()
    For Each ws In wb.Sheets
        ws.Protect ("password_to_protect")
    Next
End Sub

Private Sub fClose(wb As Workbook)
    wb.Save
    wb.Saved = True
    wb.Close
    Set wb = Nothing
End Sub

File class

Option Explicit

Private fName As String
Private fPath As String
Private fExtension As String
Private fIsMember As Boolean
Private fMemberOf As String

' Name property
Public Property Get Name() As String
    Name = fName
End Property
Public Property Let Name(Value As String)
    fName = Value
End Property

' Path property
Public Property Get Path() As String
    Path = fPath
End Property

' Full Path
Public Property Get FullPath() As String
    FullPath = Path & fName
End Property

' File Extension
Public Property Get Extension() As String
    Extension = fExtension
End Property

' Parent Directory
Public Property Get MemberOf() As String
    MemberOf = fMemberOf
End Property

' True if Parent Exist
' not for cases "C:\"
Private Function IsMember(filePath As String) As Boolean
    Dim check
    check = Split(filePath, "\")
    If UBound(check) > 2 Then IsMember = True
End Function

' returns the name of the root folder
Private Function getParent() As String
    Dim parent_ As Variant
    Dim filePath_ As String
    filePath_ = Path
    filePath_ = Left(filePath_, Len(filePath_) - 1)
    parent_ = Split(filePath_, "\")
    getParent = parent_(UBound(parent_))
End Function
' Sub accepts the constructor attributes
Public Sub InitiateProperties(filePath As String, fileName As String)
    fPath = filePath
    fName = fileName
    fExtension = Split(Name, ".")(1)
    fIsMember = IsMember(filePath)
    If fIsMember Then
        fMemberOf = getParent()
    Else
        fMemberOf = 0
    End If
End Sub

Section 2

Open Main module and locate folderPath = "..." ' always \ at the end
this is where you specify the path to your directory with files
for example:
folderPath = "\\NETWORKPLACE\SomeDocuments\Personal\mehow\" ' always \ at the end

Now the line below the folderPath allows use to filter the files you want to process
fileExtension = "xlsm" ' "*" - all files
please provide the proper file extension for the files you will be processing. Use * shift+8 to process all files in the specified folder
referer to supported file extensions for excel

For the next step please open the Processor module. Scroll all the way down and find fClose() sub

Private Sub fClose(wb As Workbook)
    wb.Save
    wb.Saved = True
    wb.Close
    Set wb = Nothing
End Sub

For now, please add a comment mark ' before the wb.Save line. Cause: we are still testing and do not want to accidentally override any data. fClose() should for now look like this:

Private Sub fClose(wb As Workbook)
    'wb.Save
    wb.Saved = True
    wb.Close
    Set wb = Nothing
End Sub

That’s all you need to do to #pre-configure your file processor! let’s move on to the next step!
How to use it:

Usage depends on the implementation of Processor Module.
Open the Processor module and look at the first static sub called Process. It’s straight forward » this sub only lists calls to the private methods implemented in the processor module. This Sub is the only one that the Main module is able to call. It’s declared static because it does not belong to an object. Instead, it executes taking an objects instance as parameter. Therefore Main module is callingProcess on each of the f (file) instances of the File class. In other words: for all files in the files collectionprocess method will be executed. Hope this makes sense!:)

fOpen fileObj - as you can see , this method opens each file and assigns it to the wb (Workbook object). From now, we will be passing the wb object to the next functions in the list.
fUnprotect wb - the purpose of this method is to Unprotect each worksheet in the openedworkbook. Look below at the implementation method – it’s very simple.

For Each ws In wb.Sheets
    ws.Unprotect ("password_to_unprotect")
Next

For each worksheet in the opened workbook sheets collection, unprotect each worksheet providing the password as a string parameter.

You will be following a very similar (if not the same) logic for all of your methods. I have written a few sample ones just to demonstrate how to make use of the Processor module. If at any time you get lost, please ask a new SO question providing the link to this little tutorial. It will be much easier to anyone to help you out.
Tips:

Immediate Window!
Make sure it’s displayed – as soon as you hit F5 (*or by clicking) to Run macro, make sure your eyes are focused on the Immediate Window. It will be printing information about the files and processes! This is definitely the best way to track what is going on as it shows live logs and results of some processes, structures and names.
A screenshot of the immediate window
Immediate

Do not forget to remove ' the comment from the Processor module fClose() method, otherwise you will not be saving any modifications in your files

Update multiple excel spreadsheets based on a master template

Tags

, , , , , , , , , , , , , , , , , , , , , , , , ,

Let’s create a master workbook that will store some formulas.

enter image description here

Note: you can store the formulas in cells if you stick a ' single quote before the = sign.

enter image description here

or you can format cell as Text then the formula will not be executed but stored as text.


It seems like you have multiple children(workbooks) to feed from the master workbook.

enter image description here

I think it should be each child’s responsibility to update itself. Think of it as a Server to Client relationship. Each client sends a request to the server to see if an update is available.

enter image description here

You can use the first cell of you master workbook to enter the last Updated Date and reserve one hidden cell or workbook property to store the last updated date in each child. If they are different you want to update the children formulas but if the same » just carry on.


You can simulate this sort of behaviour in Excel by taking advantage of the Workbook_Open() event of each children workbook.

What can you do?

Have a hidden spreadsheet to store the date of last update. Let’s say the sheet will be calledhiddenSheet and the cell to store the date will be A1. (NOTE: YOU DO NOT NEED TO CREATE THIS YET, IT WILL AUTOMATICALLY BE CREATED IF IT DOESN’T EXIST IN EACH CHILDREN WORKBOOK, see the below code)

So go into each children Workbook object module.

enter image description here

and copy-paste the below code.

Note: you need to update the path to your master workbook. See comments in the code. If you have a network location that everyone can access then place your master workbook there so everyone on your network can access it.

Note: to access a network location replace the C:\ with \\ double back-slashes.

Option Explicit

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

    ' check if the hiddenSheet exists
    Dim ws As Worksheet
    Dim hidden As Boolean
    For Each ws In Sheets
        If StrComp(ws.Name, "hiddenSheet", vbTextCompare) = 0 Then
            hidden = True
        End If
    Next
    ' if hidden already exists then set reference to it
    If hidden Then
        Set ws = Sheets("hiddenSheet")
        ws.Visible = xlSheetHidden
    ' if hidden does not exist then create it
    Else
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "hiddenSheet"
        Set ws = Sheets(Sheets.Count)
        ws.Visible = xlSheetHidden
    End If

    ' compare dates
    ws.Range("A1:B1").NumberFormat = "m/d/yyyy"

'
' UPDATE PATH
'
' this is where you need the path to master workbook
    ws.Range("B1").Formula = "='C:\Users\" & Environ$("username") & "\Desktop\[master.xlsm]Sheet1'!A1"
'
'
'

    Dim d1 As Date
    Dim s1 As String
    s1 = IIf(IsEmpty(ws.Range("A1")), ws.Range("B1"), ws.Range("A1"))

    Dim d2 As Date
    Dim s2 As String
    s2 = ws.Range("B1")

    ws.Range("A1") = IIf(s1 = s2, s2, s1)

    d1 = CDate(Year(s1) & "/" & Month(s1) & "/" & Day(s1))
    d2 = CDate(Year(s2) & "/" & Month(s2) & "/" & Day(s2))

    ' check whether there is any difference in DAYS between the dates
    If DateDiff("d", d2, d1) > 0 Then
        '
        '
        '
        '
        '
        '
        ' update formulas
        ' finish this section yourself
        '
        ' so for example
        ' set reference to the sheet you want to update
        ' pull the formulas from the master and replace them in this child workbook
        '
        ' I can't implement this for you cause I would have no idea what goes where
        '
        '
        '
        '

    Else
        ' you dont want to update so exit
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Application.DisplayAlerts = True
        ' msgbox FORMULAS ARE UP TO DATE!
        MsgBox "The formulas are up to date! No updating is required!"
        Exit Sub
    End IfApplication.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub 

Note: find comments where the path to the master is used and modify it so it point to your master.xlsm.


Summary

You have created a master workbook to store all the formulas that will be distributed across all your children workbooks. The master workbook stores the date (and time) of the latest update in cell A1.

Each children workbook needs to have the Workbook_Open() event updated with the above code + you need to update the path to the master workbook. The Workbook_Open() event will pull the date of the latest update of the master workbook and compare it with its own date. If the dates are the same then a msgbox will be displayed saying that formulas are up to date and no more work is required. If the dates are different (meaning that the date in the master workbook is newer than the one currently stored in the children) the children will need more code to execute to pull formulas from the master workbook.

The implementation of the update process it’s something you will have to tackle on your own because I can’t see the structure of your children and do not know which formulas to update.

I hope this helps :)

Various ways to pull data out of a closed or opened workbook using Excel formulas and VBA

Tags

, , , , , , , , , , ,

Let’s walk through a few ways of pulling data out of workbooks using VBA and Excel formulas.

SETUP

Create two workbooks for this exercise. Name them: book1 and book2 and save both as Macro Enabled Workbooks ( *.xlsm extension ).

Open both workbooks


FORMULAS

when both workbooks are open

in book2 go to cell A1 and type: data in cell one in book2

Now go to any empty cell in book1 and type this formula:

=[Book2.xlsm]Sheet1!A1

this formula pulls the data out of book2 that’s already open using hardcorded values.
We can achieve the same thing using variables(Text in Cells) in book1 type:

A1 = book2
A2 = Sheet1
A3 = A1

enter image description here

Now go to any empty cell in book1 and type the formula

=INDIRECT("'["&A1&"]"&A2&"'!"&A3)

The indirect pulls values from the Cells and replaces them creating a string which in the end is equal to=[Book2.xlsm]Sheet1!A1

So, instead of hard-coding the names of other opened workbooks, sheets, or ranges you can use values you entered in any other cell in your book1.

There is a downside to the =INDIRECT() function. It does NOT work with closed workbooks.


when one workbook is closed

Save and Close book2 and go back to your book1. Place your mouse in the cell where you’ve used the =INDIRECT() function and hit Return.

what do you see?!

First of all, a proof that =INDIRECT() does not work with closed workbooks because you get a #REFerror in the cell that used the =INDIRECT() function

enter image description here

Well, this mean you can’t use =INDIRECT() with closed workbooks..

Let’s have a look at the other cell that you used where you’ve entered =[Book2.xlsm]Sheet1!A1. Do the same, place mouse cursor in the cell and hit Return. The cell still displays the value from book2but look at your formula bar. Whatever you have previously entered has been overridden by Excel.

It’s changed to

='C:\Users\your_username\Desktop\[Book2.xlsm]Sheet1'!A1

note: your_username is just a place holder for your actual Windows username. The point is that the full path has been added to the formula which allows you to still pull cells from the workbook, even when it’s closed!

The downside of this approach is that the formula has to be hard-coded meaning you can’t replace parts of it with values from your cells like you could when the workbook was opened.

There is an external add-in you can use to extend the capability of INDIRECT(google: INDIRECT.EXT) but I will show you a way using UDF and VBA to achieve the same thing without the need to use external add-ins.

VBA

when both workbooks are open

To pull any data from an opened workbook you need to find its name first. Make sure both book1 andbook2 are open.

Open VBE Visual Basic Editor and insert a new standard Module

Copy and paste the below code

Sub OpenWorkbooks()

    Dim wb As Workbook

    For Each wb In Application.Workbooks
        Debug.Print wb.Name, wb.FullName
    Next

End Sub

Click View from menu bar and select Immediate Window or hit CTRL+G

Run the macro with F5 or little Play button from the menu bar.

Check out the results in the Immediate Window.

wb.name shows currently opened workbooks names within the instance

wb.FullName shows the full path to each opened workbook

enter image description here

Since you know the names of the currently opened workbooks you can assign the reference to each one like this:

Sub ReferencedWorkbooks()

    Dim b1 As Workbook
    Dim b2 As Workbook

    Set b1 = Workbooks("book1")
    Set b2 = Workbooks("book2")

    Debug.Print b1.Name, b1.FullName
    Debug.Print b2.Name, b1.FullName
End Sub

We assign references to Workbook objects to be able to work with them using Workbook variables. It will all makes sense really soon.

Now, let’s say we want to pull data from book2 and display it in cell C1 of book1

We need to create more references > variables which represent Sheets and Ranges!

This is very similar to assigning references to Workbooks.

Sub ReferencedWorkbooks()

    Dim b1 As Workbook
    Dim b2 As Workbook

    Set b1 = Workbooks("book1")
    Set b2 = Workbooks("book2")

    Dim sht1 As Worksheet
    Dim sht2 As Worksheet

    Set sht1 = b1.Sheets(1)
    Set sht2 = b2.Sheets("Sheet1")

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = b1.sht1.Range("C1")
    Set rng2 = sht2.Range("A1")

End Sub

The reason to use b1.Sheets(1) is to make sure the sht1 variable is connected to the sheet inbook1 and not any other workbook. This is called qualifying.

b1.Sheets(1) is exactly the same as b1.Sheets("Sheet1") as long as the first sheet in thebook1 is actually named Sheet1. The Sheets() has two overloads, you can either use sheets indexes or names. This is another topic – or refer to VBA Syntax for more info.

sht1.Range("C1") point to range C1 in Sheet1 in book1. It’s like reading it back-to-front to make logical sense out of it.

you do not need to qualify b1 because it already belongs to book1.

The best explanation for this is how you are going to use your Range objects now.

They are qualified to point to their own books, sheets, and cells.

add Debug.Print rng2 before the End Sub and run the macro. Check the Immediate Window for results.

See, you do not have to give it a full name likeWorkbooks("book2).Sheets("Sheet1").Range("A1"), simply use it as a rng2 object :)

Now that we have our rng1 and rng2 Range objects assigned to point to specific cells we can manipulate the data the refer to!

Add rng1 = rng2 to the code just before the end of the sub statement.

Also, add a Debug.Print "rng1 holds: "; rng1 & vbNewLine & "rng2 holds: " & rng2 line after the rng1 assignment.

Sub ReferencedWorkbooks()

    Dim b1 As Workbook
    Dim b2 As Workbook

    Set b1 = Workbooks("book1")
    Set b2 = Workbooks("book2")

    Dim sht1 As Worksheet
    Dim sht2 As Worksheet

    Set sht1 = b1.Sheets(1)
    Set sht2 = b2.Sheets("Sheet1")

    Dim rng1 As Range
    Dim rng2 As Range

    Set rng1 = sht1.Range("C1")
    Set rng2 = sht2.Range("A1")

    rng1 = rng2
    Debug.Print "rng1 holds: "; rng1 & vbNewLine & "rng2 holds: " & rng2
End Sub

Immediate Window

enter image description here

You have successfully grabbed data from book2 and assigned to cell C1 in book1.

enter image description here


Closed

Working with closed workbooks is a bit more difficult but let’s start off with the most basic example.

Sub OpenWorkbookToPullData()

    Dim path As String
    path = "C:\users\" & Environ$("username") & "\desktop\book2.xlsm"

    Dim currentWb As Workbook
    Set currentWb = ThisWorkbook

    Dim openWb As Workbook
    Set openWb = Workbooks.Open(path)

    Dim openWs As Worksheet
    Set openWs = openWb.Sheets("Sheet1")

    currentWb.Sheets("Sheet1").Range("A1") = openWs.Range("A1")

    'openWb.Close (False)

End Sub

You need to know the path to the workbook you would like to open. Assign it to the path variable if it’s different then what already is in the code.

Set currentWB = ThisWorkbook sets reference to the currently opened workbook. You need that in order to exchange information between the one you will open and the current one. What happens is : the workbook that you open becomes the active workbook so you need to retain references to the one that executes the macro in order to go back to it.

Set openWB = Workbooks.Open(path) opens the new workbook taking the path as the parameter.

You should by now understand the rest of code. If you do not, please go back and read previous section.

I have commented out the openWb.Close (False) line to leave the workbook open so you can actually see that the macro does open a workbook. If your computer was super fast you may have missed the flicker in between the time when the book2 was opened. Uncomment the line and see what happens.

You can now create new variables of Worksheet type and Range type and following the rules from the previous section assign the references in order to exchange/manipulate data between the workbooks.


Additionally, consider the alternative methods for getting data from closed workbooks.


ExecuteMacro4Excel

Sub ExecMacro4Excel()
    Dim path As String
    Dim workbookName As String
    Dim worksheetName As String
    Dim cell As String
    Dim returnedValue As String

    path = "C:\Users\" & Environ$("username") & "\Desktop\"
    workbookName = "book2.xlsm"
    worksheetName = "Sheet1"
    cell = "A1"

    returnedValue = "'" & path & "[" & workbookName & "]" & _
          worksheetName & "'!" & Range(cell).Address(True, True, -4150)

    MsgBox ExecuteExcel4Macro(returnedValue)
End Sub

using ADODB

There is a way of querying an excel spreadsheet using SQL and ADODB.

You have to add references to your VBA project.

In a completely new workbook open the VBE ALT+F11 click on Tools » References »Microsoft ActiveX Data Objects 6.1

edit fileName and provide the full path to the workbook with the data

run the below code to receive the data you only interested in

Sub Pull_Data_from_Excel_with_ADODB()

    Dim cnStr As String
    Dim rs As ADODB.Recordset
    Dim query As String

    Dim fileName As String
    fileName = "C:\...\Filename.xlsm"

    cnStr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & fileName & ";" & _
               "Extended Properties=Excel 12.0"

    'query = "SELECT * FROM [Sheet1$]"
    query = "SELECT * FROM [Sheet1$] WHERE [Day] = 'Saturday'"

    Set rs = New ADODB.Recordset
    rs.Open query, cnStr, adOpenUnspecified, adLockUnspecified

    Cells.Clear
    Range("A2").CopyFromRecordset rs    Dim cell As Range, i As Long
    With Range("A1").CurrentRegion
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i
        .EntireColumn.AutoFit
    End With
End Sub

note: if you know SQL you can modify the query and the WHERE clause to get exactly what you want

Highlight or delete entire row if more than one word matches cell content

Tags

, , , , , , , , , , , , , , , , , , , , ,

This macro searches entire used range on the active sheet. It looks for the words that you specify in the searchTerms array. You separate them by comma and wrap each word in double quotes. You can add as many words as you want.

The below code currently will delete the entire row if there has been a match. If you want to only highlight the rows then replace

Rows(rowsToDelete(v)).Delete

with

Rows(rowsToDelete(v)).Interior.Color = RGB(255, 0, 0)

Sample:

before

enter image description here

macro to run

Option Explicit

Sub RemoveRowsBasedOnArrayCondition()
    Dim searchTerms As Variant
    searchTerms = Array("tag1", "tag2", "tag3")

    ReDim rowsToDelete(0) As String

    Dim allRange As Range
    Set allRange = ActiveSheet.UsedRange

    Dim cell As Range, word As Variant
    For Each cell In allRange
        For Each word In searchTerms
            If InStr(1, cell, word, vbTextCompare) Then
                rowsToDelete(UBound(rowsToDelete)) = CStr(cell.Row)
                ReDim Preserve rowsToDelete(UBound(rowsToDelete) + 1)
            End If
        Next word
    Next cell
    ReDim Preserve rowsToDelete(UBound(rowsToDelete) - 1)
    RemoveDuplicate rowsToDelete
    Dim v As Long
    For v = UBound(rowsToDelete) To LBound(rowsToDelete) Step -1
        Rows(rowsToDelete(v)).Delete
    Next
End SubSub RemoveDuplicate(ByRef StringArray() As String)
    Dim lowBound$, UpBound&, A&, B&, cur&, tempArray() As String
    If (Not StringArray) = True Then Exit Sub
    lowBound = LBound(StringArray): UpBound = UBound(StringArray)
    ReDim tempArray(lowBound To UpBound)
    cur = lowBound: tempArray(cur) = StringArray(lowBound)
    For A = lowBound + 1 To UpBound
        For B = lowBound To cur
            If LenB(tempArray(B)) = LenB(StringArray(A)) Then
                If InStrB(1, StringArray(A), tempArray(B), vbBinaryCompare) = 1 Then Exit For
            End If
        Next B
        If B > cur Then cur = B: tempArray(cur) = StringArray(A)
    Next A
    ReDim Preserve tempArray(lowBound To cur): StringArray = tempArray
End Sub

and after

enter image description here

Follow

Get every new post delivered to your Inbox.