VBA With Compile Error: Variable Not Defined

May 27, 2008

I've used Excel for many years, but finally am learning VBA, using Excel Progamming for Dummies by John Walkenbach. Excel is version 2000.

I've searched extensively on Google and on the MrExcel site for the solution to my problem, but still haven't found it . Any direction would be most appreciated.

I'm trying to execute the examples in the book, and have gotten the error message "Compile error: Variable not defined" with "MyString =" highlighted with several of the examples. Could someone please tell me where I'm going astray?

Following is the latest subroutine I typed into the module, from page 124:

Option Explicit

Sub GetLength()
MyString = "Hello World"
StringLength = Len(MyString)
MsgBox StringLength
End Sub

View 9 Replies


ADVERTISEMENT

Compile Error - Variable Not Defined

Aug 12, 2014

New to VBA and just trying to make some edits to some existing code. I have basically copied a pre-existing form and module and changed names from "Appendix" to "Drawing" as I am trying to replicate what the piece of code already produces for a table of appendices, for my drawings.

However, when I try and run the form I get Compile error: Variable not defined with lstDrawings highlighted.

[Code] ...........

Is this something that should be defined in my global module which I am missing?

View 3 Replies View Related

Compile Error - Variable Not Defined

Jan 21, 2012

The following code has been used previously to enter data from a userform to a worksheet without a problem. However, since I added some new bits of code I am getting a compile error with the message variable not defined.

Here is part of what I have so far and the bit that is highlighted after the error comes up is the 'Set ws' line;

Code:
Private Sub CommandButton1_Click()
Worksheets("Duties").Range("C5") = txtdate
Worksheets("Duties").Range("H5") = txtarea
Worksheets("Duties").Range("N5") = txttea
Set ws = Worksheets("Duties")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

The code I have recently added:

