Using Defined name and/or direct formula in VBA

S

Schturman

Board Regular
Joined
May 28, 2022
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
Hi.
I need some help with VBA code that should copy value from one cell to another.
I have Defined name named ExtractCurrency with formula:
VBA Code: 
=RIGHT(GET.CELL(53,ינואר!$I8),3)
1726146621932.png


It working perfectly with direct formula in the sheet:
Code: 
=(I8*SWITCH(ExtractCurrency,"CHF",$M$5,"GBP",Live_Exchange_Rate!$H$5,"BGN",Live_Exchange_Rate!$A$16,"SEK",Live_Exchange_Rate!$H$7,"HUF",Live_Exchange_Rate!$C$10/100,"GEL",Live_Exchange_Rate!$A$13)/$M$3)*(1+$O$2)
First it checking which currency in use, then doing conversion to $
Because currency ration changing I trying to implement it to VBA code that just give me a result and will not change it when currency ration changing.. but without success...

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
  
'   See if any cells updated in column I
    Set rng = Intersect(Target, Range("I8:I55"))

    If rng Is Nothing Then Exit Sub

    Application.EnableEvents = False

    If Not rng Is Nothing Then
    '   Loop though all updated rows in column I
        For Each cell In rng
           If IsNumeric(cell.Value) And cell.Value > 0 Then
             If Range("ExtractCurrency").Value = "CHF"
                 cell.Offset(0, 1) = (cell * Range("$M$5") / Range("$M$3")) * (1 + Range("$O$2"))
                 Else
                   If Range("ExtractCurrency").Value = "GBP"
                     cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$5") / Range("$M$3")) * (1 + Range("$O$2"))
                      Else
                        If Range("ExtractCurrency").Value = "BGN"
                          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$A$16") / Range("$M$3")) * (1 + Range("$O$2"))
                           Else
                            If Range("ExtractCurrency").Value = "SEK"
                              cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$7") / Range("$M$3")) * (1 + Range("$O$2"))
                            
                            End If
                        End If
                   End If
             End If
           Else
               Range(cell.Offset(0, 1), cell.Offset(0, -1)) = ""
           End If
        Next cell
    End If

    Application.EnableEvents = True 'reenable events
End Sub

Can someone help me to fix it or maybe suggest a better variant for code or maybe I can use my direct formula inside the code ?
Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Maybe I do not understand something but do You need 'recalculate' (check currency) in all 47 cells because only 1 of them been changed?
If I changed 'LiveExRate!A16' (for BGN) what for checking and relacalculate SE / GBP / CHF etc.
 
Upvote 0
Hello,

I am not sure i understood very well what is the problem with your VBA code, is it not working at all or not giving the expected result ?

If you want to evaluate a formula in VBA, you can use Application.Evaluate(thingToEvaluate) or [thingToEvaluate]. For example if A1 contains 2 and A2 contains 3, [=A1*A2] returns 6.

