option explicit

Hello, All, I'm trying to use Option Explicit alongside preprocessor constants, like in the dummy code below: Option Explicit #Const TEST_CONST = 123 Sub TEST_MOD() Debug.Print TEST_CONST End Sub When I try to run TEST_MOD, I get a "Compile Error: Variable not defined." It does the...

Hi all, I am very new to coding in VBA and am trying to figure out how to make my code run automatically. I want to copy paste values from one cell to another, but nothing happens with my current code, any idea why? Thank you!

Here is my issue: Adding Option Explicit to my code module is causing an 'object needed' error when referencing named ranges within workbooks. Removing Option Explicit allows the code to run as expected. The line that errors out is Set Basewkst = [Criteria] where Criteria is the VBA name of a...

Hi I have a macro that is to run three macros in another file Two of them run fine but the third I get an error on This is the code for running the macros Workbooks.Open Filename:=FileNameFolder & xlsmFile Application.Run "'" & xlsmFile &...

Hi, I just started VBA this year and am struggling to troubleshoot...hoping you can help. I have some code I upgraded today which filters a table and copies the first column to another sheet. It uses a dynamic named range as the header to find the right column to filter (new today). It uses...

Hi all, I have a loop code as per below, which works fine in the original spreadsheet for which it was written. Although the row containing "Sub Matrix2DB()" is highlighted yellow. BUT when copying to a new spreadsheet, with all of the sheets, columns and rows set out as required in the macro...

Windows 7 excel 2007 Im real new at VBA (but do know some PHP if that helps lol) ANYWAY a website had a way to copy and past without using the clipboard link here: "Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros" Sub Testing() Sheet1.Range("A1").Copy Destination:=Sheet2.Range("B1")...

Hello Mr. Excel! I want to start by saying a quick thank you! I love this site. I have tried to find answers in previous posts but I haven't found anything to find material that applies directly here, yet. I've been using VBA to new worksheets that compile information from a "Source"...

I have used the information from another post but this is a new type of enquiry. Originally Posted by Ruddles Create the "timestamp info" worksheet by hand, make the top row bold and freeze it. Paste the following code into the code module for your main worksheet - the one where B321...

I've encountered an odd error in an Excel 2007 VBA sub. The code queries a SQL Server 2008 R2 database, then copies the recordset to a worksheet in the Excel workbook. If I don't specify Option Explicit, it works fine, but if I add Option Explicit I get the error "Class does not support...

Morning, I'm getting a Variable Not Defined error and would be grateful of some help, or to be pointed in the right direction please. in my workbook_open I have the following code Dim workround As Integer workround = 0 in one of my sheets worksheet_change I have If workround = 0 then...

Hello all, What I am trying to do is to take several parts of data from an excel sheet and move them to a ticketing system we use to log tickets. So far i have: Sub Fill_Form() Set IE = CreateObject("InternetExplorer.Application") With IE .Visible = True ' im using facebook as an example...

I have a macro that changes the font color and font size of a cell. It is located in a module that contains several other macros. If I declare Option Explicit in that module, the macro will not run. I get an "compile error" message stating that the variable "font_size" is is not defined. If I...

Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> <o:p></o:p> I am working on a template for a report, and I got stuck with something.<o:p></o:p> <o:p> </o:p> I have the function "Option Explicit" activated, and the last of the macros must search...

Following on from the Personal.xls poll, I thought it would be interesting to gauge how many VBA'ers on this board use Option Explicit to enforce variable declaration. I thought there might be a trend relating to how long peeps have been coding in VBA so I've broken the poll into categories...