Convert String To Object Or Function

Apr 11, 2007

I do not know if this is possible in VBA, but I am hoping it is as it will make my life much easier.

Is it possible to say have a TextBox and a CommandButton, that does the following?


Public Sub Execute()

Dim ObjectName As String
Dim Object As Object

ObjectName = txtObject.Text
Set Object = ObjectName

Object.Execute

End Sub

Or to take it a step further:


Public Sub Execute()

Dim ObjectName As String
Dim FunctionName As String
Dim Object As Object

ObjectName = txtObject.Text
FunctionName = txtFunction.Text
Set Object = ObjectName

Object.FunctionName

End Sub

Are there any inbuilt functions that will create/reference an object based on a string containing the name of the object or function?

View 9 Replies


ADVERTISEMENT

Convert Object Value To Cell Value Using Macro

Apr 23, 2009

Convert object values to cell values!-requirement

i need to convert the values present in the drawing objects like "Rectangle" to cell value.

Eg:

A rectangle say "Rectangle 150" is having a value as "Customer name". Now i need to get this value in normal cell in the worksheet.

View 12 Replies View Related

Userform Object Reference From A String

Feb 28, 2013

Here is the scoop.

I have a userform that has a lot of textboxes that are formatted as date fields. Some of them have a default value and are locked=true, enabled=false. For those ones I have a checkbox next to them so the user can unlock and enable the textbox if they so desired.

Well the checkboxes and textboxes have a similar naming scheme, but are not numeric (they are not CheckBox1, CheckBox2, etc). As an example they one grouping is Cust1RelExpUnlock (this is the checkbox) and Cust1RelExpDateBox (this is the textbox). The difference in their name is the last portion (Unlock or DateBox).

Now when the checkbox is selected the code I want looks something like this:

Code:
Private Sub Cust1RelExpUnlock_Click()UnlockDateBox Cust1RelExpUnlockEnd Sub

Where UnlockDateBox is the function I'm having difficulty with and is supposed to be generic enough to work on any grouping assuming my naming scheme is consistent.

Using a combination of the Left() and Len() functions I can get the unique aspects of the name. Then concatenate it with "DateBox" as required. However I'm getting type mismatch errors, or object required erros. I tried various combinations of Dim _____ As Object, As Control, As Textbox... with no success.

I'm getting the correct name as a string, how do I make it work so I can reference the textbox.enabled/.locked?

Code:
Private Sub UnlockDateBox(Ck)
Dim CkDate As Control 'Tried control, textbox, object
Set CkDate = ProjectInputForm.MultiPage1.Object ' tried just using ProjectInputForm, and Object
Dim CkName As String

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

View 4 Replies View Related

Enter A String In Range Object

Mar 30, 2007

Sub test()
Dim rng, dest, c As Range
Dim result As String
Worksheets("sheet1").Activate
Set rng = Range([a1], [a1].End(xlDown))
Set dest = Range("d1")
result = ""
For Each c In rng
result = result & " " & c
Next
MsgBox result
dest = result
End Sub

the penultimate code statement "dest=result" does notwork the value of "result" is not entered in "dest" though no error comes up if I use "dest.value=result" it gives error "object required" If I use "range("d1")=result" it works.what is the theoretical mistake.

View 2 Replies View Related

UDF To Convert String To Abbreviation?

Mar 18, 2013

I would like to have a UDF to convert any text string to an abbreviation. For example, "Del Puerto Creek at HWY" would be converted to DPCAH. Or "this text string" would be converted to TTS. This has to work on any string of any length in any cell. I guess the UDF would pass each character through a loop and extract the letter immediately after a space and capitalize the letter if it is not already in uppercase. The UDF would have to have the ability to drag using relative references. For example: =AbbrevTxt(A1). It does not matter what the UDF is named.

View 3 Replies View Related

Convert String To Date

Aug 2, 2013

I have a field in the format of 121031. I need a formula to convert this field to a date DD/MM/YYYY format, so 121031 becomes 31/10/2012.

View 9 Replies View Related

Convert String Value Into Range

Mar 27, 2009

I have a string variable (myCell) that contains a cell address (i.e. $A$66)

I have a Range variable (mrR1) that I want to give that value to.

