Solved

how do I get the referenced cell in a vba function

Posted on 2011-03-06
10
341 Views
Last Modified: 2012-05-11
I am writing a custom function for vba to parse a string.  When the user enters a cell (a15) in the formula bar to set which cell needs to be converted , i need to know that cell in my code so i can then write the answers.  it's not the activecell.
0
Comment
Question by:BROOKLYN1950
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35047179
I would recommend using a UDF For example

Public Sub ParseSring(rng As Range)
       rng.Value = rng.Value & " Hello World"
End Sub

Open in new window


You can directly call the ParseSring from excel. For Example

=ParseSring(A1)

So if the value of A1 is "Hi" then then output will be "Hi Hello World"

Sid
0
 

Author Comment

by:BROOKLYN1950
ID: 35047269
how could i then write the results of the new string to the next cell down from the value put in in excel?
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 62 total points
ID: 35047275
Do you mean a user defined function which the user can use like a built-in function?

If you do then it might not be possible to do what you want, mainly because a UDF can't alter the value of a cell - it can only return a value to the cell the function is in.

For example this UDF would return the first word in as string and you could use it in a cell like this:

=GetFirstWord(A1)

where A1 contains the string you want to get the first word from.


Option Explicit

Function GetFirstWord(rng As Range) As String
Dim pos As Long

       pos = InStr(rng, " ")

        pos = IIf(pos, pos - 1, Len(rng.Value))
        
       GetFirstWord = Left(rng.Value, pos)
       
End Function

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35047285
In that case put the formula in the next cell and reference the 1st cell in the function if the string that needs to be parsed is in 1sy cell. For example if

A1 = "Hi"

Then type this formulas in cell A2

=ParseSring(A1)

The result in A2 will be

"Hi Hello World"

Sid

Sid
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:BROOKLYN1950
ID: 35047344
sorry for being inarticulate,

my function requires me to separate out from the target cell (A1) ie  my - cat  into to separate cells, one containing "my" the other containing "cat".  I can already create the different strings in sepatarte temp strings,

how can I offset from that target cell (which my code doesn't know) so A2 has "my"  and and A3 has "cat"
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 63 total points
ID: 35047374
Instead of using a function, write a sub and then run it for example see the attached excel file.

If you run the Sub ParseString(), the results will be auto populated in the next rows. You will have to amend it for realistic situations.

Sid

Code Used

Sub ParseString()
    parsestr Range("A1")
End Sub

Sub parsestr(rng As Range)
    Dim MyArray() As String
    
    MyArray = Split(rng.Value, "-")
    j = 0
    For i = rng.Row To (rng.Row + UBound(MyArray))
        rng.Offset(i).Value = Trim(MyArray(j))
        j = j + 1
    Next i
End Sub

Open in new window

Sample.xls
0
 
LVL 33

Expert Comment

by:Norie
ID: 35047397
You can't do that with a function, but you can do it with a sub.

You could even to it with a worksheet event which will be triggered whenver something is entered on the worksheet.

What code do you currently have and how can it not know the string to parse?
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35047484
Just use Text to Columns in the Data Menu/Tab

Why reinvent the wheel?
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35357003
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now