I don't know how VBA handles Hebraic caracters, i hope it is fine. I give you below your code reviewed as far as i could. Be very careful, you had more "end if" than "if" so of course it could not work. Use indentation to understand where you are in the code, and write End if as soon as you write If not to forget it.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Dim cell As Range
 
  '   See if any cells updated in column I
  Set rng = Intersect(Target, Range("I8:I55"))

  If rng Is Nothing Then Exit Sub

  Application.EnableEvents = False

  ' you do not need to check if rng is nothing here, you already exited the sub if so
 
  '   Loop though all updated rows in column I
  For Each cell In rng
    If IsNumeric(cell.Value) Then
      If cell.Value > 0 Then    ' separated otherwise it can throw an error
        Select Case Range("ExtractCurrency").Value  ' easier to read, alternatively use ElseIf instead of nested ifs
        Case "CHF"
          cell.Offset(0, 1) = (cell * Range("$M$5") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "GBP"
          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$5") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "BGN"
          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$A$16") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "SEK"
          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$7") / Range("$M$3")) * (1 + Range("$O$2"))
        Case Else
          Range(cell.Offset(0, 1), cell.Offset(0, -1)) = ""
        End Select
      End If
    End If

  Next cell

  Application.EnableEvents = True                'reenable events
End Sub
 
Upvote 0
KOKOSEK said:
Maybe I do not understand something but do You need 'recalculate' (check currency) in all 47 cells because only 1 of them been changed?
If I changed 'LiveExRate!A16' (for BGN) what for checking and relacalculate SE / GBP / CHF etc.
Click to expand...
No, it related every time to specific cell:
For example I wrote in I9 number 20 - the number format now is CHF
The code should check what the number format, for this I want to use my defined name "ExtractCurrency" that will run formula:
VBA Code: 
=RIGHT(GET.CELL(53,ינואר!$I9),3)
Now if the result from "ExtractCurrency" = "CHF" it will run command for converting this currency, if not equals - it will check another currency (BGN and so on...)
Same if I will write the same number in another cell in column I , for example in the next date when currency rate changed
In this case the previous result will not change also if currency rate changed. It will updated only if I will update the same cell (I9)
 
Upvote 0
saboh12617 said:
Hello,

I am not sure i understood very well what is the problem with your VBA code, is it not working at all or not giving the expected result ?

If you want to evaluate a formula in VBA, you can use Application.Evaluate(thingToEvaluate) or [thingToEvaluate]. For example if A1 contains 2 and A2 contains 3, [=A1*A2] returns 6.

I don't know how VBA handles Hebraic caracters, i hope it is fine. I give you below your code reviewed as far as i could. Be very careful, you had more "end if" than "if" so of course it could not work. Use indentation to understand where you are in the code, and write End if as soon as you write If not to forget it.

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  Dim cell As Range
 
  '   See if any cells updated in column I
  Set rng = Intersect(Target, Range("I8:I55"))

  If rng Is Nothing Then Exit Sub

  Application.EnableEvents = False

  ' you do not need to check if rng is nothing here, you already exited the sub if so
 
  '   Loop though all updated rows in column I
  For Each cell In rng
    If IsNumeric(cell.Value) Then
      If cell.Value > 0 Then    ' separated otherwise it can throw an error
        Select Case Range("ExtractCurrency").Value  ' easier to read, alternatively use ElseIf instead of nested ifs
        Case "CHF"
          cell.Offset(0, 1) = (cell * Range("$M$5") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "GBP"
          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$5") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "BGN"
          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$A$16") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "SEK"
          cell.Offset(0, 1) = (cell * Range("Live_Exchange_Rate!$H$7") / Range("$M$3")) * (1 + Range("$O$2"))
        Case Else
          Range(cell.Offset(0, 1), cell.Offset(0, -1)) = ""
        End Select
      End If
    End If

  Next cell

  Application.EnableEvents = True                'reenable events
End Sub
Click to expand...
The problem like in my code with this part:
Code: 
Range("ExtractCurrency").Value
It just not work an I don't understand why... Maybe because this defined name use GET.CELL ?
1726159861337.png
 
Upvote 0
OK, I got it working by creating Helper column where it show me the currency name and now my code working like this

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cell As Range
 
'   See if any cells updated in column I
    Set rng = Intersect(Target, Range("I8:I55"))

    If rng Is Nothing Then Exit Sub

    Application.EnableEvents = False

    If Not rng Is Nothing Then
    '   Loop though all updated rows in column I
        For Each cell In rng
           If IsNumeric(cell.Value) And cell.Value > 0 Then
             If cell.Offset(0, 2) = "CHF" Then
                 cell.Offset(0, 1) = (cell * Range("$M$5") / Range("$M$3")) * (1 + Range("$O$2"))
                 Else
                   If cell.Offset(0, 2) = "GBP" Then
                     cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$H$5") / Range("$M$3")) * (1 + Range("$O$2"))
                      Else
                        If cell.Offset(0, 2) = "BGN" Then
                          cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$A$16") / Range("$M$3")) * (1 + Range("$O$2"))
                           Else
                            If cell.Offset(0, 2) = "SEK" Then
                              cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$H$7") / Range("$M$3")) * (1 + Range("$O$2"))
                            
                            End If
                        End If
                   End If
             End If
           Else
               Range(cell.Offset(0, 1), cell.Offset(0, -1)) = ""
           End If
        Next cell
    End If

    Application.EnableEvents = True 'reenable events
End Sub

Also don't understand why this part not worked:
Code: 
Range("Live_Exchange_Rate!$H$5")
I needed to change it like this:
Code: 
Sheets("Live_Exchange_Rate").Range("$H$5")
Still didn't find a way to do this without Helper column, just only with define name...
Any idea ?
 
Upvote 0
Hello,

About your last question it is just how VBA works, and i had never seen someone putting the sheet name in the Range argument before you. The last solution metionned (Workbook.Sheets.Range) is the correct way to do it.

Also i remark you did not wanted to try the ElseIf statement (Using If...Then...Else statements (VBA) | Microsoft Learn) which makes your code terribly nested for no reason. Another thing is that the nature of the problem makes the nests not even needed in the first place as the conditions checked are mutually exclusive (a cell can not be "CHF" and "SEK" at the same time). The only reason could be optimization but certainly not logic.

Finally i understand why you got the error in the code i gave you. On your first message you test the value of the Range named "ExtractCurrency", which is the same range for all values of the loop. So same value. While on your second code, if i understand correctly, you refer to an offset, i guess your helper column, which contains a different currency for each cell of the loop. I think you mislead me in the sense that the range ExtractCurrency did not ACTUALLY contain the "CHF"/"SEK"/… field but a reference to another cell. Which would explain the error. I think the helper column is a good choice.

If your code works everything seems alright to me.
 
Upvote 0
saboh12617 said:
Hello,

About your last question it is just how VBA works, and i had never seen someone putting the sheet name in the Range argument before you. The last solution metionned (Workbook.Sheets.Range) is the correct way to do it.

Also i remark you did not wanted to try the ElseIf statement (Using If...Then...Else statements (VBA) | Microsoft Learn) which makes your code terribly nested for no reason. Another thing is that the nature of the problem makes the nests not even needed in the first place as the conditions checked are mutually exclusive (a cell can not be "CHF" and "SEK" at the same time). The only reason could be optimization but certainly not logic.

Finally i understand why you got the error in the code i gave you. On your first message you test the value of the Range named "ExtractCurrency", which is the same range for all values of the loop. So same value. While on your second code, if i understand correctly, you refer to an offset, i guess your helper column, which contains a different currency for each cell of the loop. I think you mislead me in the sense that the range ExtractCurrency did not ACTUALLY contain the "CHF"/"SEK"/… field but a reference to another cell. Which would explain the error. I think the helper column is a good choice.

If your code works everything seems alright to me.
Click to expand...
Hi.
First, Thanks for answer and explanation!
Yes, probably I not explained itself correctly...
It's correct the Range named "ExtractCurrency", is the same range... It's start from I8
Before VBA I used a command and not needed the Helper column:
VBA Code: 
=IF(I8>0,(I8*SWITCH(ExtractCurrency,"CHF",$M$5,"GBP",Live_Exchange_Rate!$H$5,"BGN",Live_Exchange_Rate!$A$16,"SEK",Live_Exchange_Rate!$H$7,"HUF",Live_Exchange_Rate!$C$10/100,"GEL",Live_Exchange_Rate!$A$13)/$M$3)*(1+$O$2),"")
This is a reason I thought it will work the same way in VBA, but probably it's not possible... And I created a Helper column that show me result of "ExtractCurrency" (currency name).
And in the VBA I need to use "cell.Offset(0, 2)" to check what currency in use (CHF, GBP...)
Now also your code working perfectly and looks better than my :) :

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
' Source: https://www.mrexcel.com/board/threads/using-defined-name-and-or-direct-formula-in-vba.1264264/post-6214611
  Dim rng As Range
  Dim cell As Range
 
  '   See if any cells updated in column I
  Set rng = Intersect(Target, Range("I8:I55"))

  If rng Is Nothing Then Exit Sub

  Application.EnableEvents = False

  ' you do not need to check if rng is nothing here, you already exited the sub if so
' If Not rng Is Nothing Then
  '   Loop though all updated rows in column I
  For Each cell In rng
    If IsNumeric(cell.Value) And cell.Value > 0 Then
        Select Case cell.Offset(0, 2)  ' easier to read, alternatively use ElseIf instead of nested ifs
        Case "CHF"
          cell.Offset(0, 1) = (cell * Range("$M$5") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "GBP"
          cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$H$5") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "BGN"
          cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$A$16") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "SEK"
          cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$H$7") / Range("$M$3")) * (1 + Range("$O$2"))
        Case "HUF"
          cell.Offset(0, 1) = (cell * (Sheets("Live_Exchange_Rate").Range("$C$10") / 100) / Range("$M$3")) * (1 + Range("$O$2"))
        Case "GEL"
          cell.Offset(0, 1) = (cell * Sheets("Live_Exchange_Rate").Range("$A$13") / Range("$M$3")) * (1 + Range("$O$2"))
        End Select
        Else
          Range(cell.Offset(0, 1), cell.Offset(0, -1)) = ""
    End If

  Next cell
'End If
  Application.EnableEvents = True                'reenable events
End Sub
Thank you very much !
 
Upvote 0
No problem, i'm glad you found a solution and you progressed.

About your problem with the unique named range, it is that VBA reads litteraly the content of the cell. Or your code wanted to evaluate this value, so you needed to enclose it in something like Application.Evaluate().
But honestly i find the solution of the helper column easier. It goes with the XL workflow in which you should use columns (that you can hide) for useful intermediate results.
 
Upvote 0
saboh12617 said:
No problem, i'm glad you found a solution and you progressed.

About your problem with the unique named range, it is that VBA reads litteraly the content of the cell. Or your code wanted to evaluate this value, so you needed to enclose it in something like Application.Evaluate().
But honestly i find the solution of the helper column easier. It goes with the XL workflow in which you should use columns (that you can hide) for useful intermediate results.
Click to expand...
I also tried Application.Evaluate("ExtractCurrency"), and it worked but very weird...
That mean If I put in I10 some number it not show me result in J10
I put number in I11 - it not show me result in J11
I return to I10 and put number again - now it start to show me result in J10
Same I need to do for all cell, that mean I need to return to previous one....
I think the Helper column is enough good.
Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
349
tixarah
T
O
  • Question Question
User-defined type not defined - vba/excel error
Replies
4
Views
289
RoryA
RoryA
R
  • Question Question
Having Trouble getting this code in VBA to work. Attempting to download a list of PDFs
Replies
1
Views
143
6StringJazzer
6StringJazzer
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
321
Patriot2879
P
S
  • Solved
Combine two codes in one worksheet change events
Replies
3
Views
486
Alex Blakenburg
A

Forum statistics

Threads
1,224,795
Messages
6,181,000
Members
453,011
Latest member
Osamu9Dazai

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top