option explicit

  1. M

    Can't use Option Explicit and Preprocessor constants together?

    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...
  2. Jyggalag

    How do you make VBA code run automatically?

    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!
  3. L

    Option Explicit causes names defined in a worksheet to give object needed error in VBA

    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...
  4. M

    Running macro for another workbook - dosen't wrok

    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 &...
  5. W

    How to troubleshoot undefined variable when everything defined

    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...
  6. J

    "variable not defined" error with "sub" highlighted

    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...
  7. S

    Copy/paste without clipboard "Object required" ?

    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")...
  8. E

    Conditional Formatting with VBA and xlExpression Formula1: with VBA Variables and Text

    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"...
  9. T

    How to duplicate option explicit VBA code excel 2010

    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...
  10. J

    Option Explicit causes error with CopyFromRecordset?!? Excel 2007, SQL Server 2008 R2

    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...
  11. R

    variable not defined issue

    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...
  12. V

    VBA error excel to website submission

    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...
  13. A

    Macro won't run with Option Explicit

    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...
  14. D

    Option Explicit

    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...
  15. Colin Legg

    Who uses Option Explicit?

    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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back