I cannot figure out how to do it. Can anyone help?

After that, I'm hoping to use the Range variables in a Goal Seek.

Private Sub CommandButton1_Click()

Dim myMatch As Double
Dim myCell As String
Dim myStart As Range
Dim myIRR As String
Dim myR1 As Range
Dim myR2 As Range

myMatch = WorksheetFunction.Match(0.25, Range("K67:DZ67"))
myCell = Range("HurdleStart").Offset(0, myMatch).Address
myIRR = Range("IRRStart").Offset(0, myMatch).Address

' myR1 = myIRR value
' myR2 = myCell value

Range(myR1).GoalSeek Goal:=0.25, ChangingCell:=Range(myR2)

End Sub

View 9 Replies View Related

Convert A String To A Range

Apr 1, 2009

I'm working in Excel 2003. Is there a way to convert strings to Ranges on the Excel commandline?

I have a lookup function in an estimate sheet that uses 2 ranges, stored as strings, in another worksheet. They are used to return a price from another workbook. When I use named ranges to access them with the lookup function, it doesn't work. But when I cut and paste the strings into the commandline it works prefectly?


$A10 = Part Number to search for

MaterialOptions is a named Range
eg: '[Catalog.xls]PriceList'!$A$13:$A$17

PriceOptions is a named Range
eg: '[Catalog.xls]PriceList'!$Z$13:$Z$17

LOOKUP($A10,MaterialOptions,PriceOptions) = #VALUE!
But...
LOOKUP($A10,'[Catalog.xls]PriceList'!$A$13:$A$17,'[Catalog.xls]PriceList'!$Z$13:$Z$17) = Success!

View 9 Replies View Related

Using Find Function In VB - Object Required

Aug 6, 2013

This was my attempt, it says 'Object required'

Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, cancel As Boolean)

If Target.Column = 16 Then
Select Case Mid(Target.Text, 1, Worksheet.Function.Find("to", Target.Text, 1) - 2)

Case "CAS"
MsgBox ("Cashiers")

View 2 Replies View Related

String Of Dates Convert To From And To Date

Mar 12, 2014

I have a spreadsheet with reference relating to dates that are listed as single days. I am trying to convert the single dates relating to a reference to a from and to date but i'm having problems.

Example

048051_14110/09/2013
048051_14111/09/2013
048051_14112/09/2013
048051_14113/09/2013
041929_14115/10/2013
041929_14116/10/2013
041929_14117/10/2013
041929_14118/10/2013

I want this to read

048051_141from - 10/09/2013 - to - 13/09/2013
041929_141from - 15/10/2013 - to -18/10/2013

View 1 Replies View Related

Convert Text String To A Date?

Aug 23, 2009

My database has 6 fields names and I have created the following code to capture and post data to the database form a userform. It works but I am sure there is a more efficient and elegant way to do this. The two areas I would like advice are:
1. converting the date string to a date.
2. the line of code where I subtract line6 from line4.

View 3 Replies View Related

Convert String Number To Date?

May 3, 2013

The following function reads many worksheets in one workbook and put the information into one worksheet. F22 to Q22 is dates in the format of mmm-yy on the many worksheets. On the one worksheet B1, C1, D1, etc... is dates also formatted mmm-yy. Now the many sheets only have a 12 month period but could be any date within 60 months. In the many worksheets the beginning date in F22 is based on the date that is entered into G8 of the many worksheets. The function is:

Code:
Function HrsByMonth(strField As String, MonthNum As Integer) As Long
Dim sht As Worksheet, i As Long, j As Integer
HrsByMonth = 0

[Code].....

View 6 Replies View Related

Convert Text String To Integer

Feb 16, 2014

I've got an odd program that exports time data formatted as a text string. For example....

0:10:25

Since it has no numerical value, then I can't simply "format as number". I'm thinking that I will need to use one of the string methods eg: mid() to check each character individually and then convert that into the equivalent numerical value.

So the numerical output I am looking for in the above example would be 625.

View 2 Replies View Related

Convert Number String To Date

Dec 18, 2009

I am trying to convert a number string date that has been stored in a database to a readable date time in an excel pivot table.

1260983366 needs to look like Wednesday, December 16, 2009, 10:09 AM.