Code:
Private Sub UserForm_Initialize()
Set wsRes = Worksheets("Resources")
With wsRes
.Range("B1", .Range("B" & Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("L1"), True
With .Range("L2", .Range("L" & Rows.Count).End(xlUp))
cboTeam.List = .Value
.EntireColumn.Clear

[Code] .......

View 8 Replies View Related

Compile Error - Variable Not Defined

May 6, 2009

I keep getting 'compile error - variable not defined' in the following

Sub LockIt()
TextBox1 = ""
End Sub

View 9 Replies View Related

Getting A Compile Error Variable Not Defined

Jan 13, 2010

It works as a regular Module but when I put it into a "Private Sub" for a button its not working.

Basically I have a master sheet that I create Tests from. I push a button and it creates a Test for others to take. I want the macro to transfer from the Master to these so when they are done I can press a button and upload the answers to a tracking sheet.

It errors on the strSecondFile

Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim NAME As String

'strFirstFile = ActiveWorkbook
strSecondFile = Application.GetOpenFilename
Set wbk = ActiveWorkbook 'Workbooks.Open(strFirstFile)
NAME = Range("A6").Value

View 9 Replies View Related

Compile Error: Variable Not Defined

Jul 18, 2006

I came across a weird situation using a standard print macro. One of my users could not use the print macro due to a compile error. The row that was highlighted was

.PrintErrors = xlPrintErrorsDisplayed

the error states that the variable is not defined. I removed that line of code and everything prints fine.

I tested this code out on excel 97 and it worked; is this sensitive to OS version? Will leaving this line of code effect anything big?

View 9 Replies View Related

Compile Error - Variable Not Defined Involving Multiple If Statements

Jan 27, 2014

I am trying to put two subs into a macro. They work individually, but when I put them together, I get a "Compile Error: Variable Not Defined" Message. I was told I have to define the variable in the sub multipleif(), but I am not certain as to how to do this. The error message highlights the line I have bolded below.

VB:
Option Explicit
Private Sub CommandButton1_Click()
Dim mth As Variant, txt As Variant, des As Variant, wdt As Variant, I, Cell

[Code]....

View 5 Replies View Related

Excel 2010 :: User-Defined Type Not Defined Compile Error When Creating PowerPoint Using VBA

Jul 29, 2014

I am using Microsoft Excel 2010 and Microsoft VBA 7.0 on my system. I would like to eventually create a PowerPoint and insert charts generated in the Excel workbook. In the meantime, I cannot get the basic PowerPoint created.

The line in red is highlighted blue when the compile error "User-defined type not defined" message box appears.

Public Sub TryAgain() Dim myPowerPoint As PowerPoint.Application
'
' do nothing for now
'End Sub

I have set the references such that Microsoft Project 14.0 Object Library is indeed checked. The Excel file only contains this code in a module. All sheets are blank. Nothing else is written yet.

View 1 Replies View Related

Get Compile Error - Sub Or Function Not Defined?

Apr 9, 2014

Trring to use function edate() in VBA macro, I've activated atpvbaen.xls in Tools/Register but get compile error:"Sub or function not defined".

View 5 Replies View Related

Compile Error / Sub Or Function Not Defined

Jul 9, 2014

Have two worksheets in same workbook. First worksheet is "ReArrangedAddr" Which basically has a command button to click to run a "Sub" behind the second worksheet "Orig SH Register". When I click on button on first worksheet, I get error "Compile Error: Sub or Function not defined"

* * * * * code behind command button * * * * *

View 14 Replies View Related

Compile Error / Sub Or Function Not Defined

May 17, 2012

I have a two set of Macros in a workbook. One is to create a command button on a sheet and other macro will run onece the created commond button is clicked.

Macro runs fine. Command button gets created, but when I click the command button to run another macro it gives error "Compile Error: Sub or Function not defined" highlighting the code "Call Add" at the code entered for sheet. This code is added by macro in the sheet1. I am attaching a sample file as well as codes.

Sub CreateButton()
Dim Obj As Object
Dim Code As String
Sheets("Sheet1").Select
'create button
Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=600, Top:=30, Width:=100, Height:=35)
Obj.Name = "InsertInvoiceButton"
'buttonn text

[code]....

View 9 Replies View Related

VBA - Label Not Defined Compile Error?

Jun 30, 2014

why this would suddenly stop workin and start giving the above? Error occurs on the first GoTo ExitProc

Code:
Sub ConsolidateDataUsingFileList()
'--Purpose is open each file specified in sheet Info, then copy
' that file's data and append it to the dataset

[Code]....

View 3 Replies View Related

Compile Error: Sub Or Function Not Defined

Jul 28, 2006

Can someone explain the concept of defining the "Sub" to me.

View 5 Replies View Related

Defined Function - Compile Error Block If Without End If

Mar 17, 2014

I have created the following defined function in excel VBA but i get the complie error:-

'Block if without end if'

This this the code I have written for the function:-

Public Function Rule_45(Current, Previous, Result)
' RED: 'Decreasing Trend' OR 'Same' AMBER:'Increasing Trend' GREEN:'>83%'
If Result = "-" Then
Rule_45 = "-"

[Code].....

View 2 Replies View Related

Compile Error: Sub Or Function Not Defined - 3-line Macro

Jun 18, 2009

I'm trying to use MATCH to identify the Column number that contains "DL_Error", then I'll use this Column # to select a cell (found Column # & "34"), but I get the error shown in the title of this post.

Here's my current
Sub Macro15()
' Macro recorded 6/18/2009 by me
Range(Match("DL_ERROR", "A31:CW31", 0) & "34").Select
End Sub

View 9 Replies View Related

Microsoft Visual Basic: Compile Error: Sum Or Function Not Defined

Mar 24, 2006

I receive: "Microsoft Visual Basic Compile error:

Sum or Function not defined" after I have run a macro. I recorded the macro.

The macro code is: ..

View 12 Replies View Related

Excel 2007 :: Compile Error / Sub Or Function Not Defined - Even After Solver Has Been Referenced

May 15, 2014

I get this error : Compile error : Sub or Function not defined. I found out that this might be caused by the solver not being referenced. But, this is fixed now but I still get the same error message.

I'm using Excel 2007 - Windows.

Here is the code :

Sub Macro1()
Dim i As Integer
Dim j As Integer
j = 4
For i = 1 To 4000
If ActiveSheet.Cell(i, 12).Value = 1 Then
ActiveSheet.Cell(j, 15).Value = ActiveSheet.Cell(i, 2)

[code].....

View 2 Replies View Related

Copy And Paste Special Values But Getting Error 'Compile Error - Expected Function Or Variable'

Feb 7, 2007

I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:

Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

For which I get 'Compile Error - Expected Function or Variable'

View 6 Replies View Related

VBA - Use A Variable To Name My Object - Getting Compile Error?

Mar 19, 2013

I have an application that will require I create 20+ objects from a class I have created call "cWorkCenter." This is my first project using objects in VBA. I've created a string variable called "Title." THe application will loop through a list of resources on a worksheet, assign the name of that resource to the variable called "Title" (eg. Title=Range("A1")), then create an object named whatever the string is that "Title" represents. For example, if the first item in my list is "Resource1", then the first object created should be called "Resource1"

When I do this I get the following error: Compile Error: Duplicate declaration in current scope.

Below is the part of the code giving me trouble.

VB:
Public Title As String
Sub Loader_OO()
Title = "Resource1" [code].....

View 2 Replies View Related

Compile Error Expected Function Or Variable

Jul 27, 2006

Need the right direction to stop the error 'Compile Error Expected Function or Variable' appearing. I have both of the following codes in a module. The AddNewTenancy works perfectly but the EditTenancy comes up with the following error.
Both the Userforms exist so I know it's not that.

Sub EditTenancy()
EditTenancy.Show
End Sub
Sub AddNewTenancy()
NewTenancy.Show
End Sub

View 2 Replies View Related

Compile Error / Invalid Next Control Variable Reference

Jul 16, 2013

I have written the following VBA Script but am getting an error message saying -> "Compile Error. Invalid Next control variable reference".

Code:

Sub Form1() 'Change Yes/No
Dim bottomT1 As Integer
bottomT1 = Sheets("Data Sheet").Range("T" & Rows.Count).End(xlUp).Row
Dim bottomP2 As Integer
bottomP2 = Sheets("Follow-Up").Range("P" & Rows.Count).End(xlUp).Row
Dim bottomO2 As Integer
bottomO2 = Sheets("Follow-Up").Range("O" & Rows.Count).End(xlUp).Row

[code].....

View 5 Replies View Related

Shell Function Compile Error - Expected Procedure Not Variable

Aug 8, 2014

I have a VBA shell script that worked just fine on another computer, but now it won't run.

The code that won't execute is:

[Code] ........

View 12 Replies View Related

Compile Error: Invalid Use Of Object: Releasing Variable From Memory

Oct 19, 2006

I'm having trouble releasing a String variable from Memory...

View 9 Replies View Related

Getting Variable Not Defined Error

Sep 23, 2013

I'm getting a Variable Not Defined error

in my workbook_open I have the following code

Code:
Dim workround As Integer
workround = 0
in one of my sheets worksheet_change I have

Code:
If workround = 0 then

when I get to this I get the Variable not defined error. I am using this to bypass some code that does not always need to be run (it slows the workbook down). So I also have another piece of code that toggles workround from 0 to 1.

I have a feeling this error has something to do with Option Explicit. But My limited understanding is obviously insufficient to know what is happening or how to fix it. As I believe that I have defined the variable within the workbook_open code.

View 3 Replies View Related

UserForm (Variable Not Defined) Error

Jul 15, 2013

I am creating a UserForm to get macro running options from the user. The Workbook__Open calls the macro which shows the UserForm right after the variables are defined. Is there a way to avoid the compile error?

07.15.2013-14.15.54 - Bermex's library

View 1 Replies View Related

Complie Error: Variable Not Defined

Oct 10, 2006

I keep getting a "complie error: variable not defined" and I am not sure why (the error highlights the first instance of "sh"). Any feedback? I am trying to test a value of a cell for each row and if the row meets the test then it increases the value in another cell by 1.

This is what I have so far...

View 5 Replies View Related

Calendar Variable Not Defined Error

Jul 10, 2007

I have been using the workbook with the calendar form for ages, but now when i click on the date field to invoke the calender it come out with the comple error:Variable not defined. Here is the piece of code


Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

View 2 Replies View Related

Find And Replace :: Variable Not Defined Error

Nov 2, 2009

I am attempting to use the Find and Replace code you assisted with me into another project, But I am missing something. I keep getting a Variable not define error when I go to search for a CMM # in the add a referral form.

Highlight in yellow is the code ...

View 14 Replies View Related

Run Time Error 1004 Application-defined Or Object-defined Error

Mar 5, 2013

I have a relatively complex report that I work with and a worksheet is no longer required. I have deleted the worksheet and reference to it hwoever when running the macro to pull all the data, it gets to the summary of all the data and i get the Run Time Error 1004 Application-defined or object-defined error pop up. ON reviewing it, it is on this line ActiveCell.Offset(0, 0).Range("a1:a" & Range_Height).Select of the below code...

VB:
Sub GetRangeName()
Sheets("TOTAL").Select

[Code].....

use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.

View 1 Replies View Related

Run Time Error 1004 (application-defined Or Object-defined Error)..

Apr 26, 2009

I have a simple function below to put in different forumlas in different cells to get stock quotes. When I run this I get runtime error 1004 application-defined or object-defined error. The first formula goes through but vba chokes on the next formula: ActiveCell.Offset(I - 1, 4).Formula = username

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved