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

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

 

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?

 

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

 

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.

 

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

 

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?)

 

It makes sense to me. Thanks for all your help.