vba

How to create a function in Excel to validate cells using RegExs

I have a long spreadsheet with tariff codes that I need to validate and I would like to create a function with RegEx to do it automatically (this is a daily task that I will have to do for the following months and I would like to automatize) For example in Column A, I have the following data: CODE 1000.00.00 1000.10.00 1020.12.99 ... ...

Bitwise And with Large Numbers in VBA

I keep getting an Overflow on the bitwise and in this first function. I fixed the other overflows by converting from Long to Currency (still seems weird), but I can't get this And to work. Any ideas? I'm just trying to convert some IP addresses to CIDRs and calculate some host numbers. Option Explicit Public Function ConvertMaskToCIDR...

VB6 Attach To Process - Com Exposed VBA

I ve been given a VBA spreadsheet which calls functions that are written in VB6 and 'COM' exposed. Can anyone indicate: How I can find the location of the dll / binary that VBA actually calls in the COMP calls? How I can attach a debugger to VB and get hit when the function os called. I come from a C sharp background so would reall...

VBA inheritance, analog of super

For example I have class A which implements class B ---class A---- implements B public sub B_do() end sub --class B---- public sub do() end sub How can I call do() from A? (super.do()) So, how I can define some common variable for both classes? Now I can inherit only functions, sub and properties... added: same question http://s...

Excel combo box problem

I have a form in Excel with a combo box control. I want the values to be filled from a database table when the combo box is opened using what has already been typed in as a LIKE criteria. This is the code I have so far for the DropButtonClick event to achieve this. Private Sub cboVariety_DropButtonClick() Static search_text As Strin...

Best way to define a large vba string - i.e. heredoc equivalent?

How should I define large strings in VBA? Is there a better way than coding something like the below? Dim largeString as String largeString = "This is a long block of text that I want to fill " & _ "into a form field. I need to make sure I pay attention " & _ "to spacing and carriage return issues while doing...

The x command does not correctly when invoked under SASWorkspaceManager.WorkspaceManager?

I have written some SAS code that calls R via the x command (I am using SAS 9.1.3 so there is no native SAS interface to R). OPTIONS XWAIT XSYNC; X """&r_path."" --no-save --quiet < ""&out_code_folder.\code.r"" > ""&out_code_folder.\abba.log"""; This code works correctly when I run it in the SAS IDE but when I try to run the same cod...

How do I change the Excel logo that appears at the top left hand corner of my Excel session?

In Excel VBA I can use application.Caption = "mytitle" to change the title of the Excel session. I want to be able to go one step further and actually change the Excel icon to an icon of my own choosing. Can this be done? Please help. ...

Getting info out of XML document in VBA

I'm using MSXML2 to load an XML file (a feed from Google Analytics). I now need to get data out of it, but I can't figure out how. This is the XML: <?xml version='1.0' encoding='utf-8'?> <feed xmlns='http://www.w3.org/2005/Atom' xmlns:dxp='http://schemas.google.com/analytics/2009' xmlns:openSearch='http://a9.com/-/spec/opensearch/1.1/...

Dynamic JNLP Generation and invocation from Excel

I have an application which is invoked via Java Webstart. Opening it via the Webstart link works without any issue. I also have an application based on Excel that generates files (via vba) which can then be opened by the program that starts via Webstart. What I would like to do is have a button that invokes the Webstart application and...

Passing Null Values to Web Services Via SOAP Using Excel VBA

I have a Web Service which has one WebMethod with nullable parameters: [WebMethod] public void SaveValue(double? Real, double? Meta){ //Do the magic... } In my Excel I'm calling the WebMethod as below: Dim WebSvc As MSSOAPLib.SoapClient Set WebSvc = New SoapClient Call WebSvc.mssoapinit("http://localhost:10618/WebService.asmx?wsdl"...

How can I change the workbook icon in Excel?

I managed to write some code to change the Excel application icon. See this post. As a follow up how can I change the icon that sits directly below the Excel application icon? I want to change the workbook icon as well. Any ideas? Thanks! ...

RegEx in VBA: Break a complex string into multiple tokens?

EDIT: Two additional token types added. Hi, I am trying to parse a line in a mmCIF Protein file into separate tokens using Excel 2000/2003. Worst case it COULD look something like this: token1 token2 "token's 1a',1b'" 'token4"5"' 12 23.2 ? . 'token' tok'en to"ken Which should become the following tokens: token1 token2 token's 1a',1b'...

Process other application activity in long loop

I have a long running loop in an unfortunately large excel file that I'm scripting over. I seem to remember a keyword you could put in a loop statement that would cause the program to sort of temporarily "pause" the loop and process other application input, like...mouse movement, screen redraws and such. This might be a leftover memory ...

Access VBA verticle scroll bar

I am building a quite complex form in Access. I would like to have a vertical scroll bar control that will scroll through a list of pictures. What I want the scroll bar to actually do is change the pictures in the picture control when it is scrolled. Is there a way to insert a vertical scroll bar into an Access form where the scroll eve...

excel vba get range of user selected range by mouse

Hi All, this is not the usedrange issue. For example in excel user selects a range (possibly empty) using mouse, let's say B4:C12. And let's say after this without deselecting the range user presses the macro, and macro should tell B4:C12. Can anyone show example? Thanks a lot in advance! the macro should be smth along the lines of the ...

Generating consecutive invoice number in MS Access VBA, # restarting every year

I need to generate unique invoice numbers for my Access (2010) database. The numbers should be in the format year+sequential number, e.g. 20101447 for the 1447th invoice of 2010. I looked around for a while, but a lot of the Google results suggest using an autonumber and I'm quite certain that's not a very solid way of doing it. (because...

XML/XSLT/Access/VBA: how can i merge all child elements (even unknown elements) into one, before importing to Access database?

CURRENT XML: <?xml version="1.0"?> <form1> <page1> <first_name></first_name> <last_name></last_name> . . </page1> <page2> <address></address> <phone_number></phone_number> . . </page2> <page3> <company_name></company_name> <job_title></job_title> . ....

Convert row with columns of data into column with multiple rows in Excel

I hv rows of data:- TAG SKU SIZE GRADE LOCATION A001 123 12 A X1 A002 789 13 B X3 A003 456 15 C X5 I need to convert it into:- A001 123 SIZE 12 A001 123 GRADE A A001 123 LOCATION X1 A002 789 SIZE 13 A002 789 GRADE B A002 789 LOCATION X3 A003 456 SIZE 15 A003 ...

Creating a dashboard in exel

Hi guys, I am looking at creating a dashboard in excel using several key performance indicators to plot a graph, the data would just be collected from another sheet in excel. I understand that you can do this in conjunction with macros but my experience with macros is limited although I am willing to learn. Any help would be great! Th...