The converted date could also look like Wed, Dec 16, 09, 10:09 AM.

View 9 Replies View Related

Object Required Passing Range To Function

Sep 28, 2007

I will post my code and point out where the error occurs.

Sub GetProd()
'Averages Daily Production over the month

Dim WellRange, MonthRange As Range
Dim Month_ As Integer
TotWells = Sheets("R").Cells(1, 2)
TotMonths = Sheets("R").Cells(1, 4)
Month_ = Sheets("R").Cells(2, 4)

For Flag = 1 To TotWells
Set WellRange = DefineWellRange(Flag)
WellRange.Select <-----Used this To check If Range gets passed back properly.
For c = 1 To TotMonths
Set MonthRange = DefineMonthRange(Month_, (WellRange)) <---Error Here
Next c
Next Flag

End Sub ...

So the error occurs in the main "GetProd" Sub at the point when it calls the DefineMonthRange Function, its a 424 "Object Required" Error. Just before this is called I have a "WellRange.Select" which I was using to make sure that WellRange is in fact a range, and it does select the appropriate area.

So My question is of course, why I get an object error even though I am passing a range to a function which is expecting a range?

View 4 Replies View Related

Convert A String To A "real" String (vba)

May 18, 2007

How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example: .....

View 9 Replies View Related

Extract The First 5 Characters In A String, And Convert Them To A Number

Nov 7, 2008

Say I have a string "09800EBHR052708"

How would I take the first 5 characters and get a number out of them?

View 14 Replies View Related

Shorten String And Convert To Upper Case?

Mar 7, 2013

I got a list of counties in Texas, but the list is in mixed case and always has a " County" after each one. I got it converting to upper case, and have tried removing the " County" but no luck. Below is the code I have:

Code:
Sub TEXAS()
For a = 1 To 254
R = "D" & a
i = "a" & a
ActiveSheet.Range(R) = UCase(ActiveSheet.Range(i))
Next a
End Sub

View 7 Replies View Related

Convert Text String Into Excel Date With VBA

Aug 13, 2013

I have a date range that looks like this (British date style, not US):

edit: I posted an image but it can't be seen for some reason:

Date Range
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10
2010 11/10 17/10

Thus the first cell show 11 October 2010 to 17 October 2010 I want to return just the first day (the week commencing date):

edit: I posted an image but it can't be seen for some reason:

Week Commencing
11/10/2010
11/10/2010
11/10/2010
11/10/2010

I have written some VBA to the best of my ability but it seems clumsy using the FORMAT function. Is there a better of doing this with VBA. I can't seem to find a function in VBA which is the equivalent of the formula function DATE.

Code:
Sub ConvertTextToBritStyleDate()
' dd/mm/yyyy (not mm/dd/yyyy)
' Select the cells containing the dates (not the header)

[Code]...

View 3 Replies View Related

Basic Macro - Convert String To Unicode

Mar 30, 2014

The following macro works:

Code:
Range("b42").Resize(, Len(Range("A42").Value)) = split(StrConv(Range("A42").Value, vbUnicode), Chr(0))

I tried to revise the macro so that instead of 'b42' it's activecell.

Code:
Dim b As Range
Set b = ActiveCell
Range(b).Resize(, Len(Range(b).Value)) = split(StrConv(Range(b).Value, vbUnicode), Chr(0))

My code isn't working.

View 2 Replies View Related

VBA Custom Function - Form Control Name Object Not Supported?

Jun 18, 2014

I'm creating a custom function to cut down on redundant code. However, in converting the code to a function so that it may be used in different instances, I'm getting an unexpected error when the code executes.

The error is:

Code:
Object doesn't support this property or method.

Here is my function:

Code:
Public Function chkClick(checkboxName As String, tabName As String, chartName As String, seriesNumber As Long)
Sheets(tabName).ChartObjects(chartName).Activate
If Sheets(tabName).checkboxName.Value = True Then
ActiveChart.FullSeriesCollection(seriesNumber).Select

[code]....

The function checks the status of the checkbox name passed to it. If it's checked, it will show a chart series. If it's not checked, it will hide the chart series. The error comes in referencing the "checkboxName".

How might I need to alter the code so that I can use the form control name as a variable? This is ultimately one of the key components of the function as each control has it's own name.

View 9 Replies View Related

Using VBA Codes To Generate Consecutive Numbers And Convert Them Into String

Jan 10, 2013

If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".

I can generate the consecutive numbers, but have not figured out how to generate the required string.

View 5 Replies View Related

How To Convert String To Excel Date Format Using Formula Or VBA

Sep 28, 2012

Is there a way to convert the string "Sep 28 2012" to excel date format using a formula or vba?

View 8 Replies View Related

Convert First Letter Of String To Capital Without Altering Formatting

Nov 17, 2009

Convert the first letter of a string to a capital without altering the rest of the strings format.

example: the activecell contains the string a1st (lowercase a, superscript 1st)

how can i achieve this in VBA?

Additionally, is it also possible to convert all of a string to capitals except the superscript characters

View 9 Replies View Related

Convert Cell Number Reference To String Address

May 18, 2007

I have something like the following:

If Worksheets("RawData"). Cells(1, nColumn).Value = Worksheets("RawData").Cells(12, 16).Value Then
..do code (I know the cell location of interest at this point)
End If

Then I End up needing To Do something Like the following
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("RawData").Range("L1:N1")

“L1:N1” above is a sample. Given the code above it I have the cell location in question but it is in the format such as .Cells(12,16). That doesn’t do me much good when range wants something like “L1”. I don’t know how to convert that (12,16) to a L16 for example.

View 7 Replies View Related

Randbetween Function To Only Randomate A Number When A Certin Object Is Pressed

Mar 25, 2009

Is it possbile with the randbetween function to only randomate a number when a certin object is pressed? As at the moment, every button i press or drop down data i select it will randomate a number, which i dont want to happen.

View 2 Replies View Related

Using VBA Code To Generate List Of Consecutive Numbers And Convert Into String

Jan 10, 2013

If n = 5, then I want to generate a string like this: "1+2+3+4+5". Similarly, if n = 7, I want the string "1+2+3+4+5+6+7".

I can generate the consecutive numbers, but have not figured out how to generate the required string.

View 2 Replies View Related

Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column

Oct 13, 2009

On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

View 5 Replies View Related

Convert Function GetComputerName To VBA

Dec 7, 2007

I don't have a clue as to how to convert this to VBA code.

When installed as a macro, the function works fine in a cell on my PC, but when I send the file to another pc, it still gives the name of my pc. I need it to return the name of thier pc.

View 14 Replies View Related

Convert It To A Usefull Function I Get A #Name

Dec 7, 2009

Sub xxxxxxx()
Keli = Cells(13, 8).Value
Valid1 = Application.WorksheetFunction.VLookup(Day(Keli), Range("ToTalList"), 2, False)
Valid2 = Application.WorksheetFunction.VLookup(Month(Keli), Range("ToTalList"), 3, False)
Valid3 = Application.WorksheetFunction.VLookup(Int(Year(Keli) / 100), Range("ToTalList"), 4, False)
Valid4 = Application.WorksheetFunction.VLookup(Year(Keli) - Int(Year(Keli) / 100) * 100, Range("ToTalList"), 5, False)
MsgBox Valid1 & " " & Valid2 & " " & Valid3 & " " & Valid4
End Sub

When I try to convert it to a usefull function I get a #Name? Error
What do i do wrong? The purpose is to create a date in to words function

Public Function DateInWords(Keli As Range)
Valid1 = Application.WorksheetFunction.VLookup(Day(Keli.Value), ThisWorkbook.Worksheets(1).Range("ToTalList"), 2, False)
Valid2 = Application.WorksheetFunction.VLookup(Month(Keli.Value), ThisWorkbook.Worksheets(1).Range("ToTalList"), 3, False)
Valid3 = Application.WorksheetFunction.VLookup(Int(Year(Keli.Value) / 100), ThisWorkbook.Worksheets(1).Range("ToTalList"), 4, False)
Valid4 = Application.WorksheetFunction.VLookup(Year(Keli.Value) - Int(Year(Keli.Value) / 100) * 100, ThisWorkbook.Worksheets(1).Range("ToTalList"), 5, False)
DateInWords = Valid1 & " " & Valid2 & " " & Valid3 & " " & Valid4
End Function

View 9 Replies View Related







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