Compile Error Expected Identifier Or Bracketed Expression
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta
Expected Identifier Access
Discuss the workings and policies of this site About Us Learn excel vba compile error expected identifier more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us
Compile Error Expected Expression Vba
Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like compile error expected expression access vba you, helping each other. Join them; it only takes a minute: Sign up VBA - Expected identifier or bracketed expression error when using With command up vote 0 down vote favorite I am trying to figure out an problem for class where the idea is to "shorten" the code using the With command. Below is the code compile error expected expression excel I have so far. I am curious why you cannot use the shtThisSheet variable under the with command when there is a parenthesis directly after the . Thanks all! Option Explicit Sub Formatting() Dim shtThisSheet As Worksheet Set shtThisSheet = ActiveWorkbook.Worksheets("Sheet1").Range With shtThisSheet .Font.Bold = True .Font.Size = 14 .HorizontalAlignment = xlLeft . ("A3:A6").Font.Bold = True .("A3:A6").Font.Italic = True .("A3:A6").Font.ColorIndex = 5 .Range("A3:A6").InsertIndent 1 .Range("B2:D2").Font.Bold = True .Range("B2:D2").Font.Italic = True .Range("B2:D2").Font.ColorIndex = 5 .Range("B2:D2").HorizontalAlignment = xlRight .Range("B3:D6").Font.ColorIndex = 3 .Range("B3:D6").NumberFormat = "$#,##0" End With End Sub excel vba excel-vba share|improve this question edited Feb 10 at 8:02 Rory 16.3k21219 asked Feb 10 at 4:47 Zack Withrow 356 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote Option Explicit Sub Formatting() Dim shtThisSheet As Worksheet Set shtThisSheet = ActiveWorkbook.Worksheets("Sheet1") With shtThisSheet With .Range("A3:A6") .Font.Bold = True .Font.Size = 14 .HorizontalAlignment = xlLeft .Font.Bold = True .Font.Italic = True .Font.ColorIndex = 5 .Range("A3:A6").InsertIndent 1 End With With .Range("B2:D2") .Font.Bold = True .Font.Italic = True .Font.ColorIndex
soon) Ruby (coming soon) Getting Started Code Samples Resources Patterns and Practices App Registration Tool Events Podcasts Training API Sandbox Videos Documentation Office Add-ins Office Add-in Availability Office Add-ins Changelog Microsoft Graph API Office 365 Connectors Office
Compile Error Expected End Of Statement
365 REST APIs SharePoint Add-ins Office UI Fabric Submit to the Office Store All Documentation https://www.yammer.com/ http://feeds.feedburner.com/office/fmNx Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: compile error: Expected identifier or bracketed expression Microsoft Office for Developers > Access for Developers Question 0 Sign in to vote I have a main form containing a subform which then contains many subforms http://stackoverflow.com/questions/35307016/vba-expected-identifier-or-bracketed-expression-error-when-using-with-command (staff roster). In each control field I have an on KeyDown event that passes 3 variables to a function that colours the active control. Private Sub rMon1L_KeyDown(KeyCode As Integer, Shift As Integer) Call ColourField1(Me.Parent.Name, Me.ActiveControl.Parent.Name, Me.ActiveControl.Name, KeyCode) End Sub pass values to Function ColourField1(Value1, Value2, Value3, KeyCode As Integer) MsgBox "myValue = Value1 & ", " & Value2 & ", " & Value3 & ", " & KeyCode" If KeyCode https://social.msdn.microsoft.com/Forums/office/en-US/f210a04a-566b-4fd0-bdf8-41fedf4de577/compile-error-expected-identifier-or-bracketed-expression?forum=accessdev = 71 Then 'MsgBox "G pressed" Forms!frmBaseRosterPeriods!(Value1)!(Value2)!(Value3).BackColor = 8454016 Else Exit Function End If End Function I used the msgbox to check the values are passed correctly. If I try to reference the forms & control using Forms!frmBaseRosterPeriods!(Value1)!(Value2)!(Value3).BackColor = 8454016 then I get a compile error: expected identifier or bracketed expression at the !(Value2) position but if I reference using the actual form name in place of (Value2) e.g. Forms!frmBaseRosterPeriods!(Value1)!subfrmChild1!(Value3).BackColor = 8454016 it compiles OK. I am obviously missing something in the syntax but cannot see it. As I will have up to 200 subform names for Value2 I would prefer to be able to reference these instead of having to hard code them. Any help would be appreciated Jeff Friday, February 17, 2012 12:52 PM Reply | Quote Answers 0 Sign in to vote I'd change the event procedures to Private Sub rMon1L_KeyDown(KeyCode As Integer, Shift As Integer) Call ColourField1(Me.ActiveControl, KeyCode) End Sub and the function to Function ColourField1(ctl As Control, KeyCode As Integer) If KeyCode = 71 Then ctl.BackColor = 8454016 End If End Function Regards, Hans Vogelaar Proposed as answer by Dirk Goldgar MVPMVP Friday, February 17, 2012 4:15 PM Marked as answer by Jeffrey Souter Saturday, February 18, 2012 12:37 PM Friday, Fe
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel http://www.ozgrid.com/forum/showthread.php?t=63502 | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General Worksheetfunction Error Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register compile error link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 4 of 4 Thread: Worksheetfunction Error Thread Tools Show Printable Version Search Thread Advanced Search January 30th, 2007 #1 KjBox View Profile View compile error expected Forum Posts Super Moderator Join Date 10th January 2007 Location Borneo Posts 2,091 Worksheetfunction Error I am having problems getting the code right for the WorksheetFunction. I have put a snip of the procedure below. I have data that is sequentially entered into columns, always in rows 4 to 18 with the column ref increasing by 1 each time new data is entered. After each instance of data entry into a new column I need to loop through that data and, if the a cell value matches a value in range A1:A200, to place x in column B in the same row as the match in column A. All in Sheet2. VB: Set cStartcell = Sheet2.Range("IV4").End(xlToLeft) ' goes to last entered column For Each cell In Sheet2.Range("B2:B200") [B]Application.WorksheetFunction.If(Match(Offset.(0, -1), Sheet2.Range.("cStartCell:cStartCell.Offset(15, 0)"),""x"")[/B] Next I have put the problem in bold. whatever I try seems to come up as 'end of statement expected' or 'identifier or bracketed expression expected' errors. When I have managed to get rid of errors the fromula does nothing. Hoping sombody can shove me in the right direction. Thanks Excel Video Tutorials / Excel Dashboards Reports Reply With Quote January 30th, 2007 #2 thomach View Profile View Forum Posts OzMVP Join Date 22nd April