1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Is there a way to execute dynamically created VBA statements?

Discussion in 'Business Applications' started by ABAPer, Sep 16, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. ABAPer

    ABAPer Thread Starter

    Joined:
    Sep 14, 2004
    Messages:
    9
    Does anyone know how I can do the following?

    Public Function RecalcCol(frmtemp As Form, DOW As String) As Long
    Dim Str As String

    RecalcCol = 0.
    Str = "frmtemp." & DOW & "1.SetFocus"
    exec (Str)

    Str = "RecalcCol = RecalcCol + Val(frmtemp." & DOW & "1.Text)"
    exec (Str)
    ...

    End Function

    DOW could be "Mon", "Tue", etc.

    This way I can reuse this function for multiple days of the week.

    Thanks,

    Ken
     
  2. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    I'm not quite sure what you're trying to achieve here.

    You can access individual controls like this ..

    frmTemp.Controls(DOW & "1").SetFocus

    You can set the focus where you like on a form - but focus is normally only for the user - it isn't needed to do things with a control in code.

    You can make recursive procedures but from the code you post I can't see how the recursion would end.

    Can you post a bit more detail?
     
  3. ABAPer

    ABAPer Thread Starter

    Joined:
    Sep 14, 2004
    Messages:
    9
    Sorry for being vague. I have a timesheet with the days of the week for entering hours. There are 10 rows that I would like to tally to a total line. I was hoping to create an event for each "cell" to recalc the day's column.

    I wrote a routine to do Monday, but noticed I would have to replicate this lengthy piece of code for the other days of the week.

    I have worked with other languages where I can build a statement into a string and then execute it. This would give me better control and not so much code to manage.

    Any suggestions would be very helpful.

    Thanks,

    Ken
     
  4. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    Hi Ken,

    VBA does not have an "Interpret" type statement but it has various ways of accessing objects where the name isn't know till runtime, like the example I gave for the Controls on a Form. As I said, you shouldn't need to SetFocus in code but the same mechanism can be used for other methods and properties of the controls, such as the Text, so your example, as posted, could be written as ...

    Public Function RecalcCol(frmtemp As Form, DOW As String) As Long
    Dim Str As String

    RecalcCol = 0
    frmtemp.Controls(DOW & "1").SetFocus

    RecalcCol = RecalcCol + Val(frmtemp.Controls(DOW & "1").Text)
    ...

    End Function


    If you have a particular thing you are trying to do that you can't do like this, please post it - I'm sure there's a solution.
     
  5. ABAPer

    ABAPer Thread Starter

    Joined:
    Sep 14, 2004
    Messages:
    9
    Tony,

    Your solution works for me. The reason for the set focus is because I'm dealing with a subform and this avoids those nasty little compile errors.

    Is there a way to know what field has focus before I start setting focus all over the place so I can set focus back to where the cursor was before I started?

    Thanks,

    Ken
     
  6. TonyJollans

    TonyJollans

    Joined:
    Jun 24, 2004
    Messages:
    114
    Ken,

    Try frmtemp.ActiveControl.Name

    You will need to be sure you're looking at the right form if you're in a subform because you'll just be told that the activecontrol in the main form is the subform control (does that make sense?)
     
  7. ABAPer

    ABAPer Thread Starter

    Joined:
    Sep 14, 2004
    Messages:
    9
    It makes sense to me. Thanks for all your help.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved execute dynamically
  1. Alchemi
    Replies:
    0
    Views:
    345
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/274795

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice