Tag Archives: vba

Excel vba : Type range and cell

Questions: I am bit confused by cell and range in vba (excel). Logically, i could think a cell as a range with size = 1; and I think it is easy to make a range out of a cell. If I read the api of EntireRow property here it operates on range. But, the below… Read More »

Excel VBA Project has generated multiple Workbook objects

Questions: I am responsible for a very large Excel 2010 spreadsheet with links to all sorts of external data sources including Bloomberg, 65 worksheets with vba modules and references to other vba add-ins. I have noticed that the VBA project has acquired multiple Workbook Objects. There’s the standard ThisWorkbook. However, a number of the worksheets… Read More »

How to access contact groups in Excel VBA?

Questions: I am building an Excel add-in that sends the active workbook as an attachment in an Outlook email template to a specific Contact Group. I’ve gotten the first two parts to work with the code below, but I am not sure how to set the .TO field to a contact group. Public Sub Mail_Reports()… Read More »

How to find/extract an HTML “font” element with attribute size=“+1” using Excel VBA

Questions: I want to extract a U.S. Patent title from a url like http://patft.uspto.gov/netacgi/nph-Parser?Sect1=PTO1&Sect2=HITOFF&d=PALL&p=1&u=%2Fnetahtml%2FPTO%2Fsrchnum.htm&r=1&f=G&l=50&s1=6293874.PN.&OS=PN/6293874&RS=PN/6293874 (Update: as pointed out the comments, the patent title is not labeled “Title;” however, it consistently appears by itself above “Abstract” on the web page.) In most cases it is in the 7th child element of “body” or the 3rd “font”… Read More »

Excel VBA to C#

Questions: I am converting some codes from Excel VBA to C# and run into this problem. I am not sure the equivalent of this code in c#. Intellisence wasn’t very helpful 🙁 Selection.ShapeRange.Adjustments.Item(1) = 90 I managed to get as far as Adjustment in c# but there is no Item property. Answers: Per MSDN it… Read More »

Tables interfere with VBA range variables depending on scope

Questions: An Excel file includes VBA-coded user-defined functions (UDFs) that are deployed in tables (VBA listobjects). Now, for reasons that escape me, if the UDF module contains Range variables that are declared outside the scope of any sub or function, I get a very dramatic warning when the file is opened: “Automatic error — Catastrophic… Read More »

Comparing 2 lists in Excel with VBA Regex

Questions: I want to use them to compare two lists (columns) in Excel to find matches. As this is quite a complex operation, I have performed it in the past, using several different functions (non-VBA) in Excel, but it has proved to be awkward at best, and so I wanted to try an all-in-one VBA… Read More »

Bug in VBA DateValue() in VBA Excel?

Questions: When I use the formula datevalue(“01/01/1900”) I get 1 and formatted as a date it shows as 01/01/1900 When I use VBA .Range(“A1”).Value = DateValue(“01/01/1900″) it shows up as “02/01/1900” in the cell How is this possible? If i use for example .Range(“A1”).Value = DateValue(“01/01/1901”) it works fine! Head Melted!!! Microsoft state that –… Read More »

Checking if a nested dictionary key exists in VBA

Questions: I am trying to use a dictionary of dictionaries in Excel VBA. What I’m trying to find out is whether the nested dictionary has a key already, and if not, add it. My data looks like the following: Country, Customer, Purchased US, Alan, Lawnmower US, Alan, Hammer US, Karen, Donkey US, Simon, Mustang MX,… Read More »