TechTalk

Recently i had to call .NET code from my VBA Macro in order to do something not available out of the box using VBA. i searched a lot of content on the web and found this wonderful web page http://www.geeksengine.com/article/create-dll.html The article clearly explains how to create a dll and reference it in VBA. There were a some issues I hit upon while following the instructions mentioned in the link 1. In my code I had the following line of code     Set f1 = New myplugin.Class1 The VBA compiler complained invalid use of New Keyword. I then modified my code to the following based on a colleague's suggestion Set f1 = CreateObject("myplugin.Class1") and then it worked fine. 2. On trying to create an instance of the .NET class in my VBA code When distributing the dll by using following link, I hit upon an issue http://www.geeksengine.com/article/register-dll.html I was getting the following error on target machines RegAsm: error RA0000...

This blog post is an extract of VSTO for Dummies. VSTO isn' t a replacement technology for Visual Basic for Applications (VBA). VSTO is a set of tools that you can use with Visual Studio to supercharge Office. Put simply, VSTO offers tools to - 1. Build add-ins 2. Build customised documents for Office Applications like Word and Excel. Add your custom functionality to existing Office applications, via add-ins. Leverage all the existing Office functionality in your applications, like Word document layout and Excel formulas, through communicating with those add-ins Make documents that integrate fully with existing applications and databases Use Microsoft SharePoint to increase communications between knowledge workers with add-ins and documents that communicate. At their core, Customized Documents are document-level only, while add-ins are application-level and can apply to all documents used by that installation of Office. Four important features are availabl...

The following VBA procedure can be used to apply borders to a range of cells Public Sub ApplyBorder(ToRange As Range, intColorIndex As Integer)      On Error Resume Next      With ToRange         With .Borders(xlDiagonalDown)             .LineStyle = xlNone         End With         With .Borders(xlDiagonalUp)             .LineStyle = xlNone         End With                 With .Borders(xlEdgeLeft)             .LineStyle = xlContinuous             .Weight = xlThin        ...

If you want to open a website and go through the results of a webpage using VBA you can achieve it by first including a reference to Microsoft HTML Object Library in your VBA editor. The following snippet of code should be a good starting point of how you can achieve the same Sub GoToWebSiteAndPlayAround() Dim appIE As Object ' InternetExplorer.Application Dim sURL As String Application.ScreenUpdating = False Set appIE = CreateObject("InternetExplorer.Application") 'URL with the search term 'Cancer' at Science sURL = "http://www.google.com?q=vba" 'this URL to be replaced by your target web page With appIE .navigate sURL ' uncomment the line below if you want to watch the code execute, or for debugging '.Visible = True End With ' loop until the page finishes loading Do While appIE.readyState <> 4 DoEvents Loop 'Get info from HTML by ID and Name Dim outerDiv, innerSpan, requiredtext, HTMLDoc Dim ...