+ Reply to Thread
Results 1 to 6 of 6

Query about a simplified vba code that does not work

  1. #1
    Forum Contributor
    Join Date
    12-01-2022
    Location
    Rhodes,Greece
    MS-Off Ver
    office 2021
    Posts
    204

    Query about a simplified vba code that does not work

    Hello .i have create this code from scratch,watching a video in youtube.
    My level is at the beginning and I am learning vba code and I am trying to learn to make simple codes.
    The code I have create is this

    Sub objectvariables()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim box As Range
    Set wb = Workbooks(1)
    Set ws = Workbooks(1).Worksheets(1)
    End Sub

    Then I have simplified a little .i changed the workbooks(1) in wb because in the previous line the workbook has became in wb
    Sub objectvariables()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim box As Range
    Set wb = Workbooks(1)
    Set ws = wb.Worksheets(1)
    End Sub

    After I put the variable box in the code
    Sub objectvariables()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim box As Range
    Set wb = Workbooks(1)
    Set ws = wb.Worksheets(1)
    Set box = Workbooks(1).Worksheets(1).Range("b2")
    End Sub

    After I made it even more simple
    Sub objectvariables()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim box As Range
    Set wb = Workbooks(1)
    Set ws = wb.Worksheets(1)
    Set box = ws.Range("b2")
    End Sub

    Then the last code is this one here.
    Sub objectvariables()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim box As Range
    Set wb = Workbooks(1)
    Set ws = wb.Worksheets(1)
    Set box = ws.Range("b2")
    box.Value = 5
    End Sub

    Theoretically if I run this code in cell B2 should be the value of 5(at least according to the video) .
    unfortunately when I am using this code nothing happens. there are no errors in syntax but nothing happens.
    Am I missing anything?
    Thank you in advance

  2. #2
    Forum Expert CheeseSandwich's Avatar
    Join Date
    12-22-2021
    Location
    Kent, England
    MS-Off Ver
    365 - 2402-17328.20068
    Posts
    1,361

    Re: Query about a simplified vba code that does not work

    Perhaps it is getting lost on which workbook to run on, the below runs on the workbook that contains the code:
    Please Login or Register  to view this content.
    If things don't change they stay the same

  3. #3
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Query about a simplified vba code that does not work

    When I run your code it works perfectly, the number 5 appears in cell B2. Wondering if the issue may be the use of Workbooks(1) if you have multiple workbooks open.

    It is common to use ActiveWorkbook instead i.e.

    Please Login or Register  to view this content.
    The only other explanation I can think of is if ScreenUpdating was disabled at some point from an earlier macro run. ScreenUpdating is used to enhance the performance of complex macros, so that the computer can focus on code execution rather than updating what's displayed on your screen. It can be disabled/re-enabled as below.

    Please Login or Register  to view this content.
    The final thing you can do is temporarily add in a message to confirm that the macro code was actually executed e.g.

    Please Login or Register  to view this content.
    If you don't get the prompt, then we need to investigate why it didn't run
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

  4. #4
    Forum Contributor
    Join Date
    12-01-2022
    Location
    Rhodes,Greece
    MS-Off Ver
    office 2021
    Posts
    204

    Re: Query about a simplified vba code that does not work

    Quote Originally Posted by CheeseSandwich View Post
    Perhaps it is getting lost on which workbook to run on, the below runs on the workbook that contains the code:
    Please Login or Register  to view this content.
    Thank you very much my friend.this code worked perfectly.maybe it was that the problem.if i dont get any other responses later i will declare this query as solved.again thank you very much

  5. #5
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Query about a simplified vba code that does not work

    Great - ThisWorkbook is similar to ActiveWorkbook. In case you aren't already aware, the only difference is that ThisWorkbook will execute the code in the same workbook that holds the macro code, whereas ActiveWorkbook will execute the code in the workbook that you run the macro from.

  6. #6
    Forum Contributor
    Join Date
    12-01-2022
    Location
    Rhodes,Greece
    MS-Off Ver
    office 2021
    Posts
    204

    Re: Query about a simplified vba code that does not work

    Quote Originally Posted by AskMeAboutExcel View Post
    Great - ThisWorkbook is similar to ActiveWorkbook. In case you aren't already aware, the only difference is that ThisWorkbook will execute the code in the same workbook that holds the macro code, whereas ActiveWorkbook will execute the code in the workbook that you run the macro from.
    thank you very much for all the informations .everyday i am learning new stuff

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Looking for improvements on VBA code for simplified Knapsack Problem
    By Mark43 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-30-2019, 01:10 PM
  2. Testing single code, work fine. Put multiple codes in one sheet, one code doesn't work.
    By MayDay1988 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-30-2017, 06:14 PM
  3. [SOLVED] Simplified vba code to copy value of range from another Worksheet
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2017, 01:22 PM
  4. [SOLVED] Simplified vba code to copy value of range from another Worksheet
    By Chrispelletier in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2017, 11:00 AM
  5. [SOLVED] How can my functional code be simplified?
    By brharrii in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2013, 07:32 PM
  6. [SOLVED] Creating a simplified version of existing ineffecient code.
    By vexel77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-21-2013, 06:20 PM
  7. [SOLVED] MS-Query code from EXCEL97 doesn't work in EXCEL2003 ??
    By ln54 in forum Excel General
    Replies: 4
    Last Post: 02-21-2005